[20161025]ORA-00600 [kkpo_rcinfo_defstgdelseg], [xxxx].txt
--链接http://www.anbob.com/archives/2745.html,重复操作，做1个记录。
--模拟ORA-00600 [kkpo_rcinfo_defstgdelseg], [xxxx]错误
1.环境：
SCOTT@book> @ &r/ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
SCOTT@book> show parameter defer
NAME TYPE VALUE
-------------------------- --------- -------
deferred_segment_creation boolean TRUE
create table tpart(id number, name varchar2(100))
partition by range(id)
(
partition p_100 values less than(101),
partition p_200 values less than(201),
partition p_300 values less than(301)
);
2.测试：
SCOTT@book> select * from dba_objects where owner=user and object_name='TPART';
OWNER OBJECT_NAME SUBOBJECT_ OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE CREATED LAST_DDL_TIME TIMESTAMP STATUS T G S NAMESPACE EDITION_NAME
------ ----------- ---------- ---------- -------------- --------------- ------------------- ------------------- ------------------- ------- - - - ---------- ------------
SCOTT TPART 88785 TABLE 2016-10-27 09:18:02 2016-10-27 09:18:02 2016-10-27:09:18:02 VALID N N N 1
SCOTT TPART P_100 88786 88786 TABLE PARTITION 2016-10-27 09:18:02 2016-10-27 09:18:02 2016-10-27:09:18:02 VALID N N N 1
SCOTT TPART P_200 88787 88787 TABLE PARTITION 2016-10-27 09:18:02 2016-10-27 09:18:02 2016-10-27:09:18:02 VALID N N N 1
SCOTT TPART P_300 88788 88788 TABLE PARTITION 2016-10-27 09:18:02 2016-10-27 09:18:02 2016-10-27:09:18:02 VALID N N N 1
SCOTT@book> select ts#,file#,block#,flags,bitand(flags, 65536) from sys.tabpart$ where obj#=88786;
TS# FILE# BLOCK# FLAGS BITAND(FLAGS,65536)
---------- ---------- ---------- ---------- -------------------
4 0 0 65536 65536
--//由于使用段延迟建立，没有数据的对象，没有分配段。插入1行，分配段看看。
SCOTT@book> insert into tpart values(1,'a');
1 row created.
SCOTT@book> commit ;
Commit complete.
SCOTT@book> select ts#,file#,block#,flags,bitand(flags, 65536) from sys.tabpart$ where obj#=88786;
TS# FILE# BLOCK# FLAGS BITAND(FLAGS,65536)
---------- ---------- ---------- ---------- -------------------
4 4 657 0 0
--一旦段分配了flags不再等于65536，而且file#，block#不再是0.
SCOTT@book> select owner,segment_name,partition_name,segment_type,header_file,header_block,bytes,blocks,extents from dba_segments where owner=user and segment_name='TPART';
OWNER SEGMENT_NAME PARTITION_NAME SEGMENT_TYPE HEADER_FILE HEADER_BLOCK BYTES BLOCKS EXTENTS
------ -------------------- ------------------------------ ------------------ ----------- ------------ ---------- ---------- ----------
SCOTT TPART P_100 TABLE PARTITION 4 657 8388608 1024 1
3.开始破坏数据字典不一致。
SCOTT@book> select obj#,ts#,file#,block#,flags,bitand(flags, 65536) from sys.tabpart$ where obj# in (88786,88787,88788);
OBJ# TS# FILE# BLOCK# FLAGS BITAND(FLAGS,65536)
---------- ---------- ---------- ---------- ---------- -------------------
88786 4 4 657 0 0
88787 4 0 0 65536 65536
88788 4 0 0 65536 65536
SYS@book> update tabpart$ set flags=0 where obj#=88787;
1 row updated.
SYS@book> commit ;
Commit complete.
SCOTT@book> select obj#,ts#,file#,block#,flags,bitand(flags, 65536) from sys.tabpart$ where obj# in (88786,88787,88788);
OBJ# TS# FILE# BLOCK# FLAGS BITAND(FLAGS,65536)
---------- ---------- ---------- ---------- ---------- -------------------
88786 4 4 657 0 0
88787 4 0 0 0 0
88788 4 0 0 65536 65536
SCOTT@book> alter system flush shared_pool;
System altered.
SCOTT@book> select * from tpart partition(p_200);
select * from tpart partition(p_200)
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [kkpo_rcinfo_defstg:delseg], [88787], [], [], [], [], [], [], [], [], [], []
-- 产生错误，第2个参数值obj#。
select i.obj#, i.ts#, i.file#, i.block#, i.bo#, s.type#
from seg$ s, tabpart$ i
where i.ts#=s.ts#(+)
and i.file#=s.file#(+)
and i.block#=s.block#(+)
and i.dataobj# is not null /* A Physical object, Excludes IOT */
and nvl(s.type#,0)!=5
and bitand(i.flags, 65536) != 65536 /* Exclude DEFERRED Segment */;
OBJ# TS# FILE# BLOCK# BO# TYPE#
---------- ---------- ---------- ---------- ---------- ----------
88787 4 0 0 88785
SCOTT@book> alter table tpart modify partition p_200 allocate extent(size 10m);
alter table tpart modify partition p_200 allocate extent(size 10m)
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [25027], [4], [0], [], [], [], [], [], [], [], [], []
SCOTT@book> alter table tpart move partition p_200;
alter table tpart move partition p_200
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [ktadrprc-1], [], [], [], [], [], [], [], [], [], [], []
--//使用分区交换看看。
SCOTT@book> create table tpart_bak as select * from tpart partition(p_100) where 1=0;
Table created.
SCOTT@book> alter table tpart exchange partition p_200 with table tpart_bak;
alter table tpart exchange partition p_200 with table tpart_bak
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [ktsssun_segment2], [4], [0], [0], [], [], [], [], [], [], [], []
4.修正回来：
SYS@book> update tabpart$ set flags=65536 where obj#=88787;
1 row updated.
SYS@book> commit ;
Commit complete.
SCOTT@book> alter system flush shared_pool;
System altered.
SCOTT@book> select * from tpart partition(p_200);
no rows selected
--ok现在问题消失。
SCOTT@book> select obj#,ts#,file#,block#,flags,bitand(flags, 65536) from sys.tabpart$ where obj# in (88786,88787,88788);
OBJ# TS# FILE# BLOCK# FLAGS BITAND(FLAGS,65536)
---------- ---------- ---------- ---------- ---------- -------------------
88786 4 4 657 0 0
88787 4 0 0 65536 65536
88788 4 0 0 65536 65536
--move看看，是否分配段。
SCOTT@book> select obj#,ts#,file#,block#,flags,bitand(flags, 65536) from sys.tabpart$ where obj# in (88786,88787,88788);
OBJ# TS# FILE# BLOCK# FLAGS BITAND(FLAGS,65536)
---------- ---------- ---------- ---------- ---------- -------------------
88786 4 4 657 0 0
88787 4 0 0 65536 65536
88788 4 0 0 65536 65536
SCOTT@book> select owner,segment_name,partition_name,segment_type,header_file,header_block,bytes,blocks,extents from dba_segments where owner=user and segment_name='TPART';
OWNER SEGMENT_NAME PARTITION_NAME SEGMENT_TYPE HEADER_FILE HEADER_BLOCK BYTES BLOCKS EXTENTS
------ -------------------- ------------------------------ ------------------ ----------- ------------ ---------- ---------- ----------
SCOTT TPART P_100 TABLE PARTITION 4 657 8388608 1024 1
SCOTT@book> alter table tpart modify partition p_200 allocate extent(size 10m);
Table altered.
SCOTT@book> select owner,segment_name,partition_name,segment_type,header_file,header_block,bytes,blocks,extents from dba_segments where owner=user and segment_name='TPART';
OWNER SEGMENT_NAME PARTITION_NAME SEGMENT_TYPE HEADER_FILE HEADER_BLOCK BYTES BLOCKS EXTENTS
------ -------------------- -------------- ------------------ ----------- ------------ ---------- ---------- ----------
SCOTT TPART P_200 TABLE PARTITION 4 1681 18874368 2304 4
SCOTT TPART P_100 TABLE PARTITION 4 657 8388608 1024 1
--另外注意一个小细节，就是建立的段分配1024块，占用8M，这个oracle隐含参数影响_partition_large_extents=true影响，如果分区
--很多，而每个分区记录很小的情况下，空间浪费很大。
SYS@book> @ &r/hide _partition_large_extents
NAME DESCRIPTION DEFAULT_VALUE SESSION_VALUE SYSTEM_VALUE
------------------------------ ------------------------------------------------------- ------------- ------------- ------------
_index_partition_large_extents Enables large extent allocation for partitioned indices TRUE FALSE FALSE
_partition_large_extents Enables large extent allocation for partitioned tables TRUE TRUE TRUE
总结:
因为某些原因数据字典表不一致，导到该表在查询或导出时都会提示ora-600 [kkpo_rcinfo_defstg:delseg] 错误，因为数据库使用延迟
段创建，手动分配segment时提示ORA-600 [25027],对分区做MOVE时会提示ORA-600 [ktadrprc-1]， 使用hcheck脚本检查会提示Orphaned
TABPART$ ,然后通过脚本中找到字典的错误，修改数据字典后记的flush shared_pool, 使用RAC 时刷新所有实例shared_pool，并重新登
录验证，问题得到解决。生产不建议对基表直接修改，或修改前备份。
