合 数据泵导出导入ORA-06502、ORA-39077 错误分析与解决方案
Tags: Oracle
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