在尝试把一个树形查询的SQL中引用的一张表换成视图的时候，居然出现了Oracle internal error (ORA-00600)! 如下，
原来的SQL...
SELECT E.CODE AS CHILD_CODE,
E.SHORT_NAME || ' (' || E.CODE || ')' AS CHILD_NAME,
E.SHORT_NAME AS SHORT_NAME,
EL.LINK_WEIGHT / 100 AS LINK_WEIGHT
FROM XX_ENTITY E,
(SELECT CHILD_CODE,
LINK_WEIGHT
FROM XX_ENTITY_LINKS
START WITH MOTHER_CODE = 2
CONNECT BY MOTHER_CODE = PRIOR CHILD_CODE
) el
WHERE E.CODE = EL.CHILD_CODE
这个SQL运行的很正常，现在打算将表XX_ENTITY换成视图V_XX_ENTITY_LINKS，如下...
SELECT E.ENTITY_CODE AS CHILD_CODE,
E.SHORT_NAME || ' (' || E.ENTITY_CODE || ')' AS CHILD_NAME,
E.SHORT_NAME AS SHORT_NAME,
EL.LINK_WEIGHT AS LINK_WEIGHT
FROM XX_ENTITY E,
(SELECT CHILD_CODE,
LINK_WEIGHT
FROM V_XX_ENTITY_LINKS
START WITH MOTHER_CODE = '2'
CONNECT BY MOTHER_CODE = PRIOR CHILD_CODE
) el
WHERE E.ENTITY_CODE = EL.CHILD_CODE;
居然碰到了如下错误信息...
ORA-00600: internal error code, arguments: [kkqcbydrv:1], [], [], [], [], [], [], []
00600. 00000 - "internal error code, arguments: [%s], [%s], [%s], [%s], [%s], [%s], [%s], [%s]"
*Cause: This is the generic internal error number for Oracle program
exceptions. This indicates that a process has encountered an
exceptional condition.
*Action: Report as a bug - the first argument is the internal error number
视图V_XX_ENTITY_LINKS其实还蛮简单的，如下，
CREATE OR REPLACE VIEW V_XX_ENTITY_LINKS
AS
select
(select e.entity_code from xx_entity e where e.id = el.mother_code) as mother_code,
(select e.entity_code from xx_entity e where e.id = el.child_code) as child_code,
el.type,
el.LINK_WEIGHT/100 as LINK_WEIGHT
FROM
XX_ENTITY_LINKS el;
网上看到有人提到通过可以设置隐含参数_optimizer_connect_by_cost_based来避免这个问题(需要用引号将隐含参数包含起来)，如下所示...
SQL> alter session set _optimizer_connect_by_cost_based = false;
alter session set _optimizer_connect_by_cost_based = false
*
ERROR at line 1:
ORA-00911: invalid character
SQL> alter session set "_optimizer_connect_by_cost_based" = false;
Session altered.
不过既然是隐含参数，自然是不推荐来更改的。最好还是老老实实改SQL语句来的放心。
在"Breaking Oracle"中看到这个问题是Oracle的一个bug, bug number 是 6073325。 不过这个bug好像在Oracle 11.1.0.7 patch set 里面给修正了,参见这里。
看下在11.1.0.6.0版本的数据库中的行为，
frank@ORCL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
PL/SQL Release 11.1.0.6.0 - Production
CORE 11.1.0.6.0 Production
TNS for 32-bit Windows: Version 11.1.0.6.0 - Production
NLSRTL Version 11.1.0.6.0 - Production
frank@ORCL> select 1 from sys.table_privileges tp, user_objects uo
2 where tp.grantee in
3 (select 1 from sys.dba_role_privs
4 connect by prior granted_role = grantee
5 start with grantee = 'scott');
select 1 from sys.table_privileges tp, user_objects uo
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [kkqcbydrv:1], [], [], [], [], [], [], []
可以看到这个问题还是存在的。但是将数据库升级到11.2.0.1.0就发现这个问题不存在了，如下所示...
frank@ORCL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for 32-bit Windows: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
frank@ORCL> select 1 from sys.table_privileges tp, user_objects uo
2 where tp.grantee in
3 (select '1' from sys.dba_role_privs
4 connect by prior granted_role = grantee
5 start with grantee = 'scott');
no rows selected
