ITPub博客

首页 > Linux操作系统 > Linux操作系统 > [20120523] create table 出错.txt

[20120523] create table 出错.txt

原创 Linux操作系统 作者:lfree 时间:2012-05-24 12:01:53 0 删除 编辑
昨天看,发现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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
熟悉oracle相关技术,擅长sql优化,rman备份与恢复,熟悉linux shell编程。

注册时间:2008-01-03

  • 博文量
    2485
  • 访问量
    6290600