Drop SYS.PLAN_TABLE$ prior to upgrading

Dropping a possibly exististing table SYS.PLAN_TABLE$ and public synonym PUBLIC.PLAN_TABLE will avoid having invalid packages and a non-valid Oracle Server component in DBA_REGISTRY.

This will apply to all upgrades to 10.2.0.4, 11.1.0.6 and 11.1.0.7.

SQL> drop table plan_table$;
SQL> drop public synonym plan_table;

This issue has been introduced with the package DBMS_SQLPA for the SQL Performance Analyzer.

If you hit this issue already the following actions will solve it:

SQL> @catplan.sql -- recreate the plan table
SQL> @dbmsxpln.sql -- reload dbms_xplan spec
SQL> @prvtxpln.plb -- reload dbms_xplan implementation
SQL> @prvtspao.plb -- reload dbms_sqlpa
SQL> alter package SYS.DBMS_SUMADVISOR compile;
SQL> alter package SYS.DBMS_SUMADVISOR compile body;

You’ll find further information:

Alert Note:782735.1
Note:605317.1
Note:736353.1

Leave a Reply

Your email address will not be published. Required fields are marked *