Oracle delete删除数据是否为逻辑删除、新插入数据占用的数据块位置实验

假设一：数据库delete删除为直接删除

假设二：数据库delete删除为逻辑删除，在数据块标记出来，但是实际并没有删除。

方式一：通过dump数据块的方式来实现

我们先用小数据量，通过dump数据块的方式来实现

-- 数据库open状态下
-- 查看数据文件位置
select name from v$datafile ;

-- 创建表空间和测试表：
create tablespace PRODblock datafile '/u01/app/oracle/oradata/PROD/tes01.dbf' size 20M;

create table PRODblock(
  id number,
  name varchar(4)
) tablespace PRODblock;

-- 插入 3 条数据然后提交：
insert into PRODblock values(1,'a');
insert into PRODblock values(2,'b');
insert into PRODblock values(3,'c');
commit;
-- 查询表数据
select * from PRODblock;

-- 查看数据块是在文件中的第几个数据块
select rowid,dbms_rowid.rowid_relative_fno(rowid) 
rel_fno,dbms_rowid.rowid_block_number(rowid) blockno from PRODblock;

--跟踪本会话

oradebug setmypid

--查看跟踪文件路径

oradebug tracefile_name

-- 将数据对应的文件下的数据块dump到trace文件中

alter system dump datafile 22 block 135;

查看trace文件：

Start dump data blocks tsn: 8 file#:22 minblk 135 maxblk 135

Block dump from cache:

Dump of buffer cache at level 3 for pdb=1 tsn=8 rdba=92274823

BH (0x72ff3c18) file#: 22 rdba: 0x05800087 (22/135) class: 1 ba: 0x72f02000

set: 10 pool: 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 0,0

dbwrid: 0 obj: 73882 objn: 73882 tsn: [1/8] afn: 22 hint: f

hash: [0x65218d00,0x65218d00] lru: [0x72ff3e68,0x72ff3b98]

obj-flags: object_ckpt_list

ckptq: [0x72bd6500,0x72ff3d80] fileq: [0x653bd780,0x72ff3d90]

objq: [0x72ff3e90,0x6d80e978] objaq: [0x72ff3ea0,0x6d80e958]

st: XCURRENT md: NULL fpin: 'ktspbwh2: ktspfmdb' fscn: 0x322f89 tch: 3

flags: buffer_dirty

LRBA: [0x28.1385.0] LSCN: [0x322f89] HSCN: [0x322f89] HSUB: [1]

Printing buffer operation history (latest change first):

cnt: 9

01. sid:11 L464:chg1_mn:bic:FMS 02. sid:11 L778:chg1_mn:bis:FMS

03. sid:11 L353:gcur:set:MEXCL 04. sid:11 L464:chg1_mn:bic:FMS

05. sid:11 L778:chg1_mn:bis:FMS 06. sid:11 L353:gcur:set:MEXCL

07. sid:11 L464:chg1_mn:bic:FMS 08. sid:11 L778:chg1_mn:bis:FMS

09. sid:11 L353:gcur:set:MEXCL 10. sid:11 L464:chg1_mn:bic:FMS

11. sid:11 L614:chg1_mn:bis:FBD 12. sid:11 L922:klbc:sw:cq

13. sid:11 L778:chg1_mn:bis:FMS 14. sid:11 L004:new:acq:pin

15. sid:11 L229:bnew:bic:FEN 16. sid:11 L143:new:mk:EXCL

buffer tsn: 8 rdba: 0x05800087 (22/135)

scn: 0x322f9e seq: 0x01 flg: 0x02 tail: 0x2f9e0601

frmt: 0x02 chkval: 0x0000 type: 0x06=trans data

Hex dump of block: st=0, typ_found=1

Dump of memory from 0x0000000072F02000 to 0x0000000072F04000

072F02000 0000A206 05800087 00322F9E 02010000 [........./2.....]

072F02010 00000000 00000001 0001209A 00322F89 [......... .../2.]

072F02020 00008000 00320002 05800080 00030001 [......2.........]

