一次oracle 11g 数据泵报错的解决过程-CSDN博客

本文详细描述了使用 Oracle 数据泵导出数据时遇到的报错问题，包括 ORA-24001 错误，以及如何通过删除无效的数据库对象、执行特定的存储过程和重新加载样式表等步骤来解决这些问题。最终，通过这些操作成功执行了数据导出任务。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

一：今天用数据泵导出数据报错, oracle rac 两个实例都报错，下面是rac1上的报错，rac2上报错只是红色部分不一样，变成了

ORA-24001: cannot create QUEUE_TABLE, SYS.KUPC$DATAPUMP_QUETAB_2 already exists，

[oracle@rac1 ceshi_bak]$ expdp system/manager123 directory=bak dumpfile=tb_storetemp_coloraa.dmp tables=ustest_zh_cn.tb_storetemp_color ;

Export: Release 11.2.0.3.0 - Production on Fri Sep 11 20:18:48 2015

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
ORA-31626: job does not exist
ORA-31637: cannot create job SYS_EXPORT_TABLE_03 for user SYSTEM
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPV$FT_INT", line 798
ORA-39244: Event to disable dropping null bit image header during relational select
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86
ORA-06512: at "SYS.KUPC$QUE_INT", line 1825
ORA-24001: cannot create QUEUE_TABLE, SYS.KUPC$DATAPUMP_QUETAB_1 already exists

在网上查了查找到以下文档
查看一下数据库中的无效的数据库对象
SQL> select object_name from all_objects where owner='SYS' and status='INVALID';
OBJECT_NAME
------------------------------
KUPC$DATAPUMP_QUETAB_1
SYSNTfVzZ/2KLQNTgQ8CoAAJA1A==
SYSNTfVzZ/2KNQNTgQ8CoAAJA1A==
SYSNTfVzZ/2KPQNTgQ8CoAAJA1A==
AQ$_KUPC$DATAPUMP_QUETAB_V
AQ$_KUPC$DATAPUMP_QUETAB_E
AQ$_KUPC$DATAPUMP_QUETAB_F
AQ$KUPC$DATAPUMP_QUETAB
SBF_PUBLIC

9 rows selected.
发现无效对象中有datapump组件，

解决方法

1. 删除 datapump queue 表，在sql下执行下面

SQL> exec dbms_aqadm.drop_queue_table(queue_table =>'SYS.KUPC$DATAPUMP_QUETAB_2', force=> TRUE);

PL/SQL procedure successfully completed.

2.执行下面存储过程再次创建

BEGIN
dbms_aqadm.create_queue_table(queue_table => 'SYS.KUPC$DATAPUMP_QUETAB_2',
multiple_consumers => TRUE,
queue_payload_type =>'SYS.KUPC$_MESSAGE',
comment => 'DataPump Queue Table',
compatible=>'8.1.3');
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE = -24001 THEN NULL;
ELSE RAISE;
END IF;
END;

3，调用$ORACLE_HOME/RDBMS/ADMIN/utlrp.sql对数据库中的invalid对象进行重新编译

4 再次查下一个无效组件
SQL> select object_name from all_objects where owner='SYS' and status='INVALID';

OBJECT_NAME
------------------------------
SBF_PUBLIC
5 重启一下实例，最后expdp可以正常运行了，此解决方法原始出处是在metalink 上一篇文章，

我的库继续报错，此时报错：

ORA-39006: internal error
ORA-39213: Metadata processing is not available

然后按着下面继续操作，

二：仍然是网上的文章

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options
ORA-39006: internal error
ORA-39213: Metadata processing is not available

Metalink确认是一个又来已经的已知问题，可以尝试执行如下步骤解决：

connect / as sysdba
execute sys.dbms_metadata_util.load_stylesheets;

我的系统中，以上处理过程有效：

Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> exec dbms_metadata_util.load_stylesheets

PL/SQL procedure successfully completed.

SQL> exit

此后导出可以顺利执行。

可是我的仍然报错：

三：我的如果仍然报错：

[ oracle@rac1 ~]$ expdp system/manager123 directory=bak dumpfile=tb_storetemp_coloraa.dmp tables=ustest_zh_cn.tb_storetemp_color ;

Export: Release 11.2.0.3.0 - Production on Fri Sep 11 21:41:18 2015

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
ORA-39006: internal error
ORA-39065: unexpected master process exception in DISPATCH
ORA-01403: no data found
ORA-39097: Data Pump job encountered unexpected error 100

再运行脚本：

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

PL/SQL procedure successfully completed.