imp ORA-00604: 递归错误

imp ora-00604 ora-06502错误 ptm-c1的是生产库，一直都是10.2.0.4 客户端是windows版10.2.0.1 pri-1是测试库，之前的版本是10.2.0.1，导出导入都是正常的，上个星期把测试库升级到10.2.0.4就出 现了今天无法导入的问题 [oracle@pri-1 ~]$ imp test/test fromuser=pdid touser=test file=data_m3415.dmp Import: Release 10.2.0.4.0 - Production on Mon May 31 11:32:34 2010 Copyright (c) 1982, 2007, Oracle. All rights reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production With the Partitioning, Real Application Clusters, OLAP, Data Mining and Real Application Testing options Export file created by EXPORT:V10.02.01 via conventional path Warning: the objects were exported by PDID, not by you import done in ZHS16GBK character set and AL16UTF16 NCHAR character set . importing PDID's objects into TEST IMP-00017: following statement failed with ORACLE error 604: ... ... IMP-00003: ORACLE error 604 encountered ORA-00604: error occurred at recursive SQL level 1 ORA-06502: PL/SQL: numeric or value error: character string buffer too small ORA-06512: at line 6 Import terminated successfully with warnings. 加大buffer，而且在创建包时也出现了同样的问题 [oracle@pri-1 ~]$ imp test/test fromuser=pdid touser=test buffer=1000000000 file=data_m3415.dmp Import: Release 10.2.0.4.0 - Production on 星期一 5月 31 12:08:46 2010 Copyright (c) 1982, 2007, Oracle. All rights reserved. 连接到: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production With the Partitioning, Real Application Clusters, OLAP, Data Mining and Real Application Testing options 经由常规路径由 EXPORT:V10.02.01 创建的导出文件 警告: 这些对象由 PDID 导出, 而不是当前用户 已经完成 ZHS16GBK 字符集和 AL16UTF16 NCHAR 字符集中的导入 . 正在将 PDID 的对象导入到 TEST IMP-00017: 由于 ORACLE 错误 604, 以下语句失败: ... ... IMP-00003: 遇到 ORACLE 错误 604 ORA-00604: 递归 SQL 级别 1 出现错误 ORA-06502: PL/SQL: 数字或值错误 : 字符串缓冲区太小 ORA-06512: 在 line 6 成功终止导入, 但出现警告。 pdid@SUN> create or replace package body NEWCIDCREATE isTE is --创建 T 表 procedure CreateTTable(cid in varchar2) is sqlstr varchar2(4000); beginbegin sqlstr := 'create table DATA_T' || cid || ' ( PID VARCHAR2(4) not null, OUTSIDEID VARCHAR2(25) not null, PTID VARCHAR2(5), PSID CHAR(4), MAKEID VARCHAR2(30), STOREID CHAR(4), BATCHNO VARCHAR2(20), SERIALNO VARCHAR2(30), INOP VARCHAR2(16), INTIME DATE, INNOTE VARCHAR2(100), INNO VARCHAR2(20), STORELOCATION VARCHAR2(40), OUTOP VARCHAR2(16), OUTTIME DATE, OUTNOTE VARCHAR2(100), OUTNO VARCHAR2(20), DEALERID VARCHAR2(30), PROVINCEID CHAR(2), CITYID CHAR(3), PSTATUS NUMBER(1) default 0 not null, SPLITFLAG NUMBER(1) default 0 not null, OPFLAG NUMBER(1) default 0 not null, OPRTIME DATE not null, REOUTFLAG NUMBER(1) default 0 not null, LASTSTOREID CHAR(4), LASTDEALERID VARCHAR2(30), CHANGNO VARCHAR2(20), ADJUSTNO VARCHAR2(20), RETURNNO VARCHAR2(20) ) tablespace N3PDID; execute immediate sqlstr; sqlstr := '';= ''; 45 end CreateTTable;able; 47 48 end newcidcreate; 49 / create or replace * ERROR at line 1: ORA-00604: error occurred at recursive SQL level 1 ORA-06502: PL/SQL: numeric or value error: character string buffer too small ORA-06512: at line 6 下面准备开启10046 pdid@SUN> alter system set event='10046 trace name context forever,level 12' scope=spfile; System altered. 创建之前的包，然后查看trace文件，有这么一段 EXEC #6:c=0,e=249,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=4,tim=661284328633 FETCH #6:c=0,e=8,p=0,cr=2,cu=0,mis=0,r=0,dep=2,og=4,tim=661284328654 STAT #6 id=1 cnt=0 pid=0 pos=1 obj=75 op='TABLE ACCESS BY INDEX ROWID IDL_UB2$ (cr=2 pr=0 pw=0 time=10 us)' STAT #6 id=2 cnt=0 pid=1 pos=1 obj=116 op='INDEX RANGE SCAN I_IDL_UB21 (cr=2 pr=0 pw=0 time=8 us)' EXEC #8:c=0,e=2196,p=0,cr=16,cu=0,mis=0,r=0,dep=1,og=1,tim=661284328882 ERROR #8:err=6502 tim=67715515 WAIT #7: nam='log file sync' ela= 3405 buffer#=133 p2=0 p3=0 obj#=348 tim=661284332611 WAIT #7: nam='SQL*Net break/reset to client' ela= 7 driver id=1650815232 break?=1 p3=0 obj#=348 tim=661284332653 WAIT #7: nam='SQL*Net break/reset to client' ela= 88 driver id=1650815232 break?=0 p3=0 obj#=348 tim=661284332751 WAIT #7: nam='SQL*Net message to client' ela= 1 driver id=1650815232 #bytes=1 p3=0 obj#=348 tim=661284332763 WAIT #7: nam='SQL*Net message from client' ela= 200 driver id=1650815232 #bytes=1 p3=0 obj#=348 tim=661284333001 metalink的解决办法是_system_trig_enabled=false _system_trig_enabled的解释 Parameter: _SYSTEM_TRIG_ENABLED ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ This parameter is introduced in Oracle 8.1. It is a HIDDEN parameter (ie: It begins with an UNDERSCORE). It is documented here as this hidden parameter may need to be set when performing upgrade / downgrade type operations on a database. This parameter can only be set in the init.ora file in Oracle8i. It can be changed dynamically using ALTER SYSTEM in Oracle9i. Description ~~~~~~~~~~~ System triggers are a new feature in Oracle 8.1. When _SYSTEM_TRIG_ENABLED is set to TRUE (the default) then system triggers are enabled. In some cases it may be necessary to disable system triggers from firing by setting this parameter to FALSE. This should only be done for short periods for specific operations. It is not advisable to allow normal users onto the database whilst such triggers are disabled as system triggers may be used for audit or security checking by certain applications. 我在导入之前执行了升级的操作。 C:\Documents and Settings\Administrator>imp pdid/xj2pdid@sun fromuser=pdid touse r=pdid file=f:\data_m3415.dmp Import: Release 10.2.0.1.0 - Production on 星期二 6月 1 11:31:27 2010 Copyright (c) 1982, 2005, Oracle. All rights reserved. 连接到: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options 经由常规路径由 EXPORT:V10.02.01 创建的导出文件 已经完成 ZHS16GBK 字符集和 AL16UTF16 NCHAR 字符集中的导入 . 正在将 PDID 的对象导入到 PDID . . 正在导入表 "DATA_M3415"导入了 143453 行 IMP-00017: 由于 ORACLE 错误 1917, 以下语句失败: "GRANT SELECT ON "DATA_M3415" TO "PDIDR"" IMP-00003: 遇到 ORACLE 错误 1917 ORA-01917: 用户或角色 'PDIDR' 不存在 IMP-00017: 由于 ORACLE 错误 1917, 以下语句失败: "GRANT DELETE ON "DATA_M3415" TO "PDIDW"" IMP-00003: 遇到 ORACLE 错误 1917 ORA-01917: 用户或角色 'PDIDW' 不存在 IMP-00017: 由于 ORACLE 错误 1917, 以下语句失败: "GRANT INSERT ON "DATA_M3415" TO "PDIDW"" IMP-00003: 遇到 ORACLE 错误 1917 ORA-01917: 用户或角色 'PDIDW' 不存在 IMP-00017: 由于 ORACLE 错误 1917, 以下语句失败: "GRANT SELECT ON "DATA_M3415" TO "PDIDW"" IMP-00003: 遇到 ORACLE 错误 1917 ORA-01917: 用户或角色 'PDIDW' 不存在 IMP-00017: 由于 ORACLE 错误 1917, 以下语句失败: "GRANT UPDATE ON "DATA_M3415" TO "PDIDW"" IMP-00003: 遇到 ORACLE 错误 1917 ORA-01917: 用户或角色 'PDIDW' 不存在 成功终止导入, 但出现警告。 ORA-00604: 递归 SQL 级别 1 出现错误 ORA-06502: PL/SQL: 数字或值错误 : 字符串缓冲区太小 ORA-06512: 在 line 27 DDL触发器会读取DDL语句的前2000个字符,然后再赋给一个varchar2(2000)的变量.因为前2000个字符内有中文字符,一个中文字符占用了3个字节,所以这个变量保存不了,因此报了ORA-06502错误了. 我是通过打开SQL_TRACE功能才发现了原来还有一个DDL触发器.
链接:http://hi.baidu.com/javenzhen/blog/item/fe1fbaedb5e8a531269791d5.html

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/90986/viewspace-735199/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/90986/viewspace-735199/