在本机的WINDOWS测试环境中遇到了下面这个错误：
> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production
> select sum(bytes)/1024/1024 from user_segments where segment_name='T1';
select sum(bytes)/1024/1024 from user_segments where segment_name='T1'
*
第 1 行出现错误:
ORA-00600: 内部错误代码, 参数: [kkoitbp-corruption], [], [], [], [], [], [], []
查询了一下MOS，与这个错误相关的信息并不多。
如果WHERE条件的谓词超过了6W个，可能会遭受这个BUG，另一个就是启用了星形查询也可能会遇到这个BUG。
- Bug 10348427 ORA-600 [kkoitbp-corruption] from query with many WHERE clause predicates (>60000)
A select query with a lot of conditions in the WHERE clause may fail with ORA-600 [kkoitbp-corruption] or other errors.
Rediscovery Notes:
The failing query has a large number of conditions in the WHERE clause (in the order of 65530 or more).
Workaround
Recode the SQL not to use such long predicate lists in a single statement.
- Bug 10038373 - Query fails with ORA-600 [kkoitbp-corruption] when star transformation is enabled [ID 10038373.8]
When star transformation is enabled (star_transformation_enabled parameter is TRUE), and in some other limited cases, a query may fail with ORA-600 [kkoitbp-corruption]
当前的查询谓词数很显然没有超过60000W，另一个就是启用了星形查询，不过即时将star_transformation_enabled设置为FALSE，问题还是存在：
> show parameter star
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
dg_broker_start boolean FALSE
drs_start boolean FALSE
fast_start_io_target integer 0
fast_start_mttr_target integer 0
fast_start_parallel_rollback string LOW
log_archive_start boolean FALSE
star_transformation_enabled string TRUE
> alter system set star_transformation_enabled=false;
系统已更改。
> select sum(bytes)/1024/1024 from user_segments where segment_name='T1';
select sum(bytes)/1024/1024 from user_segments where segment_name='T1'
*
第 1 行出现错误:
ORA-00600: 内部错误代码, 参数: [kkoitbp-corruption], [], [], [], [], [], [], []
如果使用RULE提示或者将优化器设置为RULE模式即可避免这个BUG。
> select /*+rule*/sum(bytes)/1024/1024 from user_segments where segment_name='T1';
SUM(BYTES)/1024/1024
--------------------
6
> alter session set optimizer_mode=rule;
会话已更改。
> select sum(bytes)/1024/1024 from user_segments where segment_name='T1';
SUM(BYTES)/1024/1024
--------------------
6
又是一个和CBO优化器相关的BUG。