AskToad.com -- Home of your Toad Oracle KnowledgeBase

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]

— Norman Dunbar 2006/04/19 11:30



Personal tools
navigation

11/30/09