ITPub博客

首页 > Linux操作系统 > Linux操作系统 > expdp时遭遇ORA-600

expdp时遭遇ORA-600

原创 Linux操作系统 作者:lsq_008 时间:2009-06-09 10:33:07 0 删除 编辑

数据库版本是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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
十余年大型金融及电信系统数据库管理经验,曾服务于中国建设银行、中国移动。对oracle,mysql数据库有深入了解。 擅长python开发,独立开发了开源数据库自动化监控运维平台Power Monitor。

注册时间:2008-02-29

  • 博文量
    324
  • 访问量
    1227986