bbed 删除 cluster table 记录

联系:手机/微信(+86 17813235971) QQ(107644445)QQ咨询惜分飞

标题:bbed 删除 cluster table 记录

作者:惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

对应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操作正常,证明修改正确
此条目发表在 Oracle 分类目录,贴了 标签。将固定链接加入收藏夹。

bbed 删除 cluster table 记录》有 1 条评论

  1. 惜分飞 说:

    #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