Re: how to explain plan to table other than plan_table?

"Ben" <balvey_at_comcast.net> wrote in message news:1157051333.323979.300420_at_m79g2000cwm.googlegroups.com...
>
> I want to use one centrally located PLAN_TABLE for all of our
> developers to have access to use. Be it in the SYSTEM schema or some
> other schema. How can USER_A explain plan on a statement logged in as
> USER_A and use SYSTEM.PLAN_TABLE?
>
> Also, I thought it was utlxplan.sql that created the PLAN_TABLE?
>
> Thanks
>

Take a leaf out of the 10g book:

Create a global temporary table called plan_table$ in the SYS schema (or SYSTEM schema if that makes you feel safer).

Create a public synonym called plan_table for this plan_table$.

Grant select, insert, update, delete on plan_table to public;

Then run the script that creates the dbms_xplan package and grants it to public. In 9i this is in dbmsutill.sql, in 10g its in dbmsxpln.sql - which is called by catplan.sql - which is called by catalog.sql

-- 
Regards

Jonathan Lewis
http://www.oracle.com/technology/community/oracle_ace/ace1.html#lewis

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html