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:p