Setup PLAN_TABLE for all schema users

Each schema may require a PLAN_TABLE to debug performance so instead of creating the PLAN_TABLE within each schema, one can do the following and allow all schemas access to PLAN_TABLE:

— ? is the ORACLE_HOME
SQL> connect system
Password:
SQL> @?/rdbms/admin/utlxplan.sql
— create public synonym
SQL> CREATE PUBLIC SYNONYM PLAN_TABLE FOR PLAN_TABLE;
— Grant everyone access to PLAN_TABLE
SQL> GRANT ALL ON PLAN_TABLE TO PUBLIC;

Grant PLUSTRACE role to everyone
– run as sysdba
SQL> connect /as sysdba
Connected.
SQL> @?/sqlplus/admin/plustrce;

SQL> grant plustrace to public;

Grant succeeded.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s