联系:手机/微信(+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> m /x c8 offset 136 这句如何理解呢?
BBED> m /x c618 offset 134
这里的 c618 是值什么呢?
如果我想删除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> 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?