【expdp/impdp】 ORA-06502、ORA-39077 错误分析与解决方案

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来实现修改初始值。序列名称是`js
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 Da