Oracle执行CreateTableAs报ORA-600错误

1. 执行语句

CREATE TABLE TempVIDSFrom5f009f41e4a843e09 as 
Select distinct ZJJSPZ_PZNM as NM,ZJJGDWGX_DWBH as GS,ZJJSPZ_PZBH as BH,ZJJSPZ_PZRQ as PZRQ,ZJJSPZ_PZBH as PZBH,ZJJSPZ_PZLX as PZLX, (SELECT cast(SUM(FL.ZJPZFL_JE) as decimal(20,2)) FROM ZJPZFL FL WHERE FL.ZJPZFL_PZNM=ZJJSPZ_PZNM and ZJPZFL_JZFX='1') as HJJE, ZJJSPZ_DJBH as DJBH,ZJJSPZ_DWBH as YWDW,ZJJSPZ_DFDW as DFDW,ZJJSPZ_ZDR as ZDR,ZJJSPZ_JZR as JZR,ZJJSPZ_JSFS as JSFS,ZJJSPZ_PJBH as PJBH,ZJJSPZ_FJZS as FJZS,ZJJSDJ_HL as HL,FLZH.ZJZH_BZBH as BZBH,ZJJSDJ_YWLX as YWLX,ZJJSPZ_JGBH as JGBH,ZJJGDWGX_DWBH as DWBH,ZJJSDJ_SRYSXM as SRYSXM,ZJJSDJ_ZCYSXM as ZCYSXM,ZJJSDJ_XM03 as YWBM,ZJJSPZ_YWSM as YWSM,ZJJSDJ_XM01 as YQZLQQH,ZJJSDJ_WLDWID as WLDW,ZJJSPZ_PZRQ as YWRQ  
 from ZJPZFL 
 INNER JOIN ZJJSPZ ON ZJJSPZ_PZNM = ZJPZFL_PZNM 
 INNER JOIN ZJZH FLZH ON ZJPZFL_ZHBH=FLZH.ZJZH_ZHNM 
 INNER JOIN LSBZDW ON FLZH.ZJZH_DWBH=LSBZDW_DWBH 
 INNER JOIN LSWBZD ON FLZH.ZJZH_BZBH=LSWBZD_BZBH 
 INNER JOIN ZJJGDWGX ON ZJJGDWGX_JGBH=ZJJSPZ_JGBH AND (ZJJGDWGX_BZDM=ZJPZFL_BZ OR ZJJGDWGX_BZDM='-1') 
 LEFT JOIN CodeItems ON ZJPZFL_ZY=NAME AND SetID='Z001'
 LEFT JOIN ZJZH ZXZH ON FLZH.ZJZH_ZHNM=ZXZH.ZJZH_ZHNM AND ZJJGDWGX_DWBH=ZXZH.ZJZH_DWBH AND ZXZH.ZJZH_NWBZ='1'
 LEFT JOIN ZJJSDJ ON ZJJSDJ_DJNM=ZJJSPZ_DJNM AND ZJJSPZ_GLB='ZJJSDJ' 
 LEFT JOIN HPBSSQ ON HPBSSQ_DJNM=ZJJSPZ_DJNM AND ZJJSPZ_GLB='HPBSSQ'   
 Where 1=1  and ZJJSPZ_JZBZ='1'  and ZJJGDWGX_DWBH = '01020100'  

2. 错误信息

ORA-00600: 内部错误代码,参数:[rwoirw: check ret val], [],[],[],[],[],[],

3. 原因分析

查看trace文件没有任何有帮助的信息，只执行后面的select语句正常，create table报错。

而且，Oracle11.2.0.4存在此问题，11.2.0.1不存在此问题。应该是11.2.0.4的bug。

4. 解决方法

更新OracleBug

 

 

附：

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;