使用数据泵（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)