[20190918]shrink space與ORA-08102錯誤.txt1.環境:SCOTT@test01p> @ ver1PORT_STRING VERSION BANNER CON_ID IBMPC/WIN_NT64-9.1.0 12.2.0.1.0 Oracle Database 12c ...
[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,最好先檢查看看是否曾經這樣增加過新欄位.