072F02030 000004B4 0100156E 00230133 00002003 [....n...3.#.. ..]

072F02040 00322F9E 00000000 00000000 00000000 [./2.............]

072F02050 00000000 00000000 00000000 00000000 [................]

072F02060 00000000 00030100 0018FFFF 1F651F80 [..............e.]

072F02070 00001F65 1F900003 1F801F88 00000000 [e...............]

072F02080 00000000 00000000 00000000 00000000 [................]

Repeat 501 times

072F03FE0 00000000 0202012C 630104C1 0202012C [....,......c,...]

072F03FF0 620103C1 0202012C 610102C1 2F9E0601 [...b,......a.../]

Block header dump: 0x05800087

Object id on Block? Y

seg/obj: 0x1209a csc: 0x0000000000322f89 itc: 2 flg: E typ: 1 - DATA

brn: 0 bdba: 0x5800080 ver: 0x01 opc: 0

inc: 0 exflg: 0

Itl Xid Uba Flag Lck Scn/Fsc

0x01 0x0001.003.000004b4 0x0100156e.0133.23 --U- 3 fsc 0x0000.00322f9e

0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000

bdba: 0x05800087

data_block_dump,data header at 0x72f02064

===============

tsiz: 0x1f98

hsiz: 0x18

pbl: 0x72f02064

76543210

flag=--------

ntab=1

nrow=3

frre=-1

fsbo=0x18

fseo=0x1f80

avsp=0x1f65

tosp=0x1f65

0xe:pti[0] nrow=3 offs=0

0x12:pri[0] offs=0x1f90

0x14:pri[1] offs=0x1f88

0x16:pri[2] offs=0x1f80

block_row_dump:

tab 0, row 0, @0x1f90

tl: 8 fb: --H-FL-- lb: 0x1 cc: 2

col 0: [ 2] c1 02

col 1: [ 1] 61

tab 0, row 1, @0x1f88

tl: 8 fb: --H-FL-- lb: 0x1 cc: 2

col 0: [ 2] c1 03

col 1: [ 1] 62

tab 0, row 2, @0x1f80

tl: 8 fb: --H-FL-- lb: 0x1 cc: 2

col 0: [ 2] c1 04

col 1: [ 1] 63

end_of_block_dump

Block dump from disk:

buffer tsn: 8 rdba: 0x00000087 (0/135)

scn: 0x0 seq: 0x01 flg: 0x05 tail: 0x00000001

frmt: 0x02 chkval: 0xa787 type: 0x00=unknown

Hex dump of initial block header 1 = INITIAL

Dump of memory from 0x00007F22489C1000 to 0x00007F22489C1014

7F22489C1000 0000A200 00000087 00000000 05010000 [................]

7F22489C1010 0000A787 [....]

End dump data blocks tsn: 8 file#: 22 minblk 135 maxblk 135

大概分成四部分：头信息区、数据区空闲区、事物列表区、尾区

尾区记录了用户数据头和用户数据

看尾部数据

tab 0, row 0, @0x1f90

tl: 8 fb: --H-FL-- lb: 0x1 cc: 2

col 0: [ 2] c1 02

col 1: [ 1] 61

tab 0, row 1, @0x1f88

tl: 8 fb: --H-FL-- lb: 0x1 cc: 2

col 0: [ 2] c1 03

col 1: [ 1] 62

tab 0, row 2, @0x1f80

tl: 8 fb: --H-FL-- lb: 0x1 cc: 2

col 0: [ 2] c1 04

col 1: [ 1] 63

end_of_block_dump

我们来查看这些值把表中的数据转成 16 进制（即在 ORACLE 内部数据块看到的数据）：

SQL> select id,name,dump(1,'16'), dump('a','16') from PRODblock;

select id,name,dump(1,'16'), dump('a','16') from PRODblock;

和第一个数据一样

tl: 8 fb: --H-FL-- lb: 0x1 cc: 2

col 0: [ 2] c1 02

col 1: [ 1] 61

tab 0, row 1, @0x1f88

同理第二个第三个

select id,name,dump(1,'16'), dump('b','16') from PRODblock;

select id,name,dump(1,'16'), dump('c','16') from PRODblock;

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

我们也可以利用 UTL_RAW 包中的 CAST_TO_NUMBER 函数将字符串 ' c1 02 ' 转换为数字

select id,

UTL_RAW.CAST_TO_NUMBER(replace(' c1 02 ', ' ')) id1,

name,

UTL_RAW.CAST_TO_VARCHAR2(replace('61', ' ')) name1

from PRODblock;

ID为1，name字段是a，和我们上面插入的一样

ps：

UTL_RAW.CAST_TO_NUMBER(REPLACE(' c1 02 ', ' '))：这部分使用 UTL_RAW 包中的 CAST_TO_NUMBER 函数将字符串 ' c1 02 ' 转换为数字。在进行转换之前，使用 REPLACE 函数将字符串中的空格去除，以防止转换失败。
UTL_RAW.CAST_TO_VARCHAR2(REPLACE('61', ' '))：这部分使用 UTL_RAW 包中的 CAST_TO_VARCHAR2 函数将字符串 '61' 转换为 VARCHAR2 类型。同样，使用 REPLACE 函数将字符串中的空格去除，以防止转换失败。

UTL_RAW 包是 Oracle 数据库中的一个内置包，用于处理原始（raw）数据类型。以下是 UTL_RAW 包中常用的一些函数：

CAST_FROM_BINARY_INTEGER：将二进制整数转换为 RAW 格式。
CAST_TO_BINARY_INTEGER：将 RAW 格式转换为二进制整数。
CAST_FROM_NUMBER：将数字转换为 RAW 格式。
CAST_TO_NUMBER：将 RAW 格式转换为数字。
CAST_FROM_VARCHAR2：将 VARCHAR2 格式转换为 RAW 格式。
CAST_TO_VARCHAR2：将 RAW 格式转换为 VARCHAR2 格式。
CONCAT：连接两个 RAW 数据块。
LENGTH：返回 RAW 数据的长度。

接下来，我们删除一个字段，来看看记录是否会消失，这里就不做解释了，上SQL

oradebug setmypid

oradebug tracefile_name

delete from system.PRODblock where ID=3;

Commit;

-- 查看数据块是在文件中的第几个数据块

select rowid,dbms_rowid.rowid_relative_fno(rowid)

rel_fno,dbms_rowid.rowid_block_number(rowid) blockno from PRODblock;

alter system dump datafile 22 block 135;

insert into PRODblock values(4,'d');

insert into PRODblock values(5,'e');

insert into PRODblock values(6,'f');

commit;

ps：这里插入数据，是为了验证是否会占用被删除的数据块

select * from PRODblock;

来看跟踪文件

ps：退出会话重新开一个session可以得到一个新的trace文件，他跟踪的其实是一个会话进程

*** 2024-04-23T14:45:50.426886+08:00 (CDB$ROOT(1))

Start dump data blocks tsn: 8 file#:22 minblk 135 maxblk 135

Block dump from cache:

Dump of buffer cache at level 3 for pdb=1 tsn=8 rdba=92274823

BH (0x707f8f10) file#: 22 rdba: 0x05800087 (22/135) class: 1 ba: 0x70778000

set: 10 pool: 3 bsz: 8192 bsi: 0 sflg: 0 pwc: 0,0

dbwrid: 0 obj: 73882 objn: 73882 tsn: [1/8] afn: 22 hint: f

hash: [0x72ff3cc8,0x65218d00] lru: [0x707f9160,0x707f8e90]

obj-flags: object_ckpt_list

ckptq: [0x707fa428,0x70fde0c0] fileq: [0x653bd780,0x653bd780]

objq: [0x6d80e978,0x6d80e978] objaq: [0x72ff3ea0,0x6d80e958]

st: XCURRENT md: NULL fpin: 'ktspbwh2: ktspfmdb' fscn: 0x322f89 tch: 6

flags: buffer_dirty block_written_once

LRBA: [0x28.13392.0] LSCN: [0x32e698] HSCN: [0x32e698] HSUB: [1]

Printing buffer operation history (latest change first):

cnt: 11

01. sid:12 L065:cmt_main:clr:flg 02. sid:12 L922:klbc:sw:cq

03. sid:12 L616:b_cmt_mn:bis:FBD 04. sid:12 L781:cmt_mn:bis:FMS

05. sid:11 L786:pre_apl:bis:FMS 06. sid:11 L353:gcur:set:MEXCL

07. sid:11 L786:pre_apl:bis:FMS 08. sid:11 L353:gcur:set:MEXCL

09. sid:11 L786:pre_apl:bis:FMS 10. sid:11 L803:pre_apl:bis:FPB

11. sid:11 L353:gcur:set:MEXCL 12. sid:01 L192:kcbbic2:bic:FBD

13. sid:01 L191:kcbbic2:bic:FBW 14. sid:01 L602:bic1_int:bis:FWC

15. sid:01 L822:bic1_int:ent:rtn 16. sid:01 L832:oswmqbg1:clr:WRT

buffer tsn: 8 rdba: 0x05800087 (22/135)

scn: 0x32e698 seq: 0x04 flg: 0x02 tail: 0xe6980604

frmt: 0x02 chkval: 0x0000 type: 0x06=trans data

Hex dump of block: st=0, typ_found=1

Dump of memory from 0x0000000070778000 to 0x000000007077A000

070778000 0000A206 05800087 0032E698 02040000 [..........2.....]

070778010 00000000 00000001 0001209A 0032E2E1 [......... ....2.]

070778020 00008000 00320002 05800080 00170005 [......2.........]

070778030 000004B4 010018F5 001E015E 00002003 [........^.... ..]

070778040 0032E698 00210009 000004A4 010012FC [..2...!.........]

070778050 002700EE 00072001 0032E305 00000000 [..'.. ....2.....]

070778060 00000000 00060100 001EFFFF 1F441F68 [............h.D.]

070778070 00001F4D 1F900006 1F801F88 1F701F78 [M...........x.p.]

070778080 00001F68 00000000 00000000 00000000 [h...............]

070778090 00000000 00000000 00000000 00000000 [................]

Repeat 498 times

070779FC0 00000000 00000000 00000000 0202012C [............,...]

070779FD0 660107C1 0202012C 650106C1 0202012C [...f,......e,...]

070779FE0 640105C1 0202023C 630104C1 0202002C [...d<......c,...]

070779FF0 620103C1 0202002C 610102C1 E6980604 [...b,......a....]

Block header dump: 0x05800087

Object id on Block? Y

seg/obj: 0x1209a csc: 0x000000000032e2e1 itc: 2 flg: E typ: 1 - DATA

brn: 0 bdba: 0x5800080 ver: 0x01 opc: 0

inc: 0 exflg: 0

Itl Xid Uba Flag Lck Scn/Fsc

0x01 0x0005.017.000004b4 0x010018f5.015e.1e --U- 3 fsc 0x0000.0032e698

0x02 0x0009.021.000004a4 0x010012fc.00ee.27 --U- 1 fsc 0x0007.0032e305

bdba: 0x05800087

data_block_dump,data header at 0x70778064

===============

tsiz: 0x1f98

hsiz: 0x1e

pbl: 0x70778064

76543210

flag=--------

ntab=1

nrow=6

frre=-1

fsbo=0x1e

fseo=0x1f68

avsp=0x1f44

tosp=0x1f4d

0xe:pti[0] nrow=6 offs=0

0x12:pri[0] offs=0x1f90

0x14:pri[1] offs=0x1f88

0x16:pri[2] offs=0x1f80

0x18:pri[3] offs=0x1f78

0x1a:pri[4] offs=0x1f70

0x1c:pri[5] offs=0x1f68

block_row_dump:

tab 0, row 0, @0x1f90

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

col 0: [ 2] c1 02

col 1: [ 1] 61

tab 0, row 1, @0x1f88

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

col 0: [ 2] c1 03

col 1: [ 1] 62

tab 0, row 2, @0x1f80

tl: 2 fb: --HDFL-- lb: 0x2

tab 0, row 3, @0x1f78

tl: 8 fb: --H-FL-- lb: 0x1 cc: 2

col 0: [ 2] c1 05

col 1: [ 1] 64

tab 0, row 4, @0x1f70

tl: 8 fb: --H-FL-- lb: 0x1 cc: 2

col 0: [ 2] c1 06

col 1: [ 1] 65

tab 0, row 5, @0x1f68

tl: 8 fb: --H-FL-- lb: 0x1 cc: 2

col 0: [ 2] c1 07

col 1: [ 1] 66

end_of_block_dump

BH (0x72ff3c18) file#: 22 rdba: 0x05800087 (22/135) class: 1 ba: 0x72f02000

set: 10 pool: 3 bsz: 8192 bsi: 0 sflg: 0 pwc: 0,0

dbwrid: 0 obj: 73882 objn: 73882 tsn: [1/8] afn: 22 hint: f

hash: [0x65218d00,0x707f8fc0] lru: [0x6536af28,0x733ec410]

lru-flags: moved_to_tail

ckptq: [NULL] fileq: [NULL]

objq: [NULL] objaq: [NULL]

st: CR md: NULL fpin: 'ktspbwh2: ktspfmdb' fscn: 0x0 tch: 1 lfb: 252

cr: [scn: 0x32e2e1],[xid: 0xfffe.ffff.0],[uba: 0x0.0.0],[cls: 0x32e2e1],[sfl: 0x1],[lc: 0x0]

flags: block_written_once

Printing buffer operation history (latest change first):

cnt: 5

01. sid:00 L940:z_sw_cur:sw:cq 02. sid:00 L070:zswcu:ent:ob

03. sid:00 L082:zcr:ret:TRU 04. sid:01 L192:kcbbic2:bic:FBD

05. sid:01 L191:kcbbic2:bic:FBW 06. sid:01 L602:bic1_int:bis:FWC

07. sid:01 L822:bic1_int:ent:rtn 08. sid:01 L832:oswmqbg1:clr:WRT

09. sid:01 L930:kubc:sw:mq 10. sid:01 L913:bxsv:sw:objq

11. sid:01 L608:bxsv:bis:FBW 12. sid:01 L607:bxsv:bis:FFW

13. sid:11 L464:chg1_mn:bic:FMS 14. sid:11 L778:chg1_mn:bis:FMS

15. sid:11 L353:gcur:set:MEXCL 16. sid:11 L464:chg1_mn:bic:FMS

buffer tsn: 8 rdba: 0x05800087 (22/135)

scn: 0x322f9e seq: 0x01 flg: 0x06 tail: 0x2f9e0601

frmt: 0x02 chkval: 0xab54 type: 0x06=trans data

Hex dump of block: st=0, typ_found=1

Dump of memory from 0x0000000072F02000 to 0x0000000072F04000

072F02000 0000A206 05800087 00322F9E 06010000 [........./2.....]

072F02010 0000AB54 00000001 0001209A 00322F89 [T........ .../2.]

072F02020 00008000 00320002 05800080 00030001 [......2.........]

072F02030 000004B4 0100156E 00230133 00002003 [....n...3.#.. ..]

072F02040 00322F9E 00000000 00000000 00000000 [./2.............]

072F02050 00000000 00000000 00000000 00000000 [................]

072F02060 00000000 00030100 0018FFFF 1F651F80 [..............e.]

072F02070 00001F65 1F900003 1F801F88 00000000 [e...............]

072F02080 00000000 00000000 00000000 00000000 [................]

Repeat 501 times

072F03FE0 00000000 0202012C 630104C1 0202012C [....,......c,...]

072F03FF0 620103C1 0202012C 610102C1 2F9E0601 [...b,......a.../]

Block header dump: 0x05800087

Object id on Block? Y

seg/obj: 0x1209a csc: 0x0000000000322f89 itc: 2 flg: E typ: 1 - DATA

brn: 0 bdba: 0x5800080 ver: 0x01 opc: 0

inc: 0 exflg: 0

Itl Xid Uba Flag Lck Scn/Fsc

0x01 0x0001.003.000004b4 0x0100156e.0133.23 --U- 3 fsc 0x0000.00322f9e

0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000

bdba: 0x05800087

data_block_dump,data header at 0x72f02064

===============

tsiz: 0x1f98

hsiz: 0x18

pbl: 0x72f02064

76543210

flag=--------

ntab=1

nrow=3

frre=-1

fsbo=0x18

fseo=0x1f80

avsp=0x1f65

tosp=0x1f65

0xe:pti[0] nrow=3 offs=0

0x12:pri[0] offs=0x1f90

还是直接看尾部数据

block_row_dump:

tab 0, row 0, @0x1f90

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

col 0: [ 2] c1 02

col 1: [ 1] 61

tab 0, row 1, @0x1f88

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

col 0: [ 2] c1 03

col 1: [ 1] 62

tab 0, row 2, @0x1f80

tl: 2 fb: --HDFL-- lb: 0x2

tab 0, row 3, @0x1f78

tl: 8 fb: --H-FL-- lb: 0x1 cc: 2

col 0: [ 2] c1 05

col 1: [ 1] 64

tab 0, row 4, @0x1f70

tl: 8 fb: --H-FL-- lb: 0x1 cc: 2

col 0: [ 2] c1 06

col 1: [ 1] 65

tab 0, row 5, @0x1f68

tl: 8 fb: --H-FL-- lb: 0x1 cc: 2

col 0: [ 2] c1 07

col 1: [ 1] 66

end_of_block_dump

现在应该有5条数据，这里记录了六条，其中第三条是我们删除的，我们看看它有什么不同

tl: 2 fb: --HDFL-- lb: 0x2

tab 0, row 3, @0x1f78

tab 0, row 0, @0x1f90 --第一个表第一行的位置，定义了该表在行索引中的起始插槽号

tl行头: tl 表示行的总长度（total length）这里是 2 个节，这里猜测一下，没删之前数据是1，a，加一些其他的标志位，行大小为8字节，数据被删除之后为两个字节，可能类似mybatis用0或1来标志是否逻辑删除。

lb: lb 表示行的长度字节（length byte），这里是 0x2 个字节。

fb： 表示行的标志字节（flag byte），用来标识行的状态。这里的标志字节解释如下：

第一个横杠 - 表示这是一个普通的数据行。
H 表示该行包含行目录条目（Header）。
FL 表示该行有空闲空间。

删除的数据--HDFL--多了一个D，表示在逻辑上删除，物理上还存在。

Lock：byte 和上面的 ITL 的 lck 相对应，表示这行是否被 lock 了

cc:2 -表示有列数（column count），即这个表有两个字段，，这一行有 2 列。

col 0: [ 2] c1 02 --第一行的第一个字段长度和值

col 1: [ 1] 61 --第一行的第二个字段长度和值

col 0: [ 2] c1 02：表示第 0 列的内容是一个长度为 2 的值，十六进制为 c1 02。
col 1: [ 1] 61：表示第 1 列的内容是一个长度为 1 的值，十六进制为 61。
上面我们转成十进制就是我们存的数据

fb标志位列表：

H- head of row piece

D- Deleted row

F- first data piece

L- last data piece

P- First column cintinues from previous row

N- Last column cintinues in next piece

结论：

假设一成立

所以本文猜测当我们 delete 一行数据的时候，数据并不是物理的被删除，而是把该行标记为删除，这个时候 fb应该是--HDFL-- 而不是原来的--H-FL--

认真看的小伙伴应该还发现，在验证delete不需要插入数据，但我插入了三条，其实是想看看，插入的数据是否会占用数据块和高水位线之间的数据（也就是逻辑删除的数据），但是本文数据量太小，不具有参考性。

扩展：如何降低 HWM 高水位线

①移动表，②收缩表，③导入导出表，④在线重定义表