How can I find a view column's base table column?

I program, administer, and support an ERP application. One of my more common tasks is to understand where data originates, finding the link between a particular report field and its data entry field in the application. To do this, I have to manually examine the DDL SQL of one or more views in order to understand the table columns, in order to match one view's table columns with another view's table columns. This manual task might require examination of four or five abstraction layers.

Is there a faster way to examine the metadata to find how a view column is calculated from its base table column? Is there a way to examine Oracle's execution plan to see the rewritten query, skipping the manual inspection of the intermediate views?

Starting with 12c, we have DBMS_UTILITY.EXPAND_SQL_TEXT.

You basically pass the query text through the first, input parameter, and receive the rewritten query back through the second, output parameter.

But since you are on 11.2, you can not use this yet. You can however collect the optimizer trace. If you already ran your query, and have its sql_id:

begin
  dbms_sqldiag.dump_trace
  (
    p_sql_id=>'1a2b3c4d5e6f',
    p_child_number=>0,
    p_component=>'optimizer',
    p_file_id=>'optimzer_trace'
  );
end;
/

Or if you do not know the sql_id and did not run the query before:

alter session set events 'trace[rdbms.sql_optimizer.*]';
-- run your query here, no need to wait for results, it just need to be hard parsed
alter session set events 'trace[rdbms.sql_optimizer.*] off';

These will generate a trace file that you can read and find the final, rewritten query in it.

To be honest, the output produced by all methods can be difficult to read once you have some complex queries, and the way to get them is not so trivial, so I usually end up reading and "parsing" the DDL statements manually...

Edit:
The WITH clause lets us do this in SQL without a separate call to PL/SQL. The q'[]' syntax even lets us copy and paste a whole query without scrubbing the string literals.

WITH
   FUNCTION get_expanded_sql_text_ (
      input_sql_text_ IN CLOB) RETURN CLOB
   IS
      output_sql_text_ CLOB;
   BEGIN
      dbms_utility.expand_sql_text (
         input_sql_text  => input_sql_text_,
         output_sql_text => output_sql_text_);
      RETURN output_sql_text_;
   END get_expanded_sql_text_;
SELECT get_expanded_sql_text_(q'[SELECT 'Hello, World!' FROM DUAL]') AS expanded_sql_text
  FROM DUAL
/

StackExchange Database Administrators Q#146190, answer score: 5