執行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:
 
 
*** 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詳細資訊:
> 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:
 
 
*** 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]

--採用metalink的方案,問題解決
alter session set "_pred_move_around"=true;