DBMS_UTILITY.EXPAND_SQL_TEXT

DBMS_UTILITY.EXPAND_SQL_TEXT : Expand SQL References to Views in Oracle Database 12c Release 1 (12.1)

Views can be a useful way to hide complexity from developers, but that can in turn cause problems. It's easy to write apparently simple statements, that result in extremely complex SQL being sent to the server. The DBMS_UTILITY.EXPAND_SQL_TEXT procedure expands references to views, turning them into subqueries in the original statement. A simple example of this is shown below.
Create a view containing a join.

CONN scott/tiger@pdb1

CREATE OR REPLACE VIEW emp_v AS
SELECT e.empno,
       e.ename,
       e.job,
       e.mgr,
       e.hiredate,
       e.sal,
       e.comm,
       e.deptno,
       d.dname
FROM   emp e
       JOIN dept d ON e.deptno = d.deptno;

The view has hidden the complexity of the join, allowing us to use an extremely simple query.

SELECT * FROM emp_v;

We can see the real SQL statement processed by the server by expanding the statement.

SET SERVEROUTPUT ON 
DECLARE
  l_clob CLOB;
BEGIN
  DBMS_UTILITY.expand_sql_text (
    input_sql_text  => 'SELECT * FROM emp_v',
    output_sql_text => l_clob
  );

  DBMS_OUTPUT.put_line(l_clob);
END;
/

SELECT "A1"."EMPNO" "EMPNO",
       "A1"."ENAME" "ENAME",
       "A1"."JOB" "JOB","A1"."MGR" "MGR",
       "A1"."HIREDATE" "HIREDATE",
       "A1"."SAL" "SAL",
       "A1"."COMM" "COMM",
       "A1"."DEPTNO" "DEPTNO",
       "A1"."DNAME" "DNAME"
FROM   (SELECT "A2"."EMPNO_0" "EMPNO",
               "A2"."ENAME_1" "ENAME",
               "A2"."JOB_2" "JOB",
               "A2"."MGR_3" "MGR",
               "A2"."HIREDATE_4" "HIREDATE",
               "A2"."SAL_5" "SAL",
               "A2"."COMM_6" COMM",
               "A2"."QCSJ_C000000000400000_7" "DEPTNO",
               "A2"."DNAME_9" "DNAME"
         FROM  (SELECT "A4"."EMPNO" "EMPNO_0",
                       "A4"."ENAME" "ENAME_1",
                       "A4"."JOB" "JOB_2",
                       "A4"."MGR" "MGR_3",
                       "A4"."HIREDATE" "HIREDATE_4",
                       "A4"."SAL" "SAL_5",
                       "A4"."COMM" "COMM_6",
                       "A4"."DEPTNO" "QCSJ_C000000000400000_7",
                       "A3"."DEPTNO" "QCSJ_C000000000400001",
                       "A3"."DNAME" "DNAME_9"
                FROM   SCOTT."EMP" "A4",
                       SCOTT."DEPT" "A3"
                WHERE  "A4"."DEPTNO"="A3"."DEPTNO") "A2"
       ) "A1"

PL/SQL procedure successfully completed.

SQL>