1025ORA-00600[kkpo_rcinfo_defstgdelseg]

最新推荐文章于 2023-04-12 21:11:12 发布

转载最新推荐文章于 2023-04-12 21:11:12 发布 · 193 阅读

0 ·

CC 4.0 BY-SA版权

原文链接：https://yq.aliyun.com/articles/279638

文章标签：

#嵌入式 #数据库

本文介绍了一种因数据字典不一致导致的ORA-00600错误，通过修改数据字典并刷新共享池解决了分区表在查询、分配段及移动时出现的问题。

[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.

--一旦段分配了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现在问题消失。

--move看看，是否分配段。

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，并重新登
录验证，问题得到解决。生产不建议对基表直接修改，或修改前备份。