Set up EXPLAIN PLAN to be available to the world

Just perform the SQL below.

All developers would want to take care to issue a

DELETE FROM PLAN_TABLE

or

TRUNCATE PLAN_TABLE

before using EXPLAIN PLAN. This will not cause concurrency issue and they will not see the other developers plans (cause of the GLOBAL TEMPORARY TABLE).

gerardnico@orcl>>connect sys/syspassword AS sysdba
Connected.
sys@orcl>CREATE USER utility identified BY utility;
 
USER created.
 
sys@orcl>GRANT CREATE session, CREATE PROCEDURE, CREATE TABLE, CREATE TRIGGER TO utility;
 
Grant succeeded.
 
sys@orcl>connect utility/utility;
Connected.
utility@orcl>create GLOBAL TEMPORARY TABLE PLAN_TABLE (
  2          statement_id       VARCHAR2(30),
  3          plan_id            NUMBER,
  4          TIMESTAMP          DATE,
  5          remarks            VARCHAR2(4000),
  6          operation          VARCHAR2(30),
  7          options            VARCHAR2(255),
  8          object_node        VARCHAR2(128),
  9          object_owner       VARCHAR2(30),
 10          object_name        VARCHAR2(30),
 11          object_alias       VARCHAR2(65),
 12          object_instance    numeric,
 13          object_type        VARCHAR2(30),
 14          optimizer          VARCHAR2(255),
 15          search_columns     NUMBER,
 16          id                 numeric,
 17          parent_id          numeric,
 18          DEPTH              numeric,
 19          position           numeric,
 20          cost               numeric,
 21          cardinality        numeric,
 22          bytes              numeric,
 23          other_tag          VARCHAR2(255),
 24          partition_start    VARCHAR2(255),
 25          partition_stop     VARCHAR2(255),
 26          partition_id       numeric,
 27          other              LONG,
 28          distribution       VARCHAR2(30),
 29          cpu_cost           numeric,
 30          io_cost            numeric,
 31          temp_space         numeric,
 32          access_predicates  VARCHAR2(4000),
 33          filter_predicates  VARCHAR2(4000),
 34          projection         VARCHAR2(4000),
 35          TIME               numeric,
 36          qblock_name        VARCHAR2(30),
 37          other_xml          clob
 38  ) ON COMMIT PRESERVE ROWS;
 
TABLE created.
 
utility@orcl>grant ALL privileges ON plan_table TO PUBLIC;
 
Grant succeeded.
utility@orcl>connect sys/syspassword AS sysdba;
Connected.
sys@orcl>create PUBLIC SYNONYM plan_table FOR UTILITY.plan_table;
 
SYNONYM created.