oracle expdp 导出存储过程,记一次 oracle expdp 导出错误

前几天在一个环境上做了一个expdp 导出测试，但是发现expdp有问题，操作系统是aix 6105，oracle版本是11.1.0.7，执行expdp时候报错如下

Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production

With the Partitioning, Oracle Label Security, OLAP, Data Mining

and Real Application Testing options

ORA-31626: job does not exist

ORA-31637: cannot create job SYS_EXPORT_FULL_01 for user SYSTEM

ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95

ORA-06512: at "SYS.KUPV$FT_INT", line 736

ORA-39080: failed to create queues "KUPC$C_1_20140214183234" and "" for Data Pump job

ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95

ORA-06512: at "SYS.KUPC$QUE_INT", line 1665

ORA-24203: operation failed, queue table SYS.KUPC$DATAPUMP_QUETAB has errors

后来查看一下数据库中的无效的数据库对象

SQL> select object_name from all_objects where owner='SYS' and status='INVALID';

OBJECT_NAME

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

KUPC$DATAPUMP_QUETAB

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组件，后来经过同事介绍在metalink 上有关于此问题解决办法

解决方法

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

exec dbms_aqadm.drop_queue_table(queue_table =>'SYS.KUPC$DATAPUMP_QUETAB',

force=> TRUE);

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

BEGIN

dbms_aqadm.create_queue_table(queue_table => 'SYS.KUPC$DATAPUMP_QUETAB',

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.再次执行一个utlrp.sql

4 再次查下一个无效组件

SQL> select object_name from all_objects where owner='SYS' and status='INVALID';

OBJECT_NAME

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

SBF_PUBLIC

5 重启一下实例，最后expdp可以正常运行了，此解决方法原始出处是在metalink 上一篇文章，文章ID 361025.1  最后谢谢我的同事！

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/31123655/viewspace-2129382/，如需转载，请注明出处，否则将追究法律责任。