在oracle中，當資料行被刪除時，實際上並未真正的刪除。這一行僅僅是被標記為刪除,並且可利用空間計數器和指標會相應的調整。

行的狀態資訊儲存在佔用每一行的前幾個位元組的Row Header。

Row Header 包含：

1 Row Flag 判斷這行是不是行首，第一列，最後一列在不在其中，是否有行遷移和行連結。是一個標誌位。

2 Lock Byte(ITL entry)和列數。

3 column count

Row Flag 是一個單byte的標誌掩碼：標識了row的狀態。標誌掩碼的譯碼如下：

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 被標記為--H-FL--

tab 0, row 2, @0x1f04

tl: 43 fb: --H-FL-- 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

當一行資料被刪除了之後，Row Flag 會被更新，標識delete的16位會被置位，也就是說Row Flag的值為32+16+8+4=60或者ox3c。

下面我們刪除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.

刪除表中的第三行

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.

--在bbed 中進行修改。

在bbed 中定位資料塊 396號並查詢含有'yang'的行。

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>

顯示行資訊

BBED> dump /v dba 4,396 offset 8072 count 64

File: /opt/oracle/oradata/orcl/users01.dbf (4)

Block: 396 Offsets: 8072 to 8135 Dba:0x0100018c

-------------------------------------------------------

79616e67 0853414c 45534d41 4e03c24d l yang.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>

猜測性前移，猜測行頭偏移量在8064處。--此步驟可以省略，直接使用 p *kdbr[]

BBED> dump /v dba 4,396 offset 8060

File: /opt/oracle/oradata/orcl/users01.dbf (4)

Block: 396 Offsets: 8060 to 8123 Dba:0x0100018c

-------------------------------------------------------

ff02c115 2c020803 c24c1604 79616e67 l ....,....L..yang

0853414c 45534d41 4e03c24d 630777b5 l .SALESMAN..Mc.w.

02160101 0103c20d 3302c206 02c11f2c l ........3......,

000803c2 4b640541 4c4c454e 0853414c l ....Kd.ALLEN.SAL

<16 bytes per line>

使用p 確認第三行開始的offset的位置，偏移量為8064。

BBED> p *kdbr[2]

rowdata[443]

------------

ub1 rowdata[443] @8064 0x2c

--

BBED> dump /v dba 4,396 offset 8064 count 64

File: /opt/oracle/oradata/orcl/users01.dbf (4)

Block: 396 Offsets: 8064 to 8127 Dba:0x0100018c

-------------------------------------------------------

2c000803 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

<16 bytes per line>

注意;2c就說Row Flag =44 即未刪除的行。

在資料庫中刪除行操作之後，檢視第三行的資訊，資訊依然存在，但是開頭的2c已經改為了3c

BBED> dump /v dba 4,396 offset 8064 count 64

File: /opt/oracle/oradata/orcl/users01.dbf (4)

Block: 396 Offsets: 8064 to 8127 Dba:0x0100018c

-------------------------------------------------------

3c020803 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

修改塊頭的資訊修改3c為2c。

BBED> modify /x 2c offset 8064

File: /opt/oracle/oradata/orcl/users01.dbf (4)

Block: 396 Offsets: 8064 to 8127 Dba:0x0100018c

------------------------------------------------------------------------

2c020803 c24c1604 79616e67 0853414c 45534d41 4e03c24d 630777b5 02160101

0103c20d 3302c206 02c11f2c 000803c2 4b640541 4c4c454e 0853414c 45534d41

<32 bytes per line>

應用新的校驗和資訊

BBED> sum dba 4,396

Check value for File 4, Block 396:

current = 0x2baa, required = 0x2bba

BBED> sum dba 4,396 apply

Check value for File 4, Block 396:

current = 0x2bba, required = 0x2bba

BBED>

進入sqlplus 查詢驗證。

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.

資料行已被恢復。。

SQL> select * from yangobj where empno=7521;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

---------- --------------- ---------- ---------- ------------------ ---------- ---------- ----------

7521 yang SALESMAN 7698 22-FEB-81 1250 500 30

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 2: [ 8] 53 41 4c 45 53 4d 41 4e

