Oracle Plan Table

Here are SQL scripts to use the Oracle plan table:

CREATE TABLE TEMP_PLAN_TABLE (
  STATEMENT_ID VARCHAR2(30),
  TIMESTAMP DATE,
  REMARKS VARCHAR2(80),
  OPERATION VARCHAR2(30),
  OPTIONS VARCHAR2(30),
  OBJECT_NODE VARCHAR2(128),
  OBJECT_OWNER VARCHAR2(30),
  OBJECT_NAME VARCHAR2(30),
  OBJECT_INSTANCE NUMBER(38),
  OBJECT_TYPE VARCHAR2(30),
  OPTIMIZER VARCHAR2(255),
  SEARCH_COLUMNS NUMBER,
  ID NUMBER(38),
  PARENT_ID NUMBER(38),
  POSITION NUMBER(38),
  COST NUMBER(38),
  CARDINALITY NUMBER(38),
  BYTES NUMBER(38),
  OTHER_TAG VARCHAR2(255),
  PARTITION_START VARCHAR2(255),
  PARTITION_STOP VARCHAR2(255),
  PARTITION_ID NUMBER(38),
  OTHER LONG,
  DISTRIBUTION VARCHAR2(30)
);

DELETE FROM TEMP_PLAN_TABLE;
EXPLAIN PLAN INTO TEMP_PLAN_TABLE FOR
SELECT * FROM DUAL
;

SELECT LPAD(' ', level-1) || operation || ' (' || options || ')' "Operation"
, object_owner||'.'||object_name||
  (SELECT ' (on '||TABLE_OWNER||'.'||TABLE_NAME||')'
   FROM ALL_INDEXES
   WHERE OWNER=TPT.OBJECT_OWNER
   AND INDEX_NAME=TPT.OBJECT_NAME
   AND TPT.OPERATION='INDEX' ) "Object"
FROM TEMP_PLAN_TABLE TPT
START WITH ID = 0
CONNECT BY PRIOR ID=PARENT_ID

DROP TABLE TEMP_PLAN_TABLE;