Alex Lima

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