数据泵导出导入ORA-06502、ORA-39077 错误分析与解决方案

0    353    1

Tags:

👉 本文共约9475个字,系统预计阅读时间或需36分钟。

expdp/impdp操作报错信息如下:

ORA-31626: job does not exist

ORA-31638: cannot attach to job SYS_EXPORT_TABLE_01 for user SYS

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

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

ORA-39077: unable to subscribe agent KUPC$A_1_191136568928000 to queue "KUPC$C_1_20140807191136"

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

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

ORA-06502: PL/SQL: numeric or value error: character string buffer too small

根据报错信息初步分析,可能是datapump组件中sequence数值大于6位数或sequence失效导致。参考mos 文档1550344.1分析,此错误由oracle Bug 16473783 导致,在Oracle 12.2版本中被修复,如果执行脚本重新安装datapump组件可能会引起其它问题。

错误模拟

1.测试导出操作sequence值是否会增加

1.查看datapump组件包含的sequence

SQL> select OBJECT_NAME,OBJECT_TYPE,STATUS from all_objects where owner='SYS' and object_name like '%DATAPUMP%' and object_type='SEQUENCE';

OBJECT_NAME OBJECT_TYPE STATUS


AQ$_KUPC$DATAPUMP_QUETAB_N SEQUENCE VALID

AQ$_KUPC$DATAPUMP_QUETAB_1_N SEQUENCE VALID

2.查看两个sequence的值

SQL> select AQ$_KUPC$DATAPUMP_QUETAB_N.nextval from dual;

NEXTVAL

----------

1

SQL> select AQ$_KUPC$DATAPUMP_QUETAB_1_N.nextval from dual;

NEXTVAL

----------

​ 361

3.执行导出

[oracle@ENMOEDU ~]$ expdp \'/ as sysdba\' directory=MY_DIR dumpfile=test.dmp tables=test.T_TASKDONE

4.查看sequence值

SQL> select AQ$_KUPC$DATAPUMP_QUETAB_N.nextval from dual;

NEXTVAL

----------

2

SQL> select AQ$_KUPC$DATAPUMP_QUETAB_1_N.nextval from dual;

NEXTVAL

----------

​ 368

说明:导出操作datapump组件中的sequence值会增加

2.测试sequence值超过6位数是否出现此错误

1.通过Increment By来实现修改初始值。序列名称是AQ$_KUPC$DATAPUMP_QUETAB_1_N,初始值是368,而现在要设置初始值为999999,Increment By值为:999619(999999-380)

SQL> Alter Sequence AQ$_KUPC$DATAPUMP_QUETAB_1_N Increment By 999619;

Sequence altered.

SQL> Select AQ$_KUPC$DATAPUMP_QUETAB_1_N.NextVal From Dual;

NEXTVAL

----------

999987

SQL> Alter Sequence AQ$_KUPC$DATAPUMP_QUETAB_1_N Increment By 7;

Sequence altered.

SQL> Select AQ$_KUPC$DATAPUMP_QUETAB_1_N.NextVal From Dual;

NEXTVAL

----------

999994

2.执行expdp导出操作查看是否报错

[oracle@ENMOEDU ~]$ expdp \'/ as sysdba\' directory=MY_DIR dumpfile=test.dmp logfile=test.log tables=test.T_TASKDONE

Export: Release 11.2.0.3.0 - Production on Thu Aug 7 19:11:36 2014

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

ORA-31626: job does not exist

ORA-31638: cannot attach to job SYS_EXPORT_TABLE_01 for user SYS

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

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

ORA-39077: unable to subscribe agent KUPC$A_1_191136568928000 to queue "KUPC$C_1_20140807191136"

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

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

ORA-06502: PL/SQL: numeric or value error: character string buffer too small

说明:成功模拟错误。

手动修复

1.重建sequence

1.重建sequence使用cycle参数限制最大值不超过6位数

SQL> drop sequence AQ$_KUPC$DATAPUMP_QUETAB_1_N ;

Sequence dropped.

SQL> create sequence AQ$_KUPC$DATAPUMP_QUETAB_1_N minvalue 1 maxvalue 999999 start with 1 increment by 1 cache 20 cycle;

Sequence created.

2.验证sequence

1.验证sequence值超过6位时是否报错

SQL> drop sequence AQ$_KUPC$DATAPUMP_QUETAB_1_N;

Sequence dropped.

SQL> create sequence AQ$_KUPC$DATAPUMP_QUETAB_1_N minvalue 1 maxvalue 999999 start with 999997 increment by 1 cache 20 cycle;

Sequence created.

SQL> select AQ$_KUPC$DATAPUMP_QUETAB_1_N.nextval from dual;

NEXTVAL

----------

999998

2.执行expdp导入操作

[oracle@ENMOEDU admin]$ expdp \'/ as sysdba\' directory=my_dir logfile=test01.log dumpfile=test02.dmp tables=test.T_BASEITEM

Export: Release 11.2.0.3.0 - Production on Fri Aug 8 00:56:10 2014

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Starting "SYS"."SYS_EXPORT_TABLE_01": "/**** AS SYSDBA" directory=my_dir logfile=test01.log dumpfile=test02.dmp tables=test.T_BASEITEM

Estimate in progress using BLOCKS method...

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

Total estimation using BLOCKS method: 6 MB

Processing object type TABLE_EXPORT/TABLE/TABLE

本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务,私聊QQ646634621或微信db_bao,谢谢!
数据泵导出导入ORA-06502、ORA-39077 错误分析与解决方案后续精彩内容已被小麦苗无情隐藏,请输入验证码解锁本站所有文章!
验证码:
请先关注本站微信公众号,然后回复“验证码”,获取验证码。在微信里搜索“DB宝”或者“www_xmmup_com”或者微信扫描右侧二维码都可以关注本站微信公众号。

标签:

Avatar photo

小麦苗

学习或考证,均可联系麦老师,请加微信db_bao或QQ646634621

您可能还喜欢...

发表回复

嘿,我是小麦,需要帮助随时找我哦。
  • 18509239930
  • 个人微信

  • DB宝
  • 个人邮箱
  • 点击加入QQ群
  • 个人微店

  • 回到顶部