記一次 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  最後謝謝我的同事！