欢迎投稿

今日深度:

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

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


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

www.htsjk.Com true http://www.htsjk.com/oracle/35775.html NewsArticle [20190918]shrink space与ORA-08102错误.txt,2shrink [20190918]shrink space与ORA-08102错误.txt 1.环境: SCOTT@test01p @ ver1 PORT_STRING VERSION BANNER CON_ID ------------------------------ -------------- -----------------------------...
相关文章
    暂无相关文章
评论暂时关闭