在oracle中,當資料行被删除時,實際上并未真正的删除。這一行僅僅是被标記為删除,并且可利用空間計數器和指針會相應的調整。
行的狀态資訊存儲在占用每一行的前幾個位元組的Row Header。
Row Header 包含:
<b>1 Row Flag 判斷這行是不是行首,第一列,最後一列在不在其中,是否有行遷移和行連結。是一個标志位。</b>
2 Lock Byte(ITL entry)和列數。
3 column count
<b>Row Flag 是一個單byte的标志掩碼:辨別了row的狀态。标志掩碼的譯碼如下:</b>
Cluster Cluster Head of Delete First Last 1st column continnues Last column continues
key Table Member row pice data piece data piece from previous piece in next piece
128 64 32 16 8 4 2 1
是以所有在存儲在一個單塊中的所有列,既沒有沒有發生行連結或者行遷移有不是clustered table而且有沒有被删除的行都具有以下特性:
Head of Row Piece
First Data Piece
Last Data Piece
是以此行的row flag的值為 32+8+4=44或者ox2c
使用alter system dump file 4 block 396 檢視跟蹤檔案中資料塊的dump 内容,row flag 被标記為<b>--H-FL--</b>
tab 0, row 2, @0x1f04
tl: 43 fb: <b>--H-FL--</b> lb: 0x0 cc: 8
col 0: [ 3] c2 4c 16
col 1: [ 4] 57 41 52 44
col 2: [ 8] 53 41 4c 45 53 4d 41 4e
col 3: [ 3] c2 4d 63
col 4: [ 7] 77 b5 02 16 01 01 01
col 5: [ 3] c2 0d 33
col 6: [ 2] c2 06
col 7: [ 2] c1 1f
<b>當一行資料被删除了之後,Row Flag 會被更新,辨別delete的16位會被置位,也就是說Row Flag的值為32+16+8+4=60或者ox3c。</b>
下面我們删除ename為 yang列。并使用bbed工具修複已删除的行。
SQL> select * from yangobj;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- --------------- ---------- ---------- ------------------ ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 yang SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
14 rows selected.
<b>删除表中的第三行 </b>
SQL> delete yangobj where empno=7521;
1 row deleted.
SQL> commit;
Commit complete.
SQL> select * from yangobj where empno=7521;
no rows selected
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
<b>--在bbed 中進行修改。</b>
<b>在bbed 中定位資料塊 396号并查找含有'yang'的行。</b>
BBED> set dba 4 ,396
DBA 0x0100018c (16777612 4,396)
BBED> find /c yang TOP
File: /opt/oracle/oradata/orcl/users01.dbf (4)
Block: 396 Offsets: 8072 to 8135 Dba:0x0100018c
------------------------------------------------------------------------
79616e67 0853414c 45534d41 4e03c24d 630777b5 02160101 0103c20d 3302c206
02c11f2c 000803c2 4b640541 4c4c454e 0853414c 45534d41 4e03c24d 630777b5
<32 bytes per line>
<b>顯示行資訊</b>
BBED> dump /v dba 4,396 offset 8072 count 64
Block: 396 Offsets: 8072 to 8135 Dba:0x0100018c
-------------------------------------------------------
79616e67 0853414c 45534d41 4e03c24d l<b> yang</b>.SALESMAN..M
630777b5 02160101 0103c20d 3302c206 l c.w.........3...
02c11f2c 000803c2 4b640541 4c4c454e l ...,....Kd.ALLEN
0853414c 45534d41 4e03c24d 630777b5 l .SALESMAN..Mc.w.
<16 bytes per line>
<b>猜測性前移,猜測行頭偏移量在8064處。--此步驟可以省略,直接使用 p *kdbr[]</b>
BBED> dump /v dba 4,396 offset 8060
Block: 396 Offsets: 8060 to 8123 Dba:0x0100018c
ff02c115 2c020803 c24c1604 79616e67 l ....,....L..yang
02160101 0103c20d 3302c206 02c11f2c l ........3......,
000803c2 4b640541 4c4c454e 0853414c l ....Kd.ALLEN.SAL
<b>使用p 确認第三行開始的offset的位置,偏移量為8064。</b>
BBED> p *kdbr[2]
rowdata[443]
------------
ub1 rowdata[443] @8064 0x2c
--
BBED> dump /v dba 4,396 offset 8064 count 64
Block: 396 Offsets: 8064 to 8127 Dba:0x0100018c
<b>2c</b>000803 c24c1604 79616e67 0853414c l ,....L..yang.SAL
45534d41 4e03c24d 630777b5 02160101 l ESMAN..Mc.w.....
0103c20d 3302c206 02c11f2c 000803c2 l ....3......,....
4b640541 4c4c454e 0853414c 45534d41 l Kd.ALLEN.SALESMA
<b>注意;2c就說Row Flag =44 即未删除的行。</b>
<b>在資料庫中删除行操作之後,檢視第三行的資訊,資訊依然存在,但是開頭的2c已經改為了3c</b>
<b></b>
<b> 3c</b>020803 c24c1604 79616e67 0853414c l <....L..yang.SAL
<b>修改塊頭的資訊修改3c為2c。</b>
BBED> modify /x 2c offset 8064
Block: 396 Offsets: 8064 to 8127 Dba:0x0100018c
<b>2c</b>020803 c24c1604 79616e67 0853414c 45534d41 4e03c24d 630777b5 02160101
0103c20d 3302c206 02c11f2c 000803c2 4b640541 4c4c454e 0853414c 45534d41
<b>應用新的校驗和資訊</b>
BBED> sum dba 4,396
Check value for File 4, Block 396:
current = 0x2baa, required = 0x2bba
BBED> sum dba 4,396 apply
current = 0x2bba, required = 0x2bba
BBED>
<b>進入sqlplus 查詢驗證。</b>
SQL> startup
ORACLE instance started.
Total System Global Area 1224736768 bytes
Fixed Size 2020384 bytes
Variable Size 318770144 bytes
Database Buffers 889192448 bytes
Redo Buffers 14753792 bytes
Database mounted.
Database opened.
<b>資料行已被恢複。。</b>
SQL>
附上資料塊( 4,396)的dump檔案内容:
block_row_dump:
tab 0, row 0, @0x1f5a
tl: 38 fb: --H-FL-- lb: 0x0 cc: 8
col 0: [ 3] c2 4a 46
col 1: [ 5] 53 4d 49 54 48
col 2: [ 5] 43 4c 45 52 4b
col 3: [ 3] c2 50 03
col 4: [ 7] 77 b4 0c 11 01 01 01
col 5: [ 2] c2 09
col 6: *NULL*
col 7: [ 2] c1 15
tab 0, row 1, @0x1f2f
tl: 43 fb: --H-FL-- lb: 0x0 cc: 8
col 0: [ 3] c2 4b 64
col 1: [ 5] 79 61 6e 67 4e
col 4: [ 7] 77 b5 02 14 01 01 01
col 5: [ 2] c2 11
col 6: [ 2] c2 04
tab 0, row 3, @0x1edb
tl: 41 fb: --H-FL-- lb: 0x0 cc: 8
col 0: [ 3] c2 4c 43
col 1: [ 5] 4a 4f 4e 45 53
col 2: [ 7] 4d 41 4e 41 47 45 52
col 3: [ 3] c2 4f 28
col 4: [ 7] 77 b5 04 02 01 01 01
col 5: [ 3] c2 1e 4c
tab 0, row 4, @0x1eae
tl: 45 fb: --H-FL-- lb: 0x0 cc: 8
col 0: [ 3] c2 4d 37
col 1: [ 6] 4d 41 52 54 49 4e
col 4: [ 7] 77 b5 09 1c 01 01 01
col 6: [ 2] c2 0f
tab 0, row 5, @0x1e85
col 0: [ 3] c2 4d 63
col 1: [ 5] 42 4c 41 4b 45
col 4: [ 7] 77 b5 05 01 01 01 01
col 5: [ 3] c2 1d 33
tab 0, row 6, @0x1e5c
col 0: [ 3] c2 4e 53
col 1: [ 5] 43 4c 41 52 4b
col 4: [ 7] 77 b5 06 09 01 01 01
col 5: [ 3] c2 19 33
col 7: [ 2] c1 0b
tab 0, row 7, @0x1e34
tl: 40 fb: --H-FL-- lb: 0x0 cc: 8
col 0: [ 3] c2 4e 59
col 1: [ 5] 53 43 4f 54 54
col 2: [ 7] 41 4e 41 4c 59 53 54
col 3: [ 3] c2 4c 43
col 4: [ 7] 77 bb 04 13 01 01 01
col 5: [ 2] c2 1f
tab 0, row 8, @0x1e0e
col 0: [ 3] c2 4f 28
col 1: [ 4] 4b 49 4e 47
col 2: [ 9] 50 52 45 53 49 44 45 4e 54
col 3: *NULL*
col 4: [ 7] 77 b5 0b 11 01 01 01
col 5: [ 2] c2 33
tab 0, row 9, @0x1de3
col 0: [ 3] c2 4f 2d
col 1: [ 6] 54 55 52 4e 45 52
col 4: [ 7] 77 b5 09 08 01 01 01
col 5: [ 2] c2 10
col 6: [ 1] 80
tab 0, row 10, @0x1dbd
col 0: [ 3] c2 4f 4d
col 1: [ 5] 41 44 41 4d 53
col 3: [ 3] c2 4e 59
col 4: [ 7] 77 bb 05 17 01 01 01
col 5: [ 2] c2 0c
tab 0, row 11, @0x1d97
col 0: [ 2] c2 50
col 1: [ 5] 4a 41 4d 45 53
col 4: [ 7] 77 b5 0c 03 01 01 01
col 5: [ 3] c2 0a 33
tab 0, row 12, @0x1d70
tl: 39 fb: --H-FL-- lb: 0x0 cc: 8
col 0: [ 3] c2 50 03
col 1: [ 4] 46 4f 52 44
tab 0, row 13, @0x1d49
col 0: [ 3] c2 50 23
col 1: [ 6] 4d 49 4c 4c 45 52
col 3: [ 3] c2 4e 53
col 4: [ 7] 77 b6 01 17 01 01 01
col 5: [ 2] c2 0e
end_of_block_dump