Using ROWNUM to resolve ORA-00600: internal error codes
Developers executing statements are reporting ORA-00600: Internal error code
statement is as follows
create tablesytab01 as
select a.* from tab1 c,
( select b.* from tab2 s,
( select a.*, row_number()over( partition by cl order by cl_name ) rn
from tab3 a wherea.area_no= 'aa'
) a
where trim(s.home_cl)= trim(a.cl) and rn= '1' and
month= '201703'
and s.area = 'aa'
)b
where c.user_no = b.user_no
The database version is as follows:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bitProduction
With the Partitioning, Real Application Clusters, Automatic StorageManagement, OLAP,
Data Mining and Real Application Testing options
The database logs report the following error:
*** 2017-03-23 17:59:14.729
*** SESSION ID:(2191.15425) 2018-04-23 17:59:14.729
*** CLIENT ID:() 2018-04-23 17:59:14.729
*** SERVICE NAME:(hbdw) 2018-04-23 17:59:14.729
*** MODULE NAME:(PL/SQL Developer) 2018-04-23 17:59:14.729
*** ACTION NAME:(SQL Window-New) 2018-04-23 17:59:14.729
Incident 258636 created, dump file:/u01/app/oracle/diag/rdbms/hbdw/hbdw1/incident/incdir_258636/orcl1_ora_11899_i258636.trc
ORA-00600: Internal error code, arguments: [rwoirw: check ret val], [],[],[],[],[],[],[],[], [],[]
This incdir_258636/orcl1_ora_11899_i258636.trc log reported the following error:
----- Call Stack Trace -----
calling call entry argument values in hex
location type point (? means dubiousvalue)
-------------------- -------- ------------------------------------------------
skdstdst()+41 call kgdsdst() 000000000 ? 000000000 ?
7FFFFFFEBC30 ? 7FFFFFFEBD08 ?
7FFFFFFF07B0 ? 000000002 ?
ksedst1()+103 call skdstdst() 000000000 ? 000000000 ?
7FFFFFFEBC30 ? 7FFFFFFEBD08 ?
7FFFFFFF07B0 ? 000000002 ?
ksedst()+39 call ksedst1() 000000000 ? 000000001 ?
7FFFFFFEBC30 ? 7FFFFFFEBD08 ?
7FFFFFFF07B0 ? 000000002 ?
dbkedDefDump()+2746 call ksedst() 000000000 ? 000000001 ?
7FFFFFFEBC30 ? 7FFFFFFEBD08 ?
7FFFFFFF07B0 ? 000000002 ?
ksedmp()+41 call dbkedDefDump() 000000003 ? 000000002 ?
7FFFFFFEBC30 ? 7FFFFFFEBD08 ?
7FFFFFFF07B0 ? 000000002 ?
Because it is caused by the sentence, the analysis idea is as follows:
1. See if there is any problem with just checking.
select a.* from tab1 c,
(select b.* from tab2 s,
( select a.*, row_number()over(partition by cl order by cl_name ) rn
from tab3 a where a.area_no='aa'
) a
where trim(s.home_cl)=trim(a.cl) and rn='1' and
month='201703'
and s.area ='aa'
)b
where c.user_no = b.user_no
Results output, query normal
2. From the results above, it should not be a query caused by the problem.
Suddenly, he thought, could it be that the number of temporary tables was too large? Then try adding rownum later
< 1000; 手动执行create sql语句结果成功了。 3. 用rownum 解决此问题 想了一下，可能11.2.0.4里面对create tablexxx as select …. From …的限制比较严格（没有经过论证，也可能是个bug），意味着在不知道后面的select … from …的总体数量的情况下或者数量已经超过了oracle的默认值比如1000这样，会提示ORA-00600的错误。按照这个思路我查询出来select … from ..的总数量，在后面加上and rownum-1;（因为有可能select 出来空记录）呢？执行了一下居然成功，语句修改如下： create table sytab01 as select a.* fromtab1 c, (select b.* from tab2 s, ( select a.*,row_number()over(partition by cl order by cl_name ) rn from tab3 a wherea.area_no= 'aa' ) a where trim(s.home_cl)=trim(a.cl)and rn= '1' and month= '201703' and s.area = 'aa' )b where c.user_no = b.user_no and rownum>-1 ;
4. The official documentation describes the ORA-600 as follows:
Bug 14275161 - ORA-600[rwoirw: check ret val] on CTAS with predicate move around (File ID 14275161.8)
Modify implicit parameters:
600 errors encountered in the sentence is generally personal advice to modify the sentence to avoid problems,
Unless there are more statements affecting the database. Surgery on databases is generally not recommended.
