合 ORA-06502: PL/SQL: numeric or value error: character string buffer too small
前言部分
导读和注意事项
各位技术爱好者，看完本文后，你可以掌握如下的技能，也可以学到一些其它你所不知道的知识，~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 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 |
故障发生现象及报错信息
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';
SELECT *
FROM DBA_SEQUENCES D
WHERE D.sequence_name IN
('AQ$_KUPC$DATAPUMP_QUETAB_N', 'AQ$_KUPC$DATAPUMP_QUETAB_1_N');
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
SQL> DROP SEQUENCE AQ$_KUPC$DATAPUMP_QUETAB_N;
Sequence dropped.
SQL> CREATE SEQUENCE AQ$_KUPC$DATAPUMP_QUETAB_N MINVALUE 1 MAXVALUE 999999 START WITH 1 INCREMENT BY 1 CACHE 20 CYCLE;
Sequence created.
SQL> SELECT AQ$_KUPC$DATAPUMP_QUETAB_N.NEXTVAL FROM DUAL;
NEXTVAL
----------
1
SQL> EXIT
Disconnected from 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
oracle@ZTGXPADDB1:/gg/ogg/dirrpt$
oracle@ZTGXPADDB1:/gg/ogg/dirrpt$ expdp XXXXX/XXXXX 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 16:11:46
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-39002: invalid operation
ORA-39070: Unable to open the log file.
ORA-39087: directory name DATA_PUMP_DIR is invalid
oracle@ZTGXPADDB1:/gg/ogg/dirrpt$ cd /oracle/app/oracle/product/10.2.0/db/rdbms/log/
oracle@ZTGXPADDB1:/oracle/app/oracle/product/10.2.0/db/rdbms$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.5.0 - Production on Sat Jan 2 16:17:56 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> grant read,write on directory DATA_PUMP_DIR to XXXXX;
Grant succeeded.
SQL> exit
Disconnected from 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
oracle@ZTGXPADDB1:/oracle/app/oracle/product/10.2.0/db/rdbms$ expdp XXXXX/XXXXX 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 16:18:06
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
FLASHBACK automatically enabled to preserve database integrity.
Starting "XXXXX"."SYS_EXPORT_SCHEMA_01": XXXXX/******** DIRECTORY=DATA_PUMP_DIR DUMPFILE=XXXXX_20160125.dmp LOGFILE=XXXXX_20160125.log FLASHBACK_SCN=12242466617347
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 11.15 GB
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/SYNONYM/SYNONYM
Processing object type SCHEMA_EXPORT/DB_LINK
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 11.15 GB
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/SYNONYM/SYNONYM
Processing object type SCHEMA_EXPORT/DB_LINK
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_SPEC
Processing object type SCHEMA_EXPORT/FUNCTION/FUNCTION
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/PACKAGE/COMPILE_PACKAGE/PACKAGE_SPEC/ALTER_PACKAGE_SPEC
Processing object type SCHEMA_EXPORT/FUNCTION/ALTER_FUNCTION
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
Processing object type SCHEMA_EXPORT/VIEW/VIEW
Processing object type SCHEMA_EXPORT/VIEW/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_BODY
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
