To check the state of the Oracle Data Dictionary for diagnosing upgrades and migrations, use one of three methods.
Example 6-1 Run the
dbupgdiag.sql script collects
diagnostic information about the status of the database, either before or
after the upgrade. Download the script from My Oracle Support note 556610,
and run the script as the database
SYS user. The script
generates the diagnostic information in a readable format, in a log file
with the name file
sid is the Oracle system
identifier for the database, and
timestamp is the time that the
file is generated.
For example, where you download and place the script in the directory
/u01/dbupdiag-script/ $ sqlplus / as sysdba sql> alter session set nls_language='American'; sql> @dbupgdiag.sql sql> exit
You can run the script in SQL*Plus both before the upgrade on the source database, and after the upgrade on the upgraded database. For more information about the script, refer to the instructions and the output example file in My Oracle Support Note 556610.1.
Example 6-2 Run a SQL Query on DBA_REGISTRY
To show the current state of the dictionary, perform a SQL query similar to the following example:
SQL> spool /tmp/regInvalid.out SQL> set echo on -- query registry SQL> set lines 80 pages 100 SQL> select substr(comp_id,1,15) comp_id,substr(comp_name,1,30) comp_name,substr(version,1,10) version_full,status from dba_registry order by comp_id;
Example 6-3 Run SQL Queries to Check for Invalid Objects
To check for invalid objects, you can perform SQL queries, similar to the following examples:
List all the invalid objects in the database:
SQL> select owner, object_name, object_type from dba_invalid_objects order by owner, object_type, object_name;
List any invalid Oracle-maintained objects in the database.
SQL> select owner, object_name, object_type from sys.dba_invalid_objects where oracle_maintained='Y';
After you have upgraded the database, and you have run
utlrp.sql, all Oracle-maintained objects should be valid.
List invalid application objects in the database:
SQL> select owner, object_name, object_type from sys.dba_invalid_objects where oracle_maintained='N';