执行create table as 报ora-600的错误的解决方案

1.执行下列语句
create table tmp_5302_ins_prf_agtlevel_3b as
select distinct a.company_cd, a.agent_level, a.INSURED_RNG_TYPE, b.INSURED_RNG_SUB_TYPE, b.INSURED_RNG_VALUE, a.minus_NOI
from
(select company_cd, agent_level, INSURED_RNG_TYPE, NULL INSURED_RNG_SUB_TYPE, sum(case when INSURED_RNG_VALUE<>'z) Total' then INSURED_RNG_AVG_NOI end)-max(case when INSURED_RNG_VALUE<>'z) Total' then INSURED_RNG_AVG_NOI end) minus_NOI
 from tmp_5302_ins_prf_agtlevel_2 a where INSURED_RNG_TYPE='TENURE_RNG'
 group by company_cd, agent_level, INSURED_RNG_TYPE, NULL
 ) a,
(select company_cd, agent_level, INSURED_RNG_TYPE, INSURED_RNG_SUB_TYPE, INSURED_RNG_VALUE
 from --取RNG中最大的INSURED_RNG_VALUE
 (select company_cd, agent_level, INSURED_RNG_TYPE, INSURED_RNG_SUB_TYPE, INSURED_RNG_VALUE, row_number() over(partition by company_cd, agent_level, INSURED_RNG_TYPE order by INSURED_RNG_AVG_NOI desc) get_ind
 from tmp_5302_ins_prf_agtlevel_2 a where INSURED_RNG_VALUE<>'z) Total' and INSURED_RNG_TYPE='TENURE_RNG')
 where get_ind=1
 ) b
where a.company_cd=b.company_cd and a.agent_level=b.agent_level;

2. 报错信息
ORA-00600: 内部错误代码,参数:[rwoirw: check ret val], [],[],[],[],[],[],

3. trace 文件信息
Tue Feb 25 09:27:23 2014
Errors in file /opt/app/oracle/diag/rdbms/cddwh01/cddwh01/trace/cddwh01_ora_7081.trc  (incident=12305):
ORA-00600: internal error code, arguments: [rwoirw: check ret val], [], [], [], [], [], [], [], [], [], [], []
Incident details in: /opt/app/oracle/diag/rdbms/cddwh01/cddwh01/incident/incdir_12305/cddwh01_ora_7081_i12305.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Tue Feb 25 09:27:29 2014
Dumping diagnostic data in directory=[cdmp_20140225092729], requested by (instance=1, osid=7081), summary=[incident=12305].
Tue Feb 25 09:27:30 2014
Sweep [inc][12305]: completed
Sweep [inc2][12305]: completed
Tue Feb 25 09:42:04 2014

Trace file /opt/app/oracle/diag/rdbms/cddwh01/cddwh01/trace/cddwh01_ora_7081.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Automatic Storage Management and Data Mining options
ORACLE_HOME = /opt/app/oracle/product/11.2/db
System name:    Linux
Node name:      chndsora9
Release:        3.0.76-0.11-default
Version:        #1 SMP Fri Jun 14 08:21:43 UTC 2013 (ccab990)
Machine:        x86_64
VM name:        VMWare Version: 6
Instance name: cddwh01
Redo thread mounted by this instance: 1
Oracle process number: 38
Unix process pid: 7081, image: oracle@chndsora9
 
 
*** 2014-02-25 09:27:23.440
*** SESSION ID:(201.5127) 2014-02-25 09:27:23.440
*** CLIENT ID:() 2014-02-25 09:27:23.440
*** SERVICE NAME:(cddwh01) 2014-02-25 09:27:23.440
*** MODULE NAME:(SQL*Plus) 2014-02-25 09:27:23.440
*** ACTION NAME:() 2014-02-25 09:27:23.440
 
Incident 12305 created, dump file: /opt/app/oracle/diag/rdbms/cddwh01/cddwh01/incident/incdir_12305/cddwh01_ora_7081_i12305.trc
ORA-00600: internal error code, arguments: [rwoirw: check ret val], [], [], [], [], [], [], [], [], [], [], []

--incident详细信息:
oracle@chndsora9:/opt/app/oracle/diag/rdbms/cddwh01/cddwh01/trace> view /opt/app/oracle/diag/rdbms/cddwh01/cddwh01/incident/incdir_12305/cddwh01_ora_7081_i12305.trc
 
