[20120523] create table 出错.txt

昨天看,发现yangtingkun的一篇文章.关于建表出现ora-00604的问题,自己重复测试看看.

1.测试脚本:

$ cat aa.sql

create table t_604 as

select * from

(select object_type, to_char(avg(object_id), '999999.999') from dba_objects

group by object_type

order by 2 desc)

where rownum < 10;

2.9i下测试:

SQL> select * from v$version ;

BANNER

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

Oracle9i Enterprise Edition Release 9.2.0.8.0 - Production

PL/SQL Release 9.2.0.8.0 - Production

CORE    9.2.0.8.0       Production

TNS for 32-bit Windows: Version 9.2.0.8.0 - Production

NLSRTL Version 9.2.0.8.0 - Production

SQL> @aa

(SELECT OBJECT_TYPE, To_CHAR(AVG(OBJECT_ID), '999999.999') from DBA_OBJECTS

                                                                *

ERROR at line 3:

ORA-00604: error occurred at recursive SQL level 1

ORA-01401: inserted value too large for column

--9i下定位不是很明确,实际上是第2列字段长度超长.
--做10046跟踪:

SQL> alter session set events '10046 trace name context forever, level 12';

Session altered.

SQL> @aa

(SELECT OBJECT_TYPE, To_CHAR(AVG(OBJECT_ID), '999999.999') from DBA_OBJECTS

                                                                *

ERROR at line 3:

ORA-00604: error occurred at recursive SQL level 1

ORA-01401: inserted value too large for column

SQL> alter session set events '10046 trace name context off';

Session altered.

--我格式化一下:

PARSING IN CURSOR #6 len=408 dep=1 uid=0 ct=2 lid=0 tim=2087575781 hv=3687727603 ad='3db6e3ac'

INSERT into col$(obj#, name, intcol#, segcol#, type#, length, precision#, scale, null$, offset, fixedstorage, segcollength, deflength, default$, col#, property, charsetid,

charsetform, spare1, spare2, spare3)

VALUES (:1,:2,:3,:4,:5,:6, decode(:7, 0, null,:7), decode(:5, 2, decode(:8,-127/*MAXSB1MINAL*/, null,:8), 178,:8, 179,:8, 180,:8, 181,:8, 182,:8, 183,:8, 231,:8,

       null),:9, 0,:10,:11, decode(:12, 0, null,:12),:13,:14,:15,:16,:17,:18,:19,:20)

..

EXEC #6:c=15625,e=1267,p=0,cr=2,cu=7,mis=0,r=1,dep=1,og=4,tim=2087577159

BINDS #6:

 bind 0: dty=2 mxl=22(22) mal=00 scl=00 pre=00 acflg=08 oacfl2=1 size=24 ffset=0

   bfp=4390469c bln=22 avl=04 flg=05

   value=45895

 bind 1: dty=1 mxl=128(36) mal=00 scl=00 pre=00 acflg=18 oacfl2=1 size=128 ffset=0

   bfp=40e741e6 bln=128 avl=36 flg=09

   value="TO_CHAR(AVG(OBJECT_ID),'999999"

   ~~~~~~~123456789012345678901234567890~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

....

 bind 6: dty=2 mxl=22(22) mal=00 scl=00 pre=00 acflg=08 oacfl2=1 size=24 ffset=0

   bfp=439045e8 bln=24 avl=01 flg=05

   value=0

3.11GR2下测试:

SQL> select * from v$version ;

BANNER

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

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

PL/SQL Release 11.2.0.1.0 - Production

CORE    11.2.0.1.0      Production

TNS for Linux: Version 11.2.0.1.0 - Production

NLSRTL Version 11.2.0.1.0 - Production

SQL> alter session set events '10046 trace name context forever, level 12';

Session altered.

SQL> @aa

aa.sql   aa1.sql

SQL> @aa

(SELECT OBJECT_TYPE, To_CHAR(AVG(OBJECT_ID), '999999.999') from DBA_OBJECTS

                                                                *

ERROR at line 3:

ORA-01948: identifier's name length (36) exceeds maximum (30)

SQL> alter session set events '10046 trace name context off';

Session altered.

SQL> @p

   INST_ID NAME                 VALUE

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

         1 Default Trace File   /u01/app/oracle11g/diag/rdbms/test/test/trace/test_ora_11394.trc

insert into obj$(owner#,name,namespace,obj#,type#,ctime,mtime,stime,status,remoteowner,linkname,subname,dataobj#,flags,oid$,spare1,spare2,spare3) values(:1,:2,:3,:4,:5,:6,:7,:8,

:9,:10,:11,:12,:13,:14,:15,:16,:17,:18)

END OF STMT

PARSE #4:c=0,e=74,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,plh=0,tim=1337829914217177

BINDS #4:

 Bind#0

  acdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00

  acflg=08 fl2=0001 frm=00 csi=00 siz=24 ff=0

  kxsbbbfp=2a97387448  bln=22  avl=02  flg=05

  value=84

 Bind#1

  acdty=01 mxl=32(05) mxlc=00 mal=00 scl=00 pre=00

  acflg=18 fl2=0001 frm=01 csi=852 siz=32 ff=0

  kxsbbbfp=d4510a56  bln=32  avl=05  flg=09

  value="T_604"

...

EXEC #2:c=18998,e=19692,p=0,cr=28,cu=19,mis=0,r=0,dep=0,og=1,plh=2468713960,tim=1337829914220047

ERROR #2:err=1948 tim=1337829914220079

--跟踪仅仅能在插入obj$对象.不过在11G下显示很清楚.
--ORA-01948: identifier's name length (36) exceeds maximum (30)
--To_CHAR(AVG(OBJECT_ID), '999999.999'
--123456789012345678901234567890123456


 

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

转载于:http://blog.itpub.net/267265/viewspace-730804/