SQL Text Expansion in Oracle 12c

This procedure will be handy if you are asked to tune a query, which looks like short, but actual complexity hidden by the views. You can use this procedure to expand the view definition into the query.

https://docs.oracle.com/database/121/ARPLS/d_util.htm#ARPLS73973

SQL> variable x clob
SQL> begin
  2    dbms_utility.expand_sql_text(
  3      input_sql_text => 'select * from hr.emp_details_view where employee_id=107',
  4      output_sql_text => :x
  5    );
  6  end;
  7  /

PL/SQL procedure successfully completed.

SQL> print x

X
--------------------------------------------------------------------------------
SELECT "A1"."EMPLOYEE_ID" "EMPLOYEE_ID","A1"."JOB_ID" "JOB_ID","A1"."MANAGER_ID"

SQL> set long 2000
SQL> set longc 2000
SQL> set pages 999
SQL> print x

X
--------------------------------------------------------------------------------
SELECT "A1"."EMPLOYEE_ID" "EMPLOYEE_ID","A1"."JOB_ID" "JOB_ID","A1"."MANAGER_ID"
"MANAGER_ID","A1"."DEPARTMENT_ID" "DEPARTMENT_ID","A1"."LOCATION_ID" "LOCATION_
ID","A1"."COUNTRY_ID" "COUNTRY_ID","A1"."FIRST_NAME" "FIRST_NAME","A1"."LAST_NAM
E" "LAST_NAME","A1"."SALARY" "SALARY","A1"."COMMISSION_PCT" "COMMISSION_PCT","A1
"."DEPARTMENT_NAME" "DEPARTMENT_NAME","A1"."JOB_TITLE" "JOB_TITLE","A1"."CITY" "
CITY","A1"."STATE_PROVINCE" "STATE_PROVINCE","A1"."COUNTRY_NAME" "COUNTRY_NAME",
"A1"."REGION_NAME" "REGION_NAME" FROM  (SELECT "A7"."EMPLOYEE_ID" "EMPLOYEE_ID",
"A7"."JOB_ID" "JOB_ID","A7"."MANAGER_ID" "MANAGER_ID","A7"."DEPARTMENT_ID" "DEPA
RTMENT_ID","A6"."LOCATION_ID" "LOCATION_ID","A4"."COUNTRY_ID" "COUNTRY_ID","A7".
"FIRST_NAME" "FIRST_NAME","A7"."LAST_NAME" "LAST_NAME","A7"."SALARY" "SALARY","A
7"."COMMISSION_PCT" "COMMISSION_PCT","A6"."DEPARTMENT_NAME" "DEPARTMENT_NAME","A
5"."JOB_TITLE" "JOB_TITLE","A4"."CITY" "CITY","A4"."STATE_PROVINCE" "STATE_PROVI
NCE","A3"."COUNTRY_NAME" "COUNTRY_NAME","A2"."REGION_NAME" "REGION_NAME" FROM HR
."EMPLOYEES" "A7",HR."DEPARTMENTS" "A6",HR."JOBS" "A5",HR."LOCATIONS" "A4",HR."C
OUNTRIES" "A3",HR."REGIONS" "A2" WHERE "A7"."DEPARTMENT_ID"="A6"."DEPARTMENT_ID"
AND "A6"."LOCATION_ID"="A4"."LOCATION_ID" AND "A4"."COUNTRY_ID"="A3"."COUNTRY_I
D" AND "A3"."REGION_ID"="A2"."REGION_ID" AND "A5"."JOB_ID"="A7"."JOB_ID") "A1" W
HERE "A1"."EMPLOYEE_ID"=107

SQL> set lin 20000

SQL> print x

X
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SELECT "A1"."EMPLOYEE_ID" "EMPLOYEE_ID","A1"."JOB_ID" "JOB_ID","A1"."MANAGER_ID" "MANAGER_ID","A1"."DEPARTMENT_ID" "DEPARTMENT_ID","A1"."LOCATION_ID" "LOCATION_ID","A1"."COUNTRY_ID" "COUNTRY_ID","A1"."FIRST_NAME" "FIRST_NAME","A1"."LAST_NAME" "LAST_NAME","A1"."SALARY" "SALARY","A1"."COMMISSION_PCT" "COMMISSION_PCT","A1"."DEPARTMENT_NAME" "DEPARTMENT_NAME","A1"."JOB_TITLE" "JOB_TITLE","A1"."CITY" "CITY","A1"."STATE_PROVINCE" "STATE_PROVINCE","A1"."COUNTRY_NAME" "COUNTRY_NAME","A1"."REGION_NAME" "REGION_NAME" FROM  (SELECT "A7"."EMPLOYEE_ID" "EMPLOYEE_ID","A7"."JOB_ID" "JOB_ID","A7"."MANAGER_ID" "MANAGER_ID","A7"."DEPARTMENT_ID" "DEPARTMENT_ID","A6"."LOCATION_ID" "LOCATION_ID","A4"."COUNTRY_ID" "COUNTRY_ID","A7"."FIRST_NAME" "FIRST_NAME","A7"."LAST_NAME" "LAST_NAME","A7"."SALARY" "SALARY","A7"."COMMISSION_PCT" "COMMISSION_PCT","A6"."DEPARTMENT_NAME" "DEPARTMENT_NAME","A5"."JOB_TITLE" "JOB_TITLE","A4"."CITY" "CITY","A4"."STATE_PROVINCE" "STATE_PROVINCE","A3"."COUNTRY_NAME" "COUNTRY_NAME","A2"."REGION_NAME" "REGION_NAME" FROM HR."EMPLOYEES" "A7",HR."DEPARTMENTS" "A6",HR."JOBS" "A5",HR."LOCATIONS" "A4",HR."COUNTRIES" "A3",HR."REGIONS" "A2" WHERE "A7"."DEPARTMENT_ID"="A6"."DEPARTMENT_ID" AND "A6"."LOCATION_ID"="A4"."LOCATION_ID" AND "A4"."COUNTRY_ID"="A3"."COUNTRY_ID" AND "A3"."REGION_ID"="A2"."REGION_ID" AND "A5"."JOB_ID"="A7"."JOB_ID") "A1" WHERE "A1"."EMPLOYEE_ID"=107

Use SQL Developer to format the SQL Statement: