ORA-06502:
PL / SQL: numeric or value error: character string buffer too small error
analysis
1. The
cause of the problem
Recently when performing some operations oracle,
and will always encounter this error: ORA-06502: PL / SQL: numeric or value
error: character string buffer too small error as follows:
ORA-00604: Recursive SQL Level 1 errors
ORA-06502: PL / SQL: numeric or value error: character string
buffer too small
ORA-06512: at line 7
2. The
official explanation
See the document on the official explanation:
ORA-06502: PL / SQL:
numeric or value errorstring
Cause:
An arithmetic, numeric, string, conversion, or constraint error occurred For
example, this error occurs if an attempt is made to assign the value NULL to a
variable declared NOT NULL, or if an attempt is made to assign an integer
larger. than 99 to a variable declared NUMBER (2).
Action: Change the data, how it is manipulated, or
how it is declared so that values do not violate constraints.
The official explanation is: to stored data in the
database (including data, strings, etc.) does not meet the definition of the
field (such as length, constraints, etc.), for example, that you want to store
a null value to a non-empty fields, As deposited three more digits to NUMBER
(2) field, etc.
The official solution is: change the type of data,
or length, and so on.
3.
Description of the problem
However, in practice, we do upgrade, or do EXPDP /
IMPDP time, is not our own business data, also reported this error, in which
case, how do I know where the data change it,
A few
examples:
Example 1.
The last time was doing oracle upgrade, rose to 11.2.0.1.6 from 11.2.0.1.0 time
in the final run upgrade script catcpu.sql when you encounter this error:
SQL> / ***************************** PACKAGE
BODY **************** ʱ??
SQL> / *** DBMS_EXPFIL: Package to manage a
Expression Engine *** /
SQL> / *** All procedures are defined with
definer rights *** /
SQL> /
*********************************************** ʱ??
SQL> create or replace package body dbms_expfil
wrapped
a000000
abcd
abcd
f5t1PSNPJhKkNUFecK3LJ0wJChaaSsloMeCeeC3pud2dm7 +
rVQUjJz6UkBKiymIRTD47p8N
+
dIIc0OU7IlN6zUWPsEgm9hnXXIeGgSQFJU // nCdeBSWO7VQUXXQwvWTkRaQX + VcQUdld5As
cx9z + 2uhSovZ8svraprK7VAh2cy8bqQBlS
+ 1P4mkrsCSbvlsRSSqN + XgZbZqgUDSzZFfRbc0
.
.
/
create or replace package body dbms_expfil wrapped
Line 1 error:
ORA-00604: Recursive SQL Level 1 errors
ORA-06502: PL / SQL: numeric or value error: character string
buffer too small
ORA-06512: at line 7
In this case, oracle itself is obviously something
went wrong, we can not change anything.
Example 2:
In doing expdp / impdp when importing and exporting, reported this error:
expdp SCOTT directory=RIM dumpfile=expdpSCOTT.dmp logfile=expdpSCOTT.log exclude=TABLE:\"IN \(\'ABC\',\'DEF\',\'GHI\',\'JKL\',\'MNO\'\)\"
Export: Release 11.2.0.1.0 - Production on Friday
July 27 2012 17:43:11
Copyright (c) 1982, 2009, Oracle and / or its
affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise
Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real
Application Testing options
ORA-31626: job does not
exist
ORA-31637: cannot create
job SYS_EXPORT_SCHEMA_02 for user SIMANG_D
ORA-06512: at
"SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at
"SYS.KUPV$FT_INT", line 600
ORA-39080: failed to
create queues "KUPC$C_1_20150822103744" and
"KUPC$S_1_20150822103744" for Data Pump job
ORA-06512: at
"SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at
"SYS.KUPC$QUE_INT", line 1555
ORA-06502: PL/SQL: numeric
or value error: character string buffer too small
impdp SCOTT directory=RIM dumpfile=expdpSCOTT.dmp logfile=impdpSCOTT.log include=TABLE:\"IN \(\'ABC\',\'DEF\',\'GHI\',\'JKL\',\'MNO\'\)\"
Import: Release 11.2.0.1.0 - Production on Friday
July 27 2012 18:09:11
Copyright (c) 1982, 2009, Oracle and / or its
affiliates. All rights reserved.
Connected to: Oracle
Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
ORA-31626: job does not
exist
ORA-31637: cannot create
job SYS_EXPORT_SCHEMA_02 for user SIMANG_D
ORA-06512: at
"SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at
"SYS.KUPV$FT_INT", line 600
ORA-39080: failed to
create queues "KUPC$C_1_20150822103744" and
"KUPC$S_1_20150822103744" for Data Pump job
ORA-06512: at
"SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at
"SYS.KUPC$QUE_INT", line 1555
ORA-06502: PL/SQL: numeric
or value error: character string buffer too small
In this case, it looks like oracle in the creation
of this task, because ORA-06502 errors can not be created.
4. Problem
Solving
In both cases, and user data nothing but an
ORA-06502 error, indicating ORACLE itself has a system table when you insert
data does not meet the length requirement, but reported this mistake.
Gathering information: According to investigation
materials, oracle has an implicit parameter "_system_trig_enabled" is
used to control the system triggers for some of the trigger events storage
system, but this table (currently do not know which table) is itself a problem,
resulting in data can not be inserted.
Solution:
Along the idea, as long as we put this parameter off, let the system log is not
saved in question goes on the table, like a (of course there are the risks
implied, is currently unknown):
Solution:
col name for a30;
col value for a10;
col deflt for a10;
col type for a20;
col description for
a50;
select a.ksppinm
name,b.ksppstvl value,
b.ksppstdf
deflt,decode(a.ksppity, 1,'boolean', 2,'string', 3,'number', 4,'file',
a.ksppity) type,a.ksppdesc description
from sys.x$ksppi a,
sys.x$ksppcv b
where a.indx = b.indx
and
a.ksppinm like '\_%'
escape '\' and
a.ksppinm like '%_system_trig_enabled%'
order by name;
SQL> alter system
set "_system_trig_enabled"=FALSE;
After complete the activity Enabled the trigger.
After the change, do the above operation, the
problem is solved!
5. Summary
Depending on the problem there are two kinds of
solutions
A. If
the user data, change the user table, or data
B. if
the oracle system tables, use the following statement system triggers off
system:
alter system set
"_system_trig_enabled" = false;
No comments:
Post a Comment