expdp时遭遇ORA-600

数据库版本是10.2.0.4,在执行expdp导出时，按ctrl+c结束，再次执行expdp，报错：

UDI-00008: operation generated ORACLE error 31623
ORA-31623: a job is not attached to this session via the specified handle
ORA-06512: at "SYS.DBMS_DATAPUMP", line 2772
ORA-06512: at "SYS.DBMS_DATAPUMP", line 3886
ORA-06512: at line 1

查看metalink，发现如下解释：

Applies to:
Oracle Server - Enterprise Edition - Version: 10.2.0.1 to 10.2.0.4
This problem can occur on any platform.

Symptoms
An export or import operation using DataPump fails with the following errors:

UDI-00008: operation generated ORACLE error 31623
ORA-31623: a job is not attached to this session via the specified handle
ORA-06512: at "SYS.DBMS_DATAPUMP", line 2772
ORA-06512: at "SYS.DBMS_DATAPUMP", line 3886
ORA-06512: at line 1

and the alert log file of the database shows the error:

ORA-00600: internal error code, arguments: [kwqbgqc: bad state], [1], [1], [], [], [], [], []

 

Cause
A Datapump queue is invalid.

This can happen after reruning the catpatch.sql or utlrp.sql scripts

Solution
1. Shutdown the database cleanly:
   
     SQL> shutdown immediate
     SQL> startup restrict

2. Drop the queue table.

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

NOTE:
- If the ORA-4020 error is reported, wait some minutes and try again
- If an ORA-24* error is reported, it could be necessary to perform. a manual cleanup
See Note 203225.1 "How to Manually Cleanup Advanced Queuing Tables"

3. Recreate the queue
    The SQL is in the Note 361025.1 or you can pull it from the catdpb.sql script. in $ORACLE_HOME/rdbms/admin directory.

-- Create our queue table.
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;
/

4. Run utlrp.sql to recompile all the database objects..

5. Retry the DataPump operation

References
Note 203225.1 - How to Manually Cleanup Advanced Queuing Tables
Note 361025.1 - Invalid Objects After Installing a 10.2 Patchset

Keywords
QUEUE_TABLE ; DATAPUMP ; DBMS_DATAPUMP ; 

按照这个文档提供的解决方法进行操作，问题得到解决。

 

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

转载于:http://blog.itpub.net/10972173/viewspace-605614/