Configuring an Oracle Database to have a ‘self-cleaning’ PLAN_TABLE

On the database server, go to $ORACLE_HOME/rdbms/admin

copy utlxplan.sql to utlxplan.sql.original – you don’t want to lose the original Oracle script.

Edit utlxplan.sql and change it to the following :

...
CREATE GLOBAL TEMPORARY TABLE PLAN_TABLE (
  ...
  ...
) ON COMMIT PRESERVE ROWS;
...

Save the new version and run sqlplus as SYS or a SYSDBA user, then :

• run the utlxplan.sql script as normal
• create a public synonym
• grant ‘ALL’ access to public, or a few chosen users.

SQL> start ?/rdbms/admin/utlxplan
Table created.

SQL> create public synonym PLAN_TABLE for sys.plan_table;
Synonym created.

SQL> grant all on sys.plan_table to public;
Grant succeeded.

Now when developers create explain plans, the rows will remain in the table until they logoff, whereupon they will automagically delete themselves. No more checking that PLAN_TABLE is clean and that you have enough free space in whichever tablespace – it all lives in the temporary tablespace now. You won’t find a tablespace_name in XXX_SEGMENTS or XXX_TABLES for it, but the TEMPORARY flag is set to ‘Y’ in XXX_TABLES.

SQL> select table_name,tablespace_name,temporary
2  from user_tables
3  where table_name = 'PLAN_TABLE';

TABLE_NAME                     TABLESPACE_NAME                T
------------------------------ ------------------------------ -
PLAN_TABLE                                                    Y

1 row selected.

Note: you need to configure TOAD to use a plan table called PLAN_TABLE if you want to have the self-cleaning abilities from within TOAD as well as developers using SQL*Plus, for example, and running EXPLAIN PLAN FOR … statements.

Cheers,   Norm [TeamT]