col 3: [ 3] c2 4d 63

col 4: [ 7] 77 b5 02 14 01 01 01

col 5: [ 2] c2 11

col 6: [ 2] c2 04

col 7: [ 2] c1 1f

tab 0, row 2, @0x1f04

tl: 43 fb: --H-FL-- 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

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

col 6: *NULL*

col 7: [ 2] c1 15

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 2: [ 8] 53 41 4c 45 53 4d 41 4e

col 3: [ 3] c2 4d 63

col 4: [ 7] 77 b5 09 1c 01 01 01

col 5: [ 3] c2 0d 33

col 6: [ 2] c2 0f

col 7: [ 2] c1 1f

tab 0, row 5, @0x1e85

tl: 41 fb: --H-FL-- lb: 0x0 cc: 8

col 0: [ 3] c2 4d 63

col 1: [ 5] 42 4c 41 4b 45

col 2: [ 7] 4d 41 4e 41 47 45 52

col 3: [ 3] c2 4f 28

col 4: [ 7] 77 b5 05 01 01 01 01

col 5: [ 3] c2 1d 33

col 6: *NULL*

col 7: [ 2] c1 1f

tab 0, row 6, @0x1e5c

tl: 41 fb: --H-FL-- lb: 0x0 cc: 8

col 0: [ 3] c2 4e 53

col 1: [ 5] 43 4c 41 52 4b

col 2: [ 7] 4d 41 4e 41 47 45 52

col 3: [ 3] c2 4f 28

col 4: [ 7] 77 b5 06 09 01 01 01

col 5: [ 3] c2 19 33

col 6: *NULL*

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

col 6: *NULL*

col 7: [ 2] c1 15

tab 0, row 8, @0x1e0e

tl: 38 fb: --H-FL-- lb: 0x0 cc: 8

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

col 6: *NULL*

col 7: [ 2] c1 0b

tab 0, row 9, @0x1de3

tl: 43 fb: --H-FL-- lb: 0x0 cc: 8

col 0: [ 3] c2 4f 2d

col 1: [ 6] 54 55 52 4e 45 52

col 2: [ 8] 53 41 4c 45 53 4d 41 4e

col 3: [ 3] c2 4d 63

col 4: [ 7] 77 b5 09 08 01 01 01

col 5: [ 2] c2 10

col 6: [ 1] 80

col 7: [ 2] c1 1f

tab 0, row 10, @0x1dbd

tl: 38 fb: --H-FL-- lb: 0x0 cc: 8

col 0: [ 3] c2 4f 4d

col 1: [ 5] 41 44 41 4d 53

col 2: [ 5] 43 4c 45 52 4b

col 3: [ 3] c2 4e 59

col 4: [ 7] 77 bb 05 17 01 01 01

col 5: [ 2] c2 0c

col 6: *NULL*

col 7: [ 2] c1 15

tab 0, row 11, @0x1d97

tl: 38 fb: --H-FL-- lb: 0x0 cc: 8

col 0: [ 2] c2 50

col 1: [ 5] 4a 41 4d 45 53

col 2: [ 5] 43 4c 45 52 4b

col 3: [ 3] c2 4d 63

col 4: [ 7] 77 b5 0c 03 01 01 01

col 5: [ 3] c2 0a 33

col 6: *NULL*

col 7: [ 2] c1 1f

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

col 2: [ 7] 41 4e 41 4c 59 53 54

col 3: [ 3] c2 4c 43

col 4: [ 7] 77 b5 0c 03 01 01 01

col 5: [ 2] c2 1f

col 6: *NULL*

col 7: [ 2] c1 15

tab 0, row 13, @0x1d49

tl: 39 fb: --H-FL-- lb: 0x0 cc: 8

col 0: [ 3] c2 50 23

col 1: [ 6] 4d 49 4c 4c 45 52

col 2: [ 5] 43 4c 45 52 4b

col 3: [ 3] c2 4e 53

col 4: [ 7] 77 b6 01 17 01 01 01

col 5: [ 2] c2 0e

col 6: *NULL*

col 7: [ 2] c1 0b

end_of_block_dump

【BBED】使用bbed恢復已經刪除的行資料

相關文章