Script to generate Execution Plan output in a detailed manner

Please change the below script as per your need.

set lines 150
set echo on
set pages 500

delete from  plan_table where statement_id=’dba’;
commit;

spool eplan11g
explain plan set statement_id=’dba’ for
Please give your select query here

select * from table(dbms_xplan.display());
spool off


For example:
——————
set lines 150
set echo on
set pages 500

delete from  plan_table where statement_id=’oracle’;
commit;

spool eplan11g

explain plan set statement_id=’oracle’ for
SELECT p.table_name “Parent Table”, c.table_name “Child Table”,
       p.constraint_name “Parent Constraint”, c.constraint_name “Child Constraint”
       FROM user_constraints p
       JOIN user_constraints c ON(p.constraint_name=c.r_constraint_name)
       WHERE (p.constraint_type = ‘P’ OR p.constraint_type = ‘U’)
       AND c.constraint_type = ‘R’
       AND p.table_name = UPPER(‘&table_name’) and p.owner=’ABC’;
select * from table(dbms_xplan.display());
spool off
  • March 17, 2016 | 13 views
  • Comments