ORA-06502: PL/SQL: Number or value error: String Buffer too small Error Analysis

ORA-06502: PL/SQL: Number or value error: String Buffer too small error analysis 1. problem cause this error is always encountered when you recently perform some oracle operations: ORA-06502: PL/SQL: Number or value error: string buffer is too small, error: [SQL] ORA-00604: recursive SQL Level 1 error ORA-06502: PL/SQL: Number or value error: String Buffer too small ORA-06512: in line 7 2. official explanation: ORA-06502: PL/SQL: numeric or value errorstringCause: 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: The data (including data and strings) to be stored in the database does not conform to the definition of this field (such as length and constraints). For example, that is to say, you want to save a null value to a non-empty field. For example, you want to store three or more numbers to the NUMBER (2) field. The official solution is to change the data type or length, and so on. 3. problem description: However, in practice, when upgrading or executing EXPDP/IMPDP, this error is not our own business data. In this case, how can I know where to change the data? For example: 1. the last time we upgraded oracle, we ran the final upgrade script catcpu from 11.2.0.1.0 to 11.2.0.1.6. this error occurs during SQL: [SQL] SQL>/****************************** PACKAGE BODY *** * 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 2 a000000 3 1 4 abcd 5 abcd. www.2cto.com. 19 B 20 dbb0 3035 21 wBYhf/HY/fingerprint + uv30CVrF 22 fingerprint + rVQUjJz6UkBKiymIRTD47p8N 23 + fingerprint // nCdeBSWO7VQUXXQwvWT KRaQX + VcQUdld5As 24 cx9z + 2uhsovz8svraprk7vah2cy8bqq1_+ 1P4mkrsCSbvlsRSSqN + XgZbZqgUDSzZFfRbc0 .. 191 192/create or replace package body dbms_expfil wrapped * 1st row error: ORA-00604: recursive SQL Level 1 error ORA-06502: PL/SQL: Number or value error: String Buffer too small ORA-06512: in line 7, it is clear that something in oracle is wrong and we cannot change anything. Example 2: The error [SQL] C: \ Documents and Settings \ andyleng> expdp system/oracle @ test DIRECTORY = db_backup DUMPFILE = catb_normal.DMP SCHEMAS = catb logfile = partition parallel = 1 CONTENT = ALL FLASHBACK_TIME = SYSDATE www.2cto.com Export: release 11.2.0.1.0-Production on Friday July 27 17:43:11 2012 Copyright (c) 1982,200 9, Oracle and/or its affiliates. all rights reserved. connect to: Orac Le 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: Unable to create job SYS_EXPORT_SCHEMA_02 (User SYSTEM) ORA-06512: in "SYS. DBMS_SYS_ERROR ", line 95 ORA-06512: In" SYS. KUPV $ FT_INT ", line 798 ORA-39080: unable to create a queue for Data Pump jobs" KUPC $ c_1_20727174311 "and" KUPC $ S_1_20120727174311 "ORA-06512: In" SYS. DBMS_SY S_ERROR ", line 95 ORA-06512: In" SYS. KUPC $ QUE_INT ", line 1530 ORA-06502: PL/SQL: Number or value error: string buffer is too small [SQL] C: \ Documents and Settings \ andyleng> impdp system/oracle @ test DIRECTORY = db_backup DUMPFILE = scott. dmp logfile = impdp_scott_20120727.log SCHEMAS = scott content = all parallel = 1 TABLE_EXISTS_ACTION = REPLACE Import: Release 11.2.0.1.0-Production on Friday July 27 18:09:11 2012 Copyright (c) 1982,200 9, Oracle And/or its affiliates. all rights reserved. connect 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: Unable to create job SYS_IMPORT_SCHEMA_09 (User SYSTEM) ORA-06512: In "SYS. DBMS_SYS_ERROR ", line 95 ORA-06512: In" SYS. KUPV $ FT_INT ", line 798 ORA-39080: Unable to create queue for Data Pump job" KUPC $ c_000020 120727180912 "and" KUPC $ s_000020120727180912 "ORA-06512: In" SYS. DBMS_SYS_ERROR ", line 95 ORA-06512: In" SYS. KUPC $ QUE_INT ", line 1530 ORA-06502: PL/SQL: Number or value error: string buffer is too small in this case, it looks like oracle was creating this task, failed to create because of ORA-06502 error. 4. the problem solved the above two situations, and user data has nothing to do, but a ORA-06502 error, indicating that ORACLE itself has a system table when inserting data does not meet the length requirements, this error is reported. Collect information: oracle uses an implicit parameter "_ system_trig_enabled" to control system triggers and store system trigger events, however, this table (which table is unknown at present) is faulty and data cannot be inserted. Solution: follow this idea, as long as we turn off this parameter so that system logs are not stored in the problematic table (of course, there is an implicit risk, which is unknown currently ): solution: [SQL] SQL> show parameter trig name type value =----------------------- --------- _ system_trig_enabled boolean false SQL> alter system set "_ system_trig_enabled" = false; the system has changed. After www.2cto.com is changed, perform the above operations to solve the problem! 5. there are two solutions to the problem. for user data, change the user table or data 2. if it is an oracle system table, use the following statement to turn off the system trigger: alter system set "_ system_trig_enabled" = false; Author: yfleng2002