cancel
Showing results for 
Search instead for 
Did you mean: 

Invalid Objects

former_member182034
Active Contributor
0 Kudos

Dear all,

I have upgraded oracle from 10205 to 11203 and executed all steps as per upgrade guide.

I executed utlrp.sql scripts but still getting following invalid Objects.

SQL> SELECT OBJECT_NAME FROM DBA_OBJECTS WHERE STATUS = 'INVALID';

OBJECT_NAME

--------------------------------------------------------------------

DBMS_METADATA_INT

DBMS_METADATA_UTIL

XDB_MIGRATESCHEMA

3 rows selected.

Would you guide me which scripts I have to execute to compile above objects?

Regards,

Accepted Solutions (1)

Accepted Solutions (1)

stefan_koehler
Active Contributor
0 Kudos

Hi Abdul,

> Would you guide me which scripts I have to execute to compile above objects?

The invalid PL/SQL packages DBMS_METADATA_INT and DBMS_METADATA_UTIL are well known issues in that upgrade procedure. Pleas try the following (as SYS):

SQL> alter package sys.dbms_metadata_util compile;

SQL> alter package sys.dbms_metadata_int compile;

You have to run the following SQL script to fix this issue, if the compilation fails with an error like "keyword VALUE used as a defined name" or "keyword TYPE used as a defined name":

SQL> @?/rdbms/admin/catpprvt.sql

If you get another error - please post it here.

PL/SQL package XDB_MIGRATESCHEMA is also a known issue as no XDB is installed by default in a SAP environment. You can drop the package or keep it as it is. It is not a problem.

Regards

Stefan

former_member182034
Active Contributor
0 Kudos

hi Stefan,

Thanks for your reply.

out of said commands

SQL> alter package sys.dbms_metadata_util compile;

Package altered.

SQL> alter package sys.dbms_metadata_int compile;

Package altered.

SQL> SELECT OBJECT_NAME FROM DBA_OBJECTS WHERE STATUS = 'INVALID';

OBJECT_NAME

------------------------------------------------------------------------

XDB_MIGRATESCHEMA

how can i drop this?

Regards,

stefan_koehler
Active Contributor
0 Kudos

Hi Abdul,

> how can i drop this?

As previously mentioned this is a known issue with Oracle 11.2.0.3 patch bundle 24 or 25 for Windows. If you are running on this you can safely drop this package this way:

SQL> drop package SYS.XDB_MIGRATESCHEMA;

Regards

Stefan

Answers (2)

Answers (2)

Reagan
Advisor
Advisor
0 Kudos
Former Member
0 Kudos

Try to manually compile DBMS_* packages with

SQL> alter package <owner>.<package_name> compile;

command. If errors occur display them with

SQL> show errors

command and attach to message. Also check type of XDB_MIGRATESCHEMA object.