联系：手机/微信(+86 17813235971) QQ(107644445)
作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
对应cluster table使用bbed删除其中的部分记录,一直没有被攻克的难关,今天突发灵感,解决了cluster table 通过bbed删除记录后验证不能通过的难题.主要修改操作:oracle在index cluster中删除一条记录实际上只是把这条记录的行头由0x6c修改为0x7c,并且把这条记录所对应的聚簇键所在行的行头中记录的comc减1;修改验证信息
模拟cluster table 环境
SQL> create cluster clu_xff(id number(4));
Cluster created.
SQL> create table t_xifenfei
2 (id number(4)
3 ,name varchar2(25)
4 )CLUSTER clu_xff (id);
Table created.
SQL> create index ind_clu_xff ON CLUSTER clu_xff;
Index created.
SQL> insert into t_xifenfei values(1,'xifenfei');
1 row created.
SQL> insert into t_xifenfei values(2,'www.xifenfei.com');
1 row created.
SQL> insert into t_xifenfei values(3,'XIFENFEI');
1 row created.
SQL> insert into t_xifenfei values(4,'WWW.XIFENFEI.COM');
1 row created.
SQL> insert into t_xifenfei values(2,'WWW.xifenfei.COM');
1 row created.
SQL> insert into t_xifenfei values(3,'XFF_CHF');
1 row created.
SQL> COMMIT;
Commit complete.
SQL> select t.*,
2 dbms_rowid.rowid_relative_fno(rowid)||'_'||dbms_rowid.rowid_block_number(rowid) location
3 from t_xifenfei t;
ID NAME LOCATION
---------- ------------------------- ----------
2 www.xifenfei.com 4_171
2 WWW.xifenfei.COM 4_171
3 XIFENFEI 4_172
3 XFF_CHF 4_172 <----需要删除记录
4 WWW.XIFENFEI.COM 4_174
1 xifenfei 4_175
6 rows selected.
SQL> alter system checkpoint;
System altered.
SQL> conn / as sysdba
Connected.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
bbed删除记录操作
[oracle@xifenfei ~]$ bbed listfile=listfile mode=edit password=blockedit
BBED: Release 2.0.0.0.0 - Limited Production on Thu Aug 9 09:33:58 2012
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
************* !!! For Oracle Internal Use only !!! ***************
BBED> set file 4 block 172
FILE# 4
BLOCK# 172
BBED> map
File: /u01/oracle/oradata/ora11g/users01.dbf (4)
Block: 172 Dba:0x010000ac
------------------------------------------------------------
KTB Data Block (Table/Cluster)
struct kcbh, 20 bytes @0
struct ktbbh, 72 bytes @20
struct kdbh, 14 bytes @100
struct kdbt[2], 8 bytes @114
sb2 kdbr[3] @122
ub1 freespace[8013] @128
ub1 rowdata[47] @8141
ub4 tailchk @8188
--查看该块的相关数据值
BBED> p kdbr
sb2 kdbr[0] @122 8066
sb2 kdbr[1] @124 8053
sb2 kdbr[2] @126 8041
BBED> p *kdbr[0]
rowdata[25]
-----------
ub1 rowdata[25] @8166 0xac
BBED> x /rn
rowdata[25] @8166
-----------
flag@8166: 0xac (KDRHFL, KDRHFF, KDRHFH, KDRHFK)
lock@8167: 0x00
cols@8168: 1
kref@8169: 2
mref@8171: 2
hrid@8173:0x010000ac.0
nrid@8179:0x010000ac.0
col 0[2] @8185: 3
BBED> p *kdbr[1]
rowdata[12]
-----------
ub1 rowdata[12] @8153 0x6c
BBED> x /rc
rowdata[12] @8153
-----------
flag@8153: 0x6c (KDRHFL, KDRHFF, KDRHFH, KDRHFC)
lock@8154: 0x00
cols@8155: 1
col 0[8] @8157: XIFENFEI
BBED> p *kdbr[2]
rowdata[0]
----------
ub1 rowdata[0] @8141 0x6c
BBED> x /rc
rowdata[0] @8141
----------
flag@8141: 0x6c (KDRHFL, KDRHFF, KDRHFH, KDRHFC)
lock@8142: 0x02
cols@8143: 1
col 0[7] @8145: XFF_CHF <----需要删除记录
BBED> m /x 7c offset 8141
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y
File: /u01/oracle/oradata/ora11g/users01.dbf (4)
Block: 172 Offsets: 8141 to 8191 Dba:0x010000ac
------------------------------------------------------------------------
7c020100 07584646 5f434846 6c000100 08584946 454e4645 49ac0001 02000200
010000ac 00000100 00ac0000 02c10402 066c1d
<32 bytes per line>
BBED> m /x 01 offset 8171
File: /u01/oracle/oradata/ora11g/users01.dbf (4)
Block: 172 Offsets: 8171 to 8191 Dba:0x010000ac
------------------------------------------------------------------------
01000100 00ac0000 010000ac 000002c1 0402066c 1d
<32 bytes per line>
BBED> p *kdbr[0]
rowdata[25]
-----------
ub1 rowdata[25] @8166 0xac
BBED> x /rn
rowdata[25] @8166
-----------
flag@8166: 0xac (KDRHFL, KDRHFF, KDRHFH, KDRHFK)
lock@8167: 0x00
cols@8168: 1
kref@8169: 2
mref@8171: 1
hrid@8173:0x010000ac.0
nrid@8179:0x010000ac.0
col 0[2] @8185: 3
BBED> sum apply
Check value for File 4, Block 172:
current = 0x8f87, required = 0x8f87
BBED> verify
DBVERIFY - Verification starting
FILE = /u01/oracle/oradata/ora11g/users01.dbf
BLOCK = 172
Block Checking: DBA = 16777388, Block Type = KTB-managed data block
data header at 0xb53ed264
kdbchk: the amount of space used is not equal to block size <----数据块使用空间错误
used=67 fsc=0 avsp=8013 dtl=8088
Block 172 failed with check code 6110
DBVERIFY - Verification complete
Total Blocks Examined : 1
Total Blocks Processed (Data) : 1
Total Blocks Failing (Data) : 1
Total Blocks Processed (Index): 0
Total Blocks Failing (Index): 0
Total Blocks Empty : 0
Total Blocks Marked Corrupt : 0
Total Blocks Influx : 0
Message 531 not found; product=RDBMS; facility=BBED
BBED> p kdbh
struct kdbh, 14 bytes @100
ub1 kdbhflag @100 0x00 (NONE)
sb1 kdbhntab @101 2
sb2 kdbhnrow @102 3
sb2 kdbhfrre @104 -1
sb2 kdbhfsbo @106 28
sb2 kdbhfseo @108 8041
sb2 kdbhavsp @110 8013
sb2 kdbhtosp @112 8013
BBED> m /x 551f offset 110
File: /u01/oracle/oradata/ora11g/users01.dbf (4)
Block: 172 Offsets: 110 to 126 Dba:0x010000ac
------------------------------------------------------------------------
551f4d1f 00000100 01000200 821f751f
<32 bytes per line>
BBED> p kdbh
struct kdbh, 14 bytes @100
ub1 kdbhflag @100 0x00 (NONE)
sb1 kdbhntab @101 2
sb2 kdbhnrow @102 3
sb2 kdbhfrre @104 -1
sb2 kdbhfsbo @106 28
sb2 kdbhfseo @108 8041
sb2 kdbhavsp @110 8021
sb2 kdbhtosp @112 8013
BBED> sum apply
Check value for File 4, Block 172:
current = 0x8f9f, required = 0x8f9f
BBED> verify
DBVERIFY - Verification starting
FILE = /u01/oracle/oradata/ora11g/users01.dbf
BLOCK = 172
Block Checking: DBA = 16777388, Block Type = KTB-managed data block
data header at 0xb53ed264
kdbchk: avsp(8021) > tosp(8013) <----avsp 不能大于tosp
Block 172 failed with check code 6128
DBVERIFY - Verification complete
Total Blocks Examined : 1
Total Blocks Processed (Data) : 1
Total Blocks Failing (Data) : 1
Total Blocks Processed (Index): 0
Total Blocks Failing (Index): 0
Total Blocks Empty : 0
Total Blocks Marked Corrupt : 0
Total Blocks Influx : 0
Message 531 not found; product=RDBMS; facility=BBED
BBED> m /x 551f offset 112
File: /u01/oracle/oradata/ora11g/users01.dbf (4)
Block: 172 Offsets: 112 to 128 Dba:0x010000ac
------------------------------------------------------------------------
551f0000 01000100 0200821f 751f691f
<32 bytes per line>
BBED> sum apply
Check value for File 4, Block 172:
current = 0x8f87, required = 0x8f87
BBED> verify
DBVERIFY - Verification starting
FILE = /u01/oracle/oradata/ora11g/users01.dbf
BLOCK = 172
Block Checking: DBA = 16777388, Block Type = KTB-managed data block
data header at 0xb53ed264
kdbchk: space available on commit is incorrect
tosp=8021 fsc=0 stb=4 avsp=8021 <----tosp值不合适
Block 172 failed with check code 6111
DBVERIFY - Verification complete
Total Blocks Examined : 1
Total Blocks Processed (Data) : 1
Total Blocks Failing (Data) : 1
Total Blocks Processed (Index): 0
Total Blocks Failing (Index): 0
Total Blocks Empty : 0
Total Blocks Marked Corrupt : 0
Total Blocks Influx : 0
Message 531 not found; product=RDBMS; facility=BBED
BBED> set count 64
COUNT 64
BBED> m /x 591f offset 112
File: /u01/oracle/oradata/ora11g/users01.dbf (4)
Block: 172 Offsets: 112 to 175 Dba:0x010000ac
------------------------------------------------------------------------
591f0000 01000100 0200821f 751f691f 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
<32 bytes per line>
BBED> sum apply
Check value for File 4, Block 172:
current = 0x8f8b, required = 0x8f8b
--修改块工作完成
BBED> verify
DBVERIFY - Verification starting
FILE = /u01/oracle/oradata/ora11g/users01.dbf
BLOCK = 172
DBVERIFY - Verification complete
Total Blocks Examined : 1
Total Blocks Processed (Data) : 1
Total Blocks Failing (Data) : 0
Total Blocks Processed (Index): 0
Total Blocks Failing (Index): 0
Total Blocks Empty : 0
Total Blocks Marked Corrupt : 0
Total Blocks Influx : 0
Message 531 not found; product=RDBMS; facility=BBED
启动库验证
SQL> startup
ORACLE instance started.
Total System Global Area 313860096 bytes
Fixed Size 1344652 bytes
Variable Size 260049780 bytes
Database Buffers 46137344 bytes
Redo Buffers 6328320 bytes
Database mounted.
Database opened.
SQL> conn chf/xifenfei
Connected.
SQL> select * from t_xifenfei;
ID NAME
---------- -------------------------
2 www.xifenfei.com
2 WWW.xifenfei.COM
3 XIFENFEI
4 WWW.XIFENFEI.COM
1 xifenfei
--XFF_CHF记录被删除
SQL> insert into t_xifenfei values(3,'惜分飞');
1 row created.
SQL> delete from t_xifenfei where name='XIFENFEI';
1 row deleted.
SQL> commit;
Commit complete.
SQL> select * from t_xifenfei;
ID NAME
---------- -------------------------
2 www.xifenfei.com
2 WWW.xifenfei.COM
3 惜分飞
4 WWW.XIFENFEI.COM
1 xifenfei
--证明XFF_CHF所在数据块其他dml操作正常,证明修改正确
加我QQ
#define KDRHFK 0×80 Cluster Key
#define KDRHFC 0×40 Clustered table member
#define KDRHFH 0×20 Head piece of row
#define KDRHFD 0×10 Deleted row
#define KDRHFF 0×08 First data piece|
#define KDRHFL 0×04 Last data piece
#define KDRHFP 0×02 First column continues from Previous piece
#define KDRHFN 0×01 Last column continues in Next piece