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
Senior Principal Technologist - Database Upgrade Development Group - Oracle Corporation

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

Contact me either via XING or LinkedIn

Search

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