问题描述
虽然访问外部表时Oracle提供了ROWID伪列，但是尝试通过ROWID访问外部表就会导致这个ORA-600的错误。
外部表的ROWID信息：http://yangtingkun.net/?p=176
详细错误如下：
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:\oracle\product\admin\ytk102\udump\ytk102_ora_7764.trc: ORA-00600: 内部错误代码, 参数: [qknltAllocate_10], [53513], [], [], [], [], [], [] Tue Oct 04 18:14:30 2011 Errors IN file d:\oracle\product\admin\ytk102\udump\ytk102_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:\oracle\product\10.2.0\rdbms\trace\ytk102_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
在metalink文档ID 395144.1中描述了这个错误，不过Oracle认为这并不是一个bug，因此也没有必要去解决，对这个问题的处理方法就是，不要尝试利用ROWID去访问外部表。看来外部表的ROWID唯一的作用可能就是用在ORDER BY语句中了。
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
