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

Comments:

Post a Comment:
  • HTML Syntax: NOT allowed
About

Mike Dietrich - Oracle Mike Dietrich
Master Product Manager - Database Upgrade & Migrations - Oracle Corp

Based near Munich/Germany and spending plenty of time in airplanes to run either upgrade workshops or work onsite or remotely with reference customers. Acting as interlink between customers/partners and the Upgrade Development.

Follow me on TWITTER

Contact me via LinkedIn or XING

Search

Archives
« May 2015
SunMonTueWedThuFriSat
     
1
2
3
4
5
6
7
8
9
10
12
13
14
15
16
17
19
20
21
22
23
24
25
26
27
28
29
30
31
      
Today
Oracle related Tech Blogs
Slides Download Center
Visitors since 17-OCT-2011
White Paper and Docs
Workshops
Viewlets and Videos
This week on my Rega/iPod/CD
Workshop Map
Upgrade Reference Papers