Dump file /opt/app/oracle/diag/rdbms/cddwh01/cddwh01/incident/incdir_12305/cddwh01_ora_7081_i12305.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Automatic Storage Management and Data Mining options
ORACLE_HOME = /opt/app/oracle/product/11.2/db
System name:    Linux
Node name:      chndsora9
Release:        3.0.76-0.11-default
Version:        #1 SMP Fri Jun 14 08:21:43 UTC 2013 (ccab990)
Machine:        x86_64
VM name:        VMWare Version: 6
Instance name: cddwh01
Redo thread mounted by this instance: 1
Oracle process number: 38
Unix process pid: 7081, image: oracle@chndsora9
 
 
*** 2014-02-25 09:27:23.440
*** SESSION ID:(201.5127) 2014-02-25 09:27:23.440
*** CLIENT ID:() 2014-02-25 09:27:23.440
*** SERVICE NAME:(cddwh01) 2014-02-25 09:27:23.440
*** MODULE NAME:(SQL*Plus) 2014-02-25 09:27:23.440
*** ACTION NAME:() 2014-02-25 09:27:23.440
 
Dump continued from file: /opt/app/oracle/diag/rdbms/cddwh01/cddwh01/trace/cddwh01_ora_7081.trc
ORA-00600: internal error code, arguments: [rwoirw: check ret val], [], [], [], [], [], [], [], [], [], [], []
 
========= Dump for incident 12305 (ORA 600 [rwoirw: check ret val]) ========
 
*** 2014-02-25 09:27:23.453
dbkedDefDump(): Starting incident default dumps (flags=0x2, level=3, mask=0x0)
----- Current SQL Statement for this session (sql_id=cb5bxd8dnzqwf) -----
create table tmp_5302_ins_prf_agtlevel_3b as
select distinct a.company_cd, a.agent_level, a.INSURED_RNG_TYPE, b.INSURED_RNG_SUB_TYPE, b.INSURED_RNG_VALUE, a.minus_NOI
from
(select company_cd, agent_level, INSURED_RNG_TYPE, NULL INSURED_RNG_SUB_TYPE, sum(case when INSURED_RNG_VALUE<>'z) Total' then INSURED_RNG_AVG_NOI end)-max(case when INSURED_RNG_VALUE<>'z) Total' then INSURED_RNG_AVG_NOI end) minus_NOI
 from tmp_5302_ins_prf_agtlevel_2 a where INSURED_RNG_TYPE='TENURE_RNG'
 group by company_cd, agent_level, INSURED_RNG_TYPE, NULL
 ) a,
(select company_cd, agent_level, INSURED_RNG_TYPE, INSURED_RNG_SUB_TYPE, INSURED_RNG_VALUE
 from --??RNG????????INSURED_RNG_VALUE
 (select company_cd, agent_level, INSURED_RNG_TYPE, INSURED_RNG_SUB_TYPE, INSURED_RNG_VALUE, row_number() over(partition by company_cd, agent_level, INSURED_RNG_TYPE order by INSURED_RNG_AVG_NOI desc) get_ind
 from tmp_5302_ins_prf_agtlevel_2 a where INSURED_RNG_VALUE<>'z) Total' and INSURED_RNG_TYPE='TENURE_RNG')
 where get_ind=1
 ) b
where a.company_cd=b.company_cd and a.agent_level=b.agent_level
 

--查看trace文件没有收获,分析SQL, 去掉create table,只有select, 发现是可以成功的.所以尝试把create table 改为create view 是可以成功的. 但是使用此view来create table还是报错.

--进一步分析,应该是bug 所造成,等打开老的cddwh01数据库运行此语句,如果成功,就可以判断是11.2.0.4的一个bug了.

--在metalink搜索此错误: create table [rwoirw: check ret val]

Bug 14275161 - ORA-600 [rwoirw: check ret val] on CTAS with predicate move around (Doc ID 14275161.8)

To Bottom

---

Modified:Feb 12, 2014Type:PATCH

Bug 14275161  ORA-600 [rwoirw: check ret val] on CTAS with predicate move around

 This note gives a brief overview of bug 14275161.  
 The content was last updated on: 11-FEB-2014
  Click here for details of each of the sections below.

Affects:

Product (Component)	Oracle Server (Rdbms)
Range of versions believed to be affected	(Not specified)
Versions confirmed as being affected	11.2.0.4 11.2.0.3
Platforms affected	Generic (all / most platforms affected)

Fixed:

The fix for 14275161 is first included in

12.1.0.1 (Base Release)

Interim patches may be available for earlier versions - click here to check.

Symptoms:	Related To:
Internal Error May Occur (ORA-600) ORA-600 [rwoirw: check ret val]	Optimizer _pred_move_around CREATE TABLE .. AS SELECT

Description

A CTAS (Create table as select) operation undergoing predicate move around 
may fail during execution with an ORA-600 [rwoirw: check ret val] Workaround Disable predicate move around before executing the CTAS.
 eg: 
  alter session set "_pred_move_around"=FALSE;
 

Please note: The above is a summary description only. Actual symptoms can vary. Matching to any symptoms here does not confirm that you are encountering this problem. For questions about this bug please consult Oracle Support.

References

Bug:14275161  (This link will only work for PUBLISHED bugs)
Note:245840.1  Information on the sections in this article

--采用metalink的方案,问题解决
alter session set "_pred_move_around"=true;

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

转载于:http://blog.itpub.net/674865/viewspace-1089357/