EXPDP導數報ORA-00942案例

使用數據泵（expdp）導數時遇到了ORA-31626 & ORA-00942 錯誤，資料庫版本為Oracle Database 10g Release 10.2.0.5.0,具體錯誤如下所示：

$ expdp system/xxx tables=xxx.xxx directory=DUMPDIR dumpfile=xxxx.dmp logfile=xxx.log;

Export: Release 10.2.0.5.0 - 64bit Production on Saturday, 27 July, 2019 10:39:07

Copyright (c) 2003, 2007, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Release 10.2.0.5.0 - 64bit Production

ORA-31626: job does not exist

ORA-00942: table or view does not exist

ORA-00942: table or view does not exist

ORA-00942: table or view does not exist

ORA-00942: table or view does not exist

ORA-00942: table or view does not exist

ORA-00942: table or view does not exist

ORA-00942: table or view does not exist

ORA-00942: table or view does not exist

ORA-00942: table or view does not exist

ORA-00942: table or view does not exist

ORA-00942: table or view does not exist

ORA-00942: table or view does not exist

檢查驗證發現表確實是存在的，搜索metalink，發現官方文檔有介紹：“DataPump Export (EXPDP) Reports ORA-942 Even If Table Exists (Doc ID 1371613.1)”，下麵的的操作步驟基本按照官方文檔的介紹處理：

在資料庫開啟跟蹤

SQL> alter system set events '942 trace name errorstack level 3';

System altered.

然後使用expdp命令導數

在資料庫關閉跟蹤

SQL> alter system set events '942 trace name errorstack off';

System altered.

檢查告警日誌，就能發現對應的跟蹤文件，如下所示：

Errors in file /u01/app/oracle/admin/epps/udump/epps_ora_15524.trc:

ORA-00942: table or view does not exist

Sat Jul 27 10:39:08 CST 2019

The value (30) of MAXTRANS parameter ignored.

在跟蹤文件，我們會發現PL/SQL Call Stack信息。這個跟官方文檔的內容有所差別，這個也正常，一模一樣的錯誤信息還是很少見的。

根據官方文檔提示， 這個是因為DataPump內部包損壞了（damaged DataPump internal package),如果查單純看包'DBMS_DATAPUMP'，發現其狀態是VALID，對這些沒有多少研究。所以不清楚更深一層次的原因！

SQL> select owner

  2      , object_name

  3      , object_type

  4      , status 

  5  from  dba_objects where object_name='DBMS_DATAPUMP';

OWNER                          OBJECT_NAME              OBJECT_TYPE         STATUS

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

SYS                            DBMS_DATAPUMP            PACKAGE             VALID

SYS                            DBMS_DATAPUMP            PACKAGE BODY        VALID

PUBLIC                         DBMS_DATAPUMP            SYNONYM             VALID

SQL> 

官方文檔"How To Reload Datapump Utility EXPDP/IMPDP (文檔 ID 430221.1)"給出瞭如何解決這個問題（個人根據下麵步驟解決了這個問題）：

NOTE:
For running catproc.sql, please refer to
Note 863312.1 - Best Practices for running catalog, catproc and utlrp script

• The catalog or catproc script should be run after the database has been opened with startup migrate or startup upgrade depending on version.
• The catalog and catproc script should not be run when the database is opened with unrestricted access.?
  This can cause the database to experience performance issues and can even lead to a hanging situation.

In some cases DataPump utility may get corrupted and we need to recreate DataPump utility to overcome internal corruption. To do this, run specified scripts for Oracle version that you are running as given below.

Note:  Run the following as sysdba user:

SQL> connect / as sysdba

For Oracle version 10.1 :

-- 1. Catdp.sql orders the installation of all its components including the Metadata API which was previously installed separately. By default catproc.sql invoke this script.

SQL> @$ORACLE_HOME/rdbms/admin/catdp.sql


-- 2. dbmspump.sql will create DBMS procedures for dataPUMP

SQL> @$ORACLE_HOME/rdbms/admin/dbmspump.sql

 
For Oracle version 10.2:

-- 1.Catdph.sql will Re-Install DataPump types and views

SQL> @$ORACLE_HOME/rdbms/admin/catdph.sql


-- Note:
-- If XDB is installed, then it is required to run "catmetx.sql" script also.
-- Use this code to verify if XDB is installed:

SQL> select substr(comp_name,1,30) comp_name,
     substr(comp_id,1,10) comp_id,
     substr(version,1,12) version,
     status
     from dba_registry;

-- Sample output if XDB installed,
Oracle XML Database    XDB    -version-    VALID


-- 2.prvtdtde.plb will re-install tde_library packages

SQL> @$ORACLE_HOME/rdbms/admin/prvtdtde.plb


-- 3. Catdpb.sql will Re-Install DataPump packages

SQL> @$ORACLE_HOME/rdbms/admin/catdpb.sql


-- 4.Dbmspump.sql will Re-Install DBMS DataPump objects

SQL> @$ORACLE_HOME/rdbms/admin/dbmspump.sql


-- 5. To recompile  invalid objects, if any

SQL> @$ORACLE_HOME/rdbms/admin/utlrp.sql

 
For Oracle version 11g and higher prior to 12c:

-- 1.Catproc.sql

SQL> @$ORACLE_HOME/rdbms/admin/catproc.sql


-- 2.To recompile invalid objects, if any

SQL> @$ORACLE_HOME/rdbms/admin/utlrp.sql

For Oracle version 12c:

Note 1: Prior rebuilding DP catalog in 12.1.0.2 CDB , install?Patch 25139545 as alerted in?Document 2175021.1 - "Alert - Multitenant Customers: The objects created by the post-install steps of 12.1.0.2 Generic DataPump Patches Are not Shared Across All PDBS".

Note 2: For issues regarding KU$ Invalid Objects Owned by SYS after upgrading or applying datapatch, refer to?Document 2289785.1 to rebuild Datapump.

Rebuild the DataPump packages with the following steps.

Under the ORACLE_HOME, execute:
cd rdbms/admin

-- run the dpload.sql in the CDB with all of the PDBs open

From a SQL*Plus session, connect as sysdba

and then run dpload.sql:

@dpload.sql

on the affected database.

Note: If DataPump catalog is not valid in a PDB, same step should be executed to validate the DP catalog on a pluggable database.

 
Additional Resources

Community: Database Utilities

Still have questions? Use the above community to search for similar discussions or start a new discussion on this subject.

參考資料：

       DataPump Export (EXPDP) Reports ORA-942 Even If Table Exists (Doc ID 1371613.1)

How To Reload Datapump Utility EXPDP/IMPDP (文檔 ID 430221.1)