昨天看,发现yangtingkun的一篇文章.关于建表出现ora-00604的问题,自己重复测试看看.
1.测试脚本:
--9i下定位不是很明确,实际上是第2列字段长度超长.
--做10046跟踪:
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
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
