How to retrieve the underlying SQL for a view.

Some time , you run into performance tuning tasks , where non-performant query uses views . Those views in turn uses views one after the other . 
Though , this is not a recommended practice , it is widely (ab)used . You need to reverse engineer the view to get the actual base tables and its filters and so on. 
Most of the time , the query performs better when directly accessed with the base table ( it depends on the query , and other factors) . 

In any case , Oracle 12c has introduced a new API ( dbms_utility.EXPAND_SQL_TEXT ) to get the underlying base table structures for the views .
This is in one of the most underutilized package , in my opinion ( dbms_utility ). 

Let us get to the example . 



SQL> CREATE OR REPLACE VIEW dept_sales_v
  2  AS
  3    SELECT
  4      deptno AS DepartmentNumber ,
  5      dname  AS DepartmentName ,
  6      loc    AS Location
  7    FROM
  8      scott.dept
  9    WHERE
 10      deptno = 30 ;

View created.


Now , Let us use this new API to get the underlying DDL for the view. 
You may notice that the generated DDL is conceptually the same , syntactically Oracle has created sub query and reference that sub query in the outer query.

SQL> SET serveroutput ON
SQL> SET LONG 10000
SQL> DECLARE
  2    expsql CLOB ;
  3    inpsql CLOB ;
  4  BEGIN
  5    inpsql := 'Select * From dept_sales_v' ;
  6    dbms_utility.EXPAND_SQL_TEXT ( input_sql_text => inpsql, output_sql_text =>
  7    expsql) ;
  8    dbms_output.put_line( expsql) ;
  9  END ;
 10  /
SELECT "A1"."DEPARTMENTNUMBER" "DEPARTMENTNUMBER","A1"."DEPARTMENTNAME"
"DEPARTMENTNAME","A1"."LOCATION" "LOCATION" FROM  (SELECT "A2"."DEPTNO"
"DEPARTMENTNUMBER","A2"."DNAME" "DEPARTMENTNAME","A2"."LOC" "LOCATION" FROM
"SCOTT"."DEPT" "A2" WHERE "A2"."DEPTNO"=30) "A1"

PL/SQL procedure successfully completed.


Now , let us create a view based on this view. 


SQL> CREATE OR REPLACE VIEW EmpSales_v
  2  AS
  3    SELECT
  4      v.DEPARTMENTNAME ,
  5      v.location ,
  6      e.EMPNO AS EmployeeCode,
  7      e.ENAME AS EmployeeName
  8    FROM
  9      DEPT_SALES_V v ,
 10      emp e
 11    WHERE
 12      v.DEPARTMENTNUMBER = e.deptno ;

View created.

Here is the SQL generated by Oracle for this view. 

SQL>     set serveroutput on
SQL> set long 10000
SQL> declare
  2  expsql clob ;
  3  inpsql clob ;
  4  begin
  5  inpsql := 'Select * From EmpSales_v'  ;
  6  dbms_utility.EXPAND_SQL_TEXT  ( input_sql_text => inpsql, output_sql_text => expsql) ;
  7  dbms_output.put_line( expsql) ;
  8  end ;
  9  /
SELECT "A1"."DEPARTMENTNAME" "DEPARTMENTNAME","A1"."LOCATION"
"LOCATION","A1"."EMPLOYEECODE" "EMPLOYEECODE","A1"."EMPLOYEENAME" "EMPLOYEENAME"
FROM  (SELECT "A3"."DEPARTMENTNAME" "DEPARTMENTNAME","A3"."LOCATION"
"LOCATION","A2"."EMPNO" "EMPLOYEECODE","A2"."ENAME" "EMPLOYEENAME" FROM  (SELECT
"A4"."DEPTNO" "DEPARTMENTNUMBER","A4"."DNAME" "DEPARTMENTNAME","A4"."LOC"
"LOCATION" FROM "SCOTT"."DEPT" "A4" WHERE "A4"."DEPTNO"=30) "A3",SCOTT."EMP"
"A2" WHERE "A3"."DEPARTMENTNUMBER"="A2"."DEPTNO") "A1"

PL/SQL procedure successfully completed.

Here is the SQL that has been formatted for Clarity.

Comments Welcome.