联系：手机/微信(+86 17813235971) QQ(107644445)
标题：bbed 删除普通表记录
作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
有朋友和我说我的bbed系列缺少一篇普通表使用bbed删除记录的文章,月底现场保证回来没睡意,完善这篇文章,也算是对bbed系列的一个终结.
创建模拟记录
SQL> create table t_xifenfei tablespace users
2 as
3 select * from dba_tables where rownum<10;
Table created.
SQL> alter system checkpoint;
System altered.
SQL> select table_name,owner,rowid,
2 dbms_rowid.rowid_relative_fno(rowid)rel_fno,
3 dbms_rowid.rowid_block_number(rowid)blockno,
4 dbms_rowid.rowid_row_number(rowid) rowno
5 from t_xifenfei;
TABLE_NAME OWNER ROWID REL_FNO BLOCKNO ROWNO
------------------------------ ------------------------------ ------------------ ---------- ---------- ----------
CON$ SYS AAAM9UAAEAAACA0AAA 4 8244 0
UNDO$ SYS AAAM9UAAEAAACA0AAB 4 8244 1
CDEF$ SYS AAAM9UAAEAAACA0AAC 4 8244 2
CCOL$ SYS AAAM9UAAEAAACA0AAD 4 8244 3
PROXY_ROLE_DATA$ SYS AAAM9UAAEAAACA0AAE 4 8244 4
FILE$ SYS AAAM9UAAEAAACA0AAF 4 8244 5
FET$ SYS AAAM9UAAEAAACA0AAG 4 8244 6
TS$ SYS AAAM9UAAEAAACA0AAH 4 8244 7
PROXY_DATA$ SYS AAAM9UAAEAAACA0AAI 4 8244 8
9 rows selected.
SQL> select dump('FILE$',16) from dual;
DUMP('FILE$',16)
----------------------------
Typ=96 Len=5: 46,49,4c,45,24
SQL> select dump('SYS',16) FROM DUAL;
DUMP('SYS',16)
----------------------
Typ=96 Len=3: 53,59,53
SQL> SHUTDOWN IMMEDIATE
Database closed.
Database dismounted.
ORACLE instance shut down.
这里创建一张测试表,有9条记录,计划使用bbed删除file$的记录
bbed删除表记录
[oracle@xifenfei ~]$ bbed listfile=bbedfile
Password:
BBED: Release 2.0.0.0.0 - Limited Production on Sat Sep 1 10:28:57 2012
Copyright (c) 1982, 2005, Oracle. All rights reserved.
************* !!! For Oracle Internal Use only !!! ***************
BBED> info
File# Name Size(blks)
----- ---- ----------
1 /u01/oradata/orcl/system01.dbf 0
3 /u01/oradata/orcl/sysaux01.dbf 0
4 /u01/oradata/orcl/users01.dbf 0
5 /u01/oradata/orcl/GS_ORADB_001.dbf 0
6 /u01/oradata/orcl/GS_ORADB_IDX_001.dbf 0
7 /u01/oradata/orcl/undo01.dbf 0
BBED> set file 4 block 8244
FILE# 4
BLOCK# 8244
BBED> map
File: /u01/oradata/orcl/users01.dbf (4)
Block: 8244 Dba:0x01002034
------------------------------------------------------------
KTB Data Block (Table/Cluster)
struct kcbh, 20 bytes @0
struct ktbbh, 96 bytes @20
struct kdbh, 14 bytes @124
struct kdbt[1], 4 bytes @138
sb2 kdbr[9] @142
ub1 freespace[6137] @160
ub1 rowdata[1891] @6297
ub4 tailchk @8188
BBED> set count 32
COUNT 32
--查找对应值,估算起位置
BBED> find /x 494c4524
File: /u01/oradata/orcl/users01.dbf (4)
Block: 8244 Offsets: 6929 to 6960 Dba:0x01002034
------------------------------------------------------------------------
494c4524 06535953 54454dff ff055641 4c494402 c10b02c1 2902c102 03c20338
<32 bytes per line>
BBED> p *kdbr[7]
rowdata[209]
------------
ub1 rowdata[209] @6506 0x2c
--6506肯定不是在这个位置
BBED> p *kdbr[5]
rowdata[623]
------------
ub1 rowdata[623] @6920 0x2c
--6920包含了6929,可以确定在该位置
--查看对应值
BBED> x /rccc
rowdata[623] @6920
------------
flag@6920: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@6921: 0x00
cols@6922: 49
col 0[3] @6923: SYS
col 1[5] @6927: FILE$
col 2[6] @6933: SYSTEM
col 3[0] @6940: *NULL*
col 4[0] @6941: *NULL*
col 5[5] @6942: VALID
col 6[2] @6948: ..
col 7[2] @6951: .)
col 8[2] @6954: ..
col 9[3] @6957: ..8
col 10[4] @6961: ..8%
col 11[0] @6966: *NULL*
col 12[2] @6967: ..
col 13[6] @6970: ..01%.
col 14[0] @6977: *NULL*
col 15[2] @6978: ..
col 16[2] @6981: ..
col 17[3] @6984: YES
col 18[1] @6988: N
col 19[2] @6990: ..
col 20[2] @6993: ..
col 21[1] @6996: .
col 22[1] @6998: .
col 23[1] @7000: .
col 24[2] @7002: .(
col 25[1] @7005: .
col 26[1] @7007: .
col 27[10] @7009: 1
col 28[10] @7020: 1
col 29[5] @7031: N
col 30[7] @7037: ENABLED
col 31[2] @7045: ..
col 32[7] @7048: xp....!
col 33[2] @7056: NO
col 34[0] @7059: *NULL*
col 35[1] @7060: N
col 36[1] @7062: N
col 37[2] @7064: NO
col 38[7] @7067: DEFAULT
col 39[8] @7075: DISABLED
col 40[3] @7084: YES
col 41[2] @7088: NO
col 42[0] @7091: *NULL*
col 43[8] @7092: DISABLED
col 44[3] @7101: YES
col 45[0] @7105: *NULL*
col 46[8] @7106: DISABLED
col 47[8] @7115: DISABLED
col 48[2] @7124: NO
BBED> d
File: /u01/oradata/orcl/users01.dbf (4)
Block: 8244 Offsets: 6920 to 6951 Dba:0x01002034
------------------------------------------------------------------------
2c003103 53595305 46494c45 24065359 5354454d ffff0556 414c4944 02c10b02
<32 bytes per line>
BBED> set mode edit
MODE Edit
--修改为delete状态
BBED> m /x 3c
File: /u01/oradata/orcl/users01.dbf (4)
Block: 8244 Offsets: 6920 to 6951 Dba:0x01002034
------------------------------------------------------------------------
3c003103 53595305 46494c45 24065359 5354454d ffff0556 414c4944 02c10b02
<32 bytes per line>
BBED> sum apply
Check value for File 4, Block 8244:
current = 0xa274, required = 0xa274
--验证不通过,因为空闲空间不正确(删除了数据还是以前的值当然不正确)
BBED> verify
DBVERIFY - Verification starting
FILE = /u01/oradata/orcl/users01.dbf
BLOCK = 8244
Block Checking: DBA = 16785460, Block Type = KTB-managed data block
data header at 0x7f0a75d0327c
kdbchk: the amount of space used is not equal to block size
used=1722 fsc=0 avsp=6137 dtl=8064
Block 8244 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
BBED> p kdbh
struct kdbh, 14 bytes @124
ub1 kdbhflag @124 0x00 (NONE)
b1 kdbhntab @125 1
b2 kdbhnrow @126 9
sb2 kdbhfrre @128 -1
sb2 kdbhfsbo @130 36
sb2 kdbhfseo @132 6173
b2 kdbhavsp @134 6137
b2 kdbhtosp @136 6137
BBED> m /x c618 offset 134
File: /u01/oradata/orcl/users01.dbf (4)
Block: 8244 Offsets: 134 to 165 Dba:0x01002034
------------------------------------------------------------------------
c618f917 00000900 b01ee11d 0a1d311c 5b1b8c1a be19ee18 1d180000 00000000
<32 bytes per line>
BBED> m /x c618 offset 136
File: /u01/oradata/orcl/users01.dbf (4)
Block: 8244 Offsets: 136 to 167 Dba:0x01002034
------------------------------------------------------------------------
c6180000 0900b01e e11d0a1d 311c5b1b 8c1abe19 ee181d18 00000000 00000000
<32 bytes per line>
BBED> sum apply
Check value for File 4, Block 8244:
current = 0xa274, required = 0xa274
BBED> verify
DBVERIFY - Verification starting
FILE = /u01/oradata/orcl/users01.dbf
BLOCK = 8244
Block Checking: DBA = 16785460, Block Type = KTB-managed data block
data header at 0x13ef07c
kdbchk: space available on commit is incorrect
tosp=6342 fsc=0 stb=2 avsp=6342
Block 8244 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
BBED> m /x c8 offset 136
File: /u01/oradata/orcl/users01.dbf (4)
Block: 8244 Offsets: 136 to 167 Dba:0x01002034
------------------------------------------------------------------------
c8180000 0900b01e e11d0a1d 311c5b1b 8c1abe19 ee181d18 00000000 00000000
<32 bytes per line>
BBED> p kdbh
struct kdbh, 14 bytes @124
ub1 kdbhflag @124 0x00 (NONE)
b1 kdbhntab @125 1
b2 kdbhnrow @126 9
sb2 kdbhfrre @128 -1
sb2 kdbhfsbo @130 36
sb2 kdbhfseo @132 6173
b2 kdbhavsp @134 6342
b2 kdbhtosp @136 6344
BBED> sum apply
Check value for File 4, Block 8244:
current = 0xa27a, required = 0xa27a
BBED> verify
DBVERIFY - Verification starting
FILE = /u01/oradata/orcl/users01.dbf
BLOCK = 8244
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
启动数据库测试
SQL> startup ORACLE instance started. Total System Global Area 167772160 bytes Fixed Size 2019320 bytes Variable Size 75497480 bytes Database Buffers 88080384 bytes Redo Buffers 2174976 bytes Database mounted. Database opened. SQL> set lines 150 SQL> select table_name,owner,rowid, 2 dbms_rowid.rowid_relative_fno(rowid)rel_fno, 3 dbms_rowid.rowid_block_number(rowid)blockno, 4 dbms_rowid.rowid_row_number(rowid) rowno 5 from t_xifenfei; TABLE_NAME OWNER ROWID REL_FNO BLOCKNO ROWNO ------------------------------ ------------------------------ ------------------ ---------- ---------- ---------- CON$ SYS AAAM9UAAEAAACA0AAA 4 8244 0 UNDO$ SYS AAAM9UAAEAAACA0AAB 4 8244 1 CDEF$ SYS AAAM9UAAEAAACA0AAC 4 8244 2 CCOL$ SYS AAAM9UAAEAAACA0AAD 4 8244 3 PROXY_ROLE_DATA$ SYS AAAM9UAAEAAACA0AAE 4 8244 4 FET$ SYS AAAM9UAAEAAACA0AAG 4 8244 6 TS$ SYS AAAM9UAAEAAACA0AAH 4 8244 7 PROXY_DATA$ SYS AAAM9UAAEAAACA0AAI 4 8244 8 8 rows selected.
可以看到file$这条记录已经被删除,证明bbed操作普通表删除成功
相关文章:
1.bbed 删除 cluster table 记录
2.bbed 找回被删除数据
3.利用bbed找回ORACLE更新前值
请教飞总两个问题：
BBED> p *kdbr[7]
rowdata[209]
————
ub1 rowdata[209] @6506 0x2c
–6506肯定不是在这个位置 —-请问是如何判断的呢？
BBED> p *kdbr[5]
rowdata[623]
————
ub1 rowdata[623] @6920 0x2c
–6920包含了6929,可以确定在该位置 —为什么说6920包含了6929？
如果我想删除CDEF$这一列
SQL> select dump(‘CDEF$’,16) from dual;
DUMP(‘CDEF$’,16)
——————————————————————————–
Typ=96 Len=5: 43,44,45,46,24
BBED> f /x 4344454624 –这里会报错。 应该怎么写呢？
BBED-00209: invalid number (4344454624)
BBED> m /x c618 offset 134
这里的 c618 是值什么呢？
BBED> m /x c8 offset 136 这句如何理解呢？