[20190918]shrink space与ORA-08102错误.txt

[20190918]shrink space与ORA-08102错误.txt 1.环境: SCOTT@test01p> @ ver1 PORT_STRING                    VERSION        BANNER                                                                               CON_ID ------------------------------ -------------- -------------------------------------------------------------------------------- ---------- IBMPC/WIN_NT64-9.1.0           12.2.0.1.0     Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production              0 2.再现ORA-08102错误: SCOTT@test01p> create table t(x int, pad varchar2(100)) enable row movement; Table created. SCOTT@test01p> insert /*+ append*/  into t select level, lpad('x', 100, 'x') from dual connect by level<=1e4; 10000 rows created. SCOTT@test01p> alter table t add y int default 10 not null; Table altered. SCOTT@test01p> create index i_t_xy on t(x,y); Index created. SCOTT@test01p> delete t where x<=5000; 5000 rows deleted. SCOTT@test01p> commit ; Commit complete. SCOTT@test01p> alter table t shrink space; alter table t shrink space * ERROR at line 1: ORA-08102: index key not found, obj# 27979, file 11, block 2445 (2) SCOTT@test01p> host  oerr ora 8102 08102, 00000, "index key not found, obj# %s, file %s, block %s (%s)" // *Cause:  Internal error: possible inconsistency in index // *Action:  Send trace file to your customer support representative, along //           with information on reproducing the error 3.10046跟踪看看. SCOTT@test01p> alter session set events '10046 level 12'; Session altered. SCOTT@test01p> alter table t shrink space; alter table t shrink space * ERROR at line 1: ORA-08102: index key not found, obj# 27979, file 11, block 2445 (2) SCOTT@test01p> alter session set events '10046 off'; Session altered. --//检查转储发现: oer 8102.2 - obj# 27979, rdba: 0x02c0098d(afn 11, blk# 2445) kdk key 8102.2:   ncol: 3, len: 12   key: (12):  03 c2 64 31 ff 06 02 c0 1d a5 00 00   mask: (2048): --//通过bbed观察看看. --//03 c2 64 31 ,03表示长度.后面3位表示oracle数字. SCOTT@test01p> @ conv_n c26431        N20 ----------       9948 BBED> set dba 11,2446         DBA             0x02c0098e (46139790 11,2446) --//注:windows下bbed,无法识别10g以上版本的os头,block存在+1的偏移. BBED> map  File: D:\APP\ORACLE\ORADATA\TEST\TEST01P\USERS01.DBF (11)  Block: 2446                                  Dba:0x02c0098e ------------------------------------------------------------  KTB Data Block (Index Leaf)  struct kcbh, 20 bytes                      @0  struct ktbbh, 72 bytes                     @20  struct kdxle, 32 bytes                     @100  b2 kd_off[399]                             @132 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~  ub1 freespace[822]                         @930  ub1 rowdata[6380]                          @1752  ub4 tailchk                                @8188 BBED> x /rnnx *kd_off[3] rowdata[6352]                               @8104 ------------- flag@8104:     0x00 (NONE) lock@8105:     0x00 data key: col    0[3] @8107: 9583 col    1[2] @8111: 10 col    2[6] @8114:  0x02  0xc0  0x1d  0x9f  0x00  0x19 --//9948-9583+3 = 368 BBED> x /rnnx *kd_off[368] rowdata[516]                                @2268 ------------ flag@2268:     0x00 (NONE) lock@2269:     0x00 data key: col    0[3] @2271: 9948 col    1[2] @2275: 10 col    2[6] @2278:  0x02  0xc0  0x1d  0xa5  0x00  0x00 BBED> x /rxxx *kd_off[368] rowdata[516]                                @2268 ------------ flag@2268:     0x00 (NONE) lock@2269:     0x00 data key: col    0[3] @2271:  0xc2  0x64  0x31 col    1[2] @2275:  0xc1  0x0b col    2[6] @2278:  0x02  0xc0  0x1d  0xa5  0x00  0x00 --//可以看出原来的key是 03 c2  64 31 02 c1  0b 06 02 c0 1d a5 00 00 --//而shrink space后,索引的键值发生了变化,变为如下: --//key: (12):  03 c2 64 31 ff 06 02 c0 1d a5 00 00 --//0xff表示NULL,参考链接:http://blog.itpub.net/267265/viewspace-2120439/=>[20160619]NULL在数据库的存储.txt --//也就是索引的第2字段oracle认为是NULL,也就是遇到这样的情况shrink space时.oracle错误的认为Y=null, --//因为这样的情况Y=10的值并没有保存在数据块中,而是放在sys.ecol$中. SCOTT@test01p> SELECT *  FROM sys.ecol$ WHERE tabobj# IN (SELECT DATA_OBJECT_ID FROM dba_objects WHERE owner = USER AND object_name = 'T');    TABOBJ#     COLNUM BINARYDEFVAL                     GUARD_ID ---------- ---------- ------------------------------ ----------      27978          3 C10B --//c10b对应number类型是数字10. --//对于这样的情况如果要降低HWM,仅仅ctas建立表以及索引. --//如果增加字段时写入数据块中,应该不会出现这样的情况.看了一下隐含参数,应该是_add_col_optim_enabled. SYS@test> @ hide _add_col_optim_enabled NAME                   DESCRIPTION                        DEFAULT_VALUE SESSION_VALUE SYSTEM_VALUE ISSES ISSYS_MOD ---------------------- ---------------------------------- ------------- ------------- ------------ ----- --------- _add_col_optim_enabled Allows new add column optimization TRUE          TRUE          TRUE         TRUE  IMMEDIATE SCOTT@test01p> alter session set "_add_col_optim_enabled"=false; Session altered. create table t1(x int, pad varchar2(100)) enable row movement; insert /*+ append*/  into t1 select level, lpad('x', 100, 'x') from dual connect by level<=1e4; alter table t1 add y int default 10 not null; create index i_t1_xy on t1(x,y); delete t1 where x<=5000; commit ; alter table t1 shrink space; SCOTT@test01p> alter table t1 shrink space; Table altered. --//当然这样增加字段就很慢!! 总结: 如果要做shrink space,最好先检查看看是否曾经这样增加过新字段.