联系:手机/微信(+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操作正常,证明修改正确
#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