在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
显示行信息
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.
猜测性前移,猜测行头偏移量在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
使用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
注意;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
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
应用新的校验和信息
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