oracle 06502 解决,【故障解决】ORA-06502错误解决

【故障解决】ORA-06502: PL/SQL: numeric or value error: character string buffer too small

一.1BLOG文档结构图

一.2前言部分

一.2.1导读和注意事项

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

① EXPDP和IMPDP基于scn的导出

② ora-06502的解决方法

Tips：

① 若文章代码格式有错乱，推荐使用QQ或360浏览器，也可以下载pdf格式的文档来查看，pdf文档下载地址：(提取码：ed9b)

②本篇BLOG中代码部分需要特别关注的地方我都用黄色背景和红色字体来表示，比如下边的例子中，thread 1的最大归档日志号为33，thread 2的最大归档日志号为43是需要特别关注的地方，命令一般使用粉红颜色标注，注释一般采用蓝色字体表示。

List of Archived Logs in backup set 11

Thrd Seq     Low SCN    Low Time            Next SCN   Next Time

---- ------- ---------- ------------------- ---------- ---------

1    32      1621589    2015-05-29 11:09:52 1625242    2015-05-29 11:15:48

1    33      1625242    2015-05-29 11:15:48 1625293    2015-05-29 11:15:58

2    42      1613951    2015-05-29 10:41:18 1625245    2015-05-29 11:15:49

2    43      1625245    2015-05-29 11:15:49 1625253    2015-05-29 11:15:53

[ZFXDESKDB1:root]:/>lsvg -o

T_XDESK_APP1_vg

rootvg

[ZFXDESKDB1:root]:/>

[ZFXDESKDB1:root]:/>lsvg rootvg

====》2097152*512/1024/1024/1024=1G

本文如有错误或不完善的地方请大家多多指正，ITPUB留言或QQ皆可，您的批评指正是我写作的最大动力。

一.2.2相关参考文章链接

一.2.3本文简介

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

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

一.3相关知识点扫盲

一.4故障分析及解决过程

一.4.1故障环境介绍

项目 source db

db类型 rac

db version 10.2.0.5

db存储 FS type

ORACLE_SID xxx

db_name xxx

主机IP地址： XXX.XXX.XXX.XXX

OS版本及kernel版本 AIX 6

OS hostname ZTGXPADDB1

一.4.2故障发生现象及报错信息

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

一.4.3故障分析过程

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*

FROMdba_objects d

WHEREd.OBJECT_NAMElike'%DATAPUMP%'

ANDD.OBJECT_TYPE='SEQUENCE';

SELECT*

FROMDBA_SEQUENCES D

WHERED.sequence_nameIN

('AQ$_KUPC$D