執行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]
--採用metalink的方案,問題解決
alter session set "_pred_move_around"=true;
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/674865/viewspace-1089357/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- IIS 7.5 解析錯誤 命令執行漏洞解決方案
- ORA-600(17069)錯誤的解決過程
- 執行無法解決的編譯錯誤編譯
- 應用儲存過程執行報錯解決方案儲存過程
- WCF執行錯誤:“此集合已經包含方案 http 的地址”的解決辦法HTTP
- 解決ORA-600(16164)錯誤的過程(二)
- 解決ORA-600(16164)錯誤的過程(一)
- 解決掉電導致的ORA-600(4194)錯誤
- IDEA啟動時報Failed to create JVM錯誤的解決IdeaAIJVM
- Incorrect key file for table '/tmp/....'錯誤的解決--記錄
- 解決aix下不能執行netca的錯誤AI
- Node出錯導致執行崩潰的解決方案
- Qt報Multiple definition錯誤的解決QT
- 關於 Composer dump-autoload 執行報錯解決方案
- 解決MySQL server has gone away錯誤的解決方案MySqlServerGo
- latex 錯誤以及解決方案
- Docker Hello World容器執行報錯的解決辦法Docker
- tomcat錯誤資訊解決方案【嚴重:StandardServer.await:create[8005]】TomcatServerAI
- 執行Docker命令報錯解決辦法Docker
- navicat連線MySQL8.0.11報2059錯誤的解決方案MySql
- java:錯誤:不支援發行版本1.4解決方案Java
- ORA-00845的錯誤與解決方案
- SVN 遇到的一些錯誤解決方案
- Django(21)migrate報錯的解決方案Django
- ORA-01756報錯的解決方案
- 關於MAC M1處理器執行Android protoc報錯的解決方案MacAndroid
- 在RAC 中解決 vipca 和 srvctl 無法執行的錯誤PCA
- linux執行sh報錯:$’\r’: 未找到命令的解決Linux
- 解決了一例Shutdown時碰到Ora-600錯誤的問題
- ORA-600(2662)錯誤的重現和解決(二)
- ORA-600(2662)錯誤的重現和解決(一)
- 執行轉換時如何讓Kettle記錄錯誤並繼續執行?——記一種解決方案
- CentOS 中yum命令執行錯誤解決辦法CentOS
- mac 下PyCharm執行報錯問題解決MacPyCharm
- SSL錯誤ssl connect error 35的解決方案Error
- MySQL連線錯誤(10048)的解決方案MySql
- [轉]MySql錯誤程式碼1045的解決方案MySql
- 執行 PHP artisan migrate 時報長度錯誤的解決辦法?PHP