I have several hundred views that contain column aliases in their definitions. I need to programmatically (preferably SQL query) determine the underlying expression for a given column alias, but I can't find any views or tables in Oracle that correlate the alias with the expression. DBA_VIEWS.TEXT doesn't include the aliases. DBMS_METADATA.GET_DDL will generate the alias list in the view-definition header, but those are positional, comma-separated values that do not directly correlate to the expressions to which they apply. The correlation has to exist somewhere in the database since GET_DDL can find both the alias and the expression to generation the view definition. How can I determine the underlying expression for a given view name/column alias in a query?

Thanks

create table t (
  c1 int, c2 int
);

create or replace view vw  as 
  select c1, c2, c1 + c2 as sm from t;
  
create or replace view vw2 ( c1, c2, c3 ) as 
  select * from vw;

declare
  l_clob clob;
begin
  dbms_utility.expand_sql_text (
    input_sql_text  => q'!select * from vw2!',
    output_sql_text => l_clob  );
  dbms_output.put_line(l_clob);
end;
/

select "A1"."C1"    "C1",
       "A1"."C2"    "C2",
       "A1"."C3"    "C3"
from (
  select "A2"."C1"    "C1",
         "A2"."C2"    "C2",
         "A2"."SM"    "C3"
  from (
    select "A3"."C1"                  "C1",
           "A3"."C2"                  "C2",
           "A3"."C1" + "A3"."C2"      "SM"
    from "CHRIS"."T" "A3"
  ) "A2"
) "A1"