ora-600 qkatab_10错误

使用rowid访问外部表时出现的错误

 

版本：11.1.0.7.0

 

select a.text
  from alert_log a
 where a.rowid > (select rowid
                    from alert_log a
                   where a.text like (select '%' || initcap(substr(to_char(sysdate,
                                                                           'dy mon dd HH24:mi:ss yyyy'),
                                                                   5,
                                                                   6)) || '%'
                                        from dual)
                     and rownum = 1)
   and a.text like '%ORA-%'

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

好象是个bug，11.2.0.1.0中应该修复了，运行一样的SQL在11.2.0.1.0版本没有报错

 

另附：http://blog.itpub.net/post/468/524092

 

详细错误如下：

SQL> create table t_alert
2 (text varchar2(1000)
3 )
4 organization external
5 (type oracle_loader
6 default directory d_alert
7 access parameters
8 (records delimited by newline
9 fields (text (1:255) char))
10 location ('alert_ytk102.log'));

表已创建。

SQL> SELECT ROWID FROM T_ALERT WHERE ROWNUM < 10;

ROWID
-----------------------
(AADRCQAAAAAAAAAAAAAAAA
(AADRCQAAAAAAAAAAAAAAQQ
(AADRCQAAAAAAAAAAAAAAXA
(AADRCQAAAAAAAAAAAAAAhg
(AADRCQAAAAAAAAAAAAAAmg
(AADRCQAAAAAAAAAAAAAAtA
(AADRCQAAAAAAAAAAAAAA6g
(AADRCQAAAAAAAAAAAAABDA
(AADRCQAAAAAAAAAAAAABVg

已选择9行。

SQL> SELECT * FROM T_ALERT WHERE ROWID = '(AADRCQAAAAAAAAAAAAAAAA';
SELECT * FROM T_ALERT WHERE ROWID = '(AADRCQAAAAAAAAAAAAAAAA'
*
第 1 行出现错误:
ORA-00600: 内部错误代码, 参数: [qknltAllocate_10], [53513], [], [], [], [], [], []

SQL> SELECT A.* FROM T_ALERT A WHERE A.ROWID IN (SELECT MIN(ROWID) FROM T_ALERT);
SELECT A.* FROM T_ALERT A WHERE A.ROWID IN (SELECT MIN(ROWID) FROM T_ALERT)
*
第 1 行出现错误:
ORA-00600: 内部错误代码, 参数: [qknltAllocate_10], [53513], [], [], [], [], [], []

SQL> SELECT DBMS_ROWID.ROWID_OBJECT(ROWID) FROM T_ALERT WHERE ROWNUM = 1;
SELECT DBMS_ROWID.ROWID_OBJECT(ROWID) FROM T_ALERT WHERE ROWNUM = 1
*
第 1 行出现错误:
ORA-06553: PLS-306: 调用 'ROWID_OBJECT' 时参数个数或类型错误

SQL> SELECT DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) FROM T_ALERT WHERE ROWNUM = 1;
SELECT DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) FROM T_ALERT WHERE ROWNUM = 1
*
第 1 行出现错误:
ORA-06553: PLS-306: 调用 'ROWID_RELATIVE_FNO' 时参数个数或类型错误

SQL> SELECT DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) FROM T_ALERT WHERE ROWNUM = 1;
SELECT DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) FROM T_ALERT WHERE ROWNUM = 1
*
第 1 行出现错误:
ORA-06553: PLS-306: 调用 'ROWID_BLOCK_NUMBER' 时参数个数或类型错误

SQL> SELECT DBMS_ROWID.ROWID_ROW_NUMBER(ROWID) FROM T_ALERT WHERE ROWNUM = 1;
SELECT DBMS_ROWID.ROWID_ROW_NUMBER(ROWID) FROM T_ALERT WHERE ROWNUM = 1
*
第 1 行出现错误:
ORA-06553: PLS-306: 调用 'ROWID_ROW_NUMBER' 时参数个数或类型错误

无论用哪种方法，只要试图通过ROWID访问外部表就会导致这个ORA-600错误，可以看到，DBMS_ROWID包也是不支持外部表的ROWID的。

Tue Oct 04 18:13:40 2011
Errors in file d:oracleproductadminytk102udumpytk102_ora_7764.trc:
ORA-00600: 内部错误代码, 参数: [qknltAllocate_10], [53513], [], [], [], [], [], []

Tue Oct 04 18:14:30 2011
Errors in file d:oracleproductadminytk102udumpytk102_ora_7764.trc:
ORA-00600: 内部错误代码, 参数: [qknltAllocate_10], [53513], [], [], [], [], [], []

详细错误信息为：

Tue Oct 04 18:13:40 2011
ORACLE V10.2.0.5.0 - Production vsnsta=0
vsnsql=14 vsnxtr=3
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Windows NT Version V6.1 Service Pack 1
CPU : 4 - type 586, 2 Physical Cores
Process Affinity : 0x00000000
Memory (Avail/Total): Ph:1914M/2995M, Ph+PgF:3697M/5989M, VA:1426M/2047M
Instance name: ytk102

Redo thread mounted by this instance: 1

Oracle process number: 23

Windows thread id: 7764, image: ORACLE.EXE (SHAD)

*** ACTION NAME:() 2011-10-04 18:13:40.367
*** MODULE NAME:(SQL*Plus) 2011-10-04 18:13:40.367
*** SERVICE NAME:(SYS$USERS) 2011-10-04 18:13:40.367
*** SESSION ID:(141.3) 2011-10-04 18:13:40.367
*** 2011-10-04 18:13:40.367
ksedmp: internal or fatal error
ORA-00600: 内部错误代码, 参数: [qknltAllocate_10], [53513], [], [], [], [], [], []
Current SQL statement for this session:
SELECT * FROM T_ALERT WHERE ROWID = '(AADRCQAAAAAAAAAAAAAAAA'
check trace file d:oracleproduct10.2.0rdbmstraceytk102_ora_0.trc for preloading .sym file messages
----- Call Stack Trace -----
calling call entry argument values in hex
location type point (? means dubious value)
-------------------- -------- -------------------- ----------------------------
_ksedst+38 CALLrel _ksedst1+0 0 1
_ksedmp+898 CALLrel _ksedst+0 0
_ksfdmp+70 CALLrel _ksedmp+0 3
0417C640 CALLreg 00000000 981C3B0 3
0417CA07 CALLrel 0417C5B4 981C3B0 573B20C 3B889D8 1
C2DB330
__VInfreq__qknltAll CALLrel _kgeasnmierr+0 981C3B0 573B20C 3B889D8 1 0
ocate+71 D109 0
_qkatab+4736 CALLrel _qknltAllocate+0
_qkajoi+296 CALLrel _qkatab+0
_qkaqkn+837 CALLrel _qkajoi+0
_qkadrv+686 CALLrel _qkaqkn+0 574EB60 1 0 C2DB7B8
_opitca+1990 CALLrel _qkadrv+0 574EB60 1
_kksLoadChild+8023 CALLrel _opitca+0 577E184 247B8478
_kxsGetRuntimeLock+ CALLrel _kksLoadChild+0 981C3B0 28480DFC C2DC910
1669
_kksfbc+10697 CALLrel _kxsGetRuntimeLock+ 981C3B0 577E184 C2DC910 3 1
0
_kkspsc0+1728 CALLrel _kksfbc+0 577E184 3 108 C2DD8BC 3E 0 0
0
_kksParseCursor+143 CALLrel _kkspsc0+0
_opiosq0+1923 CALLrel _kksParseCursor+0 C2DCED0
_kpooprx+234 CALLrel _opiosq0+0 3 E C2DD000 A4 0
_kpoal8+746 CALLrel _kpooprx+0 C2DF63C C2DD8BC 3D 1 0 A4
_opiodr+1306 CALLreg 00000000 5E 17 C2DF638
60FFDE4A CALLreg 00000000 5E 17 C2DF638 0
_opitsk+1102 CALL??? 00000000
_opiino+1081 CALLrel _opitsk+0 0 0
_opiodr+1306 CALLreg 00000000 3C 4 C2DFBF8
_opidrv+819 CALLrel _opiodr+0 3C 4 C2DFBF8 0
_sou2o+45 CALLrel _opidrv+0 3C 4 C2DFBF8
_opimai_real+112 CALLrel _sou2o+0 C2DFBEC 3C 4 C2DFBF8
_opimai+92 CALLrel _opimai_real+0 2 C2DFC24
_OracleThreadStart@ CALLrel _opimai+0
4+726
76F9ED67 CALLptr 00000000
771B37F3 CALLreg 00000000
771B37C3 CALLrel 771B37CE

--------------------- Binary Stack Dump ---------------------

在metalink文档ID 395144.1中描述了这个错误，不过Oracle认为这并不是一个bug，因此也没有必要去解决，对这个问题的处理方法就是，不要尝试利用ROWID去访问外部表。看来外部表的ROWID唯一的作用可能就是用在ORDER BY语句中了。