[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,最好先检查看看是否曾经这样增加过新字段.