前几天在一个环境上做了一个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/,如需转载,请注明出处,否则将追究法律责任。