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

0    268    1

Tags:

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

前言部分

导读和注意事项

各位技术爱好者,看完本文后,你可以掌握如下的技能,也可以学到一些其它你所不知道的知识,~O(∩_∩)O~:

① EXPDP和IMPDP基于scn的导出

② ora-06502的解决方法

本文简介

执行导出操作的时候报错信息如下:

ORA-31626: job does not exist

ORA-31638: cannot attach to job SYS_EXPORT_SCHEMA_01 for user XXXXX

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

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

ORA-39077: unable to subscribe agent KUPC$A_2_20210102154238 to queue "KUPC$C_2_20210102154237"

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

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

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

网上查询后有网友已经遇到了,连接地址:http://blog.itpub.net/26736162/viewspace-1982160/ ,我直接根据这个来解决吧。

故障分析及解决过程

故障环境介绍

项目source db
db 类型rac
db version10.2.0.5
db 存储FS type
ORACLE_SIDxxx
db_namexxx
主机IP地址:XXX.XXX.XXX.XXX
OS版本及kernel版本AIX 6
OS hostnameZTGXPADDB1

故障发生现象及报错信息

oracle@ZTGXPADDB1:/gg/ogg/dirrpt$ expdp XXXXX/XXXXX@22.188.131.27:1521/oraXPAD DIRECTORY=DATA_PUMP_DIR DUMPFILE=XXXXX_20160125.dmp LOGFILE=XXXXX_20160125.log FLASHBACK_SCN=12242466617347

Export: Release 10.2.0.5.0 - 64bit Production on Saturday, 02 January, 2021 15:42:47

Copyright (c) 2003, 2007, Oracle. All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production

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

ORA-31626: job does not exist

ORA-31638: cannot attach to job SYS_EXPORT_SCHEMA_01 for user XXXXX

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

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

ORA-39077: unable to subscribe agent KUPC$A_2_20210102154238 to queue "KUPC$C_2_20210102154237"

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

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

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

故障分析过程

oracle@ZTGXPADDB1:/gg/ogg/dirrpt$ expdp XXXXX/XXXXX@22.188.131.27:1521/oraXPAD DIRECTORY=DATA_PUMP_DIR DUMPFILE=XXXXX_20160125.dmp LOGFILE=XXXXX_20160125.log FLASHBACK_SCN=12242466617347

Export: Release 10.2.0.5.0 - 64bit Production on Saturday, 02 January, 2021 15:42:47

Copyright (c) 2003, 2007, Oracle. All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production

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

ORA-31626: job does not exist

ORA-31638: cannot attach to job SYS_EXPORT_SCHEMA_01 for user XXXXX

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

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

ORA-39077: unable to subscribe agent KUPC$A_2_20210102154238 to queue "KUPC$C_2_20210102154237"

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

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

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

根据资料解决过程如下:

SELECT *

FROM dba_objects d

WHERE d.OBJECT_NAME like '%DATAPUMP%'

AND D.OBJECT_TYPE = 'SEQUENCE';

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

SELECT *

FROM DBA_SEQUENCES D

WHERE D.sequence_name IN

('AQ$_KUPC$DATAPUMP_QUETAB_N', 'AQ$_KUPC$DATAPUMP_QUETAB_1_N');

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

oracle@ZTGXPADDB1:/gg/ogg/dirrpt$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.5.0 - Production on Sat Jan 2 16:08:47 2021

Copyright (c) 1982, 2010, Oracle. All Rights Reserved.

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production

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

SQL> SELECT AQ$_KUPC$DATAPUMP_QUETAB_N.CURRVAL FROM DUAL;

SELECT AQ$_KUPC$DATAPUMP_QUETAB_N.CURRVAL FROM DUAL

*

ERROR at line 1:

ORA-08002: sequence AQ$_KUPC$DATAPUMP_QUETAB_N.CURRVAL is not yet defined in

this session

SQL> SELECT AQ$_KUPC$DATAPUMP_QUETAB_N.NEXTVAL FROM DUAL;

NEXTVAL

----------

1194988

SQL> DROP SEQUENCE AQ$_KUPC$DATAPUMP_QUETAB_1_N;

DROP SEQUENCE AQ$_KUPC$DATAPUMP_QUETAB_1_N

*

ERROR at line 1:

ORA-02289: sequence does not exist

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

标签:

Avatar photo

小麦苗

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

您可能还喜欢...

发表回复

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

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

  • 回到顶部