The package VERIFY_DATAPUMP checks the objects created by different DataPump specific scripts located in $ORACLE_HOME/rdbms/admin, which are automatically called within
Different versions have different DataPump objects in the data dictionary. At this moment the package works only with 11gR2 and 12c.
The procedure CHECK_ALL is the core of the package. It calls other internal procedures (similar to corresponding scripts) and fills a global collection which is verified and emptied at the end.
1. Download the file verify_datapump.zip and decompress it into a certain directory. Then install the version depending procedures and packages into your database with:
connect / as sysdba @verify_datapump_header.sql @verify_datapump_body.sql
The body is very large and needs up to 10-15 minutes to complete.
2. Start the procedure in SQL*Plus (as SYSDBA) with:
@verify_datapump_start.sql
3. The results are printed into a trace file:
SQL> @verify_datapump_start.sql For more details please check the trace file: C:\DATABASES\O12102\diag\rdbms\o12102\o12102\trace\o12102_ora_7868_VERIFY_DATAPUMP.trc
The file contains:
Trace file C:\DATABASES\O12102\diag\rdbms\o12102\o12102\trace\o12102_ora_7880_VERIFY_DATAPUMP.trc Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options Windows NT Version V6.1 Service Pack 1 CPU : 4 - type 8664, 2 Physical Cores Process Affinity : 0x0x0000000000000000 Memory (Avail/Total): Ph:2013M/7865M, Ph+PgF:9800M/15729M Instance name: o12102 Redo thread mounted by this instance: 1 Oracle process number: 24 Windows thread id: 7880, image: ORACLE.EXE (SHAD) *** 2015-06-25 13:00:42.019 *** SESSION ID:(6.22626) 2015-06-25 13:00:42.019 *** CLIENT ID:() 2015-06-25 13:00:42.019 *** SERVICE NAME:(SYS$USERS) 2015-06-25 13:00:42.019 *** MODULE NAME:(sqlplus.exe) 2015-06-25 13:00:42.019 *** CLIENT DRIVER:(SQL*PLUS) 2015-06-25 13:00:42.019 *** ACTION NAME:() 2015-06-25 13:00:42.019 A-0000000001 25.06.2015 13:00.42 ================================================================================ A-0000000002 25.06.2015 13:00.42 Version: 12.1.0.2.0 A-0000000003 25.06.2015 13:00.42 Compatiblity: 12.1.0.2.0 A-0000000004 25.06.2015 13:00.42 CATALOG version: 12.1.0.2.0 A-0000000005 25.06.2015 13:00.42 XDB Version: 12.1.0.2.0 A-0000000006 25.06.2015 13:00.42 Major Database Release: 12 A-0000000007 25.06.2015 13:00.42 Database Maintenance Release: 1 A-0000000008 25.06.2015 13:00.42 ================================================================================ I-0000000009 25.06.2015 13:00.42 prereqs TABLE SYS.DUAL [VALID] I-0000000010 25.06.2015 13:00.42 prereqs SELECT priv. on SYS.DUAL to PUBLIC [GRANTED] I-0000000011 25.06.2015 13:00.42 prereqs SYNONYM PUBLIC.DUAL [VALID] I-0000000012 25.06.2015 13:00.42 prereqs Objects named SYS or SYSTEM not found. [OK] I-0000000013 25.06.2015 13:00.42 dbmsmeta.sql TYPE SYS.KU$_PARSED_ITEM [VALID] I-0000000014 25.06.2015 13:00.42 dbmsmeta.sql EXECUTE priv. on SYS.KU$_PARSED_ITEM to PUBLIC [GRANTED] ... E-0000000945 25.06.2015 13:00.42 catmetviews.sql VIEW SYS.KU$_ADD_SNAP_VIEW [NOT FOUND] W-0000000946 25.06.2015 13:00.42 catmetviews.sql VIEW SYS.KU$_REFGROUP_VIEW [INVALID] ... E-0000001782 25.06.2015 13:00.48 catmetgrant2.sql SELECT priv. on SYS.KU$_ADD_SNAP_VIEW to SELECT_CATALOG_ROLE [MISSING] ... A-0000006356 25.06.2015 13:00.51 ================================================================================ A-0000006357 25.06.2015 13:00.51 Please run the following scripts using SQL*Plus connected to the database A-0000006358 25.06.2015 13:00.51 as SYSDBA. The scripts are located in directory $ORACLE_HOME/rdbms/admin: A-0000006359 25.06.2015 13:00.51 A-0000006360 25.06.2015 13:00.51 catmetviews.sql A-0000006361 25.06.2015 13:00.51 catmetgrant2.sql A-0000006362 25.06.2015 13:00.51 utlrp.sql A-0000006363 25.06.2015 13:00.51 ================================================================================
Or, in case the DataPump catalog is complete:
A-0000006356 25.06.2015 13:12.31 ================================================================================ A-0000006357 25.06.2015 13:12.31 DataPump dictionary is up to date. No further steps are required. A-0000006358 25.06.2015 13:12.31 ================================================================================
4. Delete the installed objects from data dictionary with:
@verify_datapump_drop
DataPump within Multitenant Databases
If you are running a Multitenant Database then you should determine whether the issue that you are encountering is at the container level or with one specific pluggable databases (PDBs). Once this is established, then run the script in the root (CDB$ROOT) and in the PDB that is experiencing the issue. Both outputs should be verified to determine whether “DataPump dictionary is up to date”.