Oracle的回滚段存储内容分析

来源：互联网发布：java定义一个动态数组编辑：程序博客网时间：2024/05/06 19:28

Oracle的回滚段存储内容分析

事务在执行DML操作时，会首先将相关的数据块写入数据缓冲区中，数据缓冲区中存储的是DML操作相关的完整数据块，比如我们对表中的某一个记录执行update操作，oracle会将记录所在的数据块读入数据缓冲区中。

在执行update操作之后，oracle后台进程会首先将修改前的内容（包括数据块中其他记录内容）以及scn，块信息等写入回滚段中，但这里写的时候不仅仅只是简单的块复制，而是将原来的数据块顺序写入回滚段的数据块。测试表明，在源表数据块占用空间较少的情况下（比如设置pctfree为99），对源表两个数据块中记录的修改只占用一个回滚段中的数据块（因为这时回滚段的数据块pctfree值是默认的，相对较小，一个回滚块可以存储更多的源数据块）。但如果设置源表占用空间较大，比如设置默认或者设pctfree为1，则对源表的两个数据块内容修改时，会占用回滚段中的两个数据块。

同时会将修改记录的前后内容都写入重做日志文件中（这里只写入修改前后的该记录的信息，数据块中的其他记录将不会写入重做日志）。

一旦用户对该操作执行了commit或rollback操作，回滚段内容会理解清空。

现在我们来作个测试验证上面的说法。

1， 创建一个用户表Trollsegment，并插入数据1000条记录到表中

drop table trollsegment;

create table trollsegment(
FID integer,
Fname varchar2(40),
Fothers varchar2(40)
) tablespace odsd
pctfree 98;

insert into trollsegment
select rownum, rpad('name',20,rownum),rpad('other',20,rownum)
from dba_objects
where rownum < 1000;

commit;

2， 转储表中FID＝10的数据块内容

select dbms_rowid.rowid_block_number(rowid),count(*)
from trollsegment
group by dbms_rowid.rowid_block_number(rowid)

select dbms_rowid.rowid_block_number(rowid),dbms_rowid.rowid_relative_fno(rowid),fid
from trollsegment
where fid = 10

1 7437 14 10

SQL> alter system dump datafile 14 block 7437;

System altered

*** 2009-02-07 10:28:48.629

Start dump data blocks tsn: 11 file#: 14 minblk 7437 maxblk 7437

buffer tsn: 11 rdba: 0x03801d0d (14/7437)

scn: 0x0001.8569780b seq: 0x01 flg: 0x02 tail: 0x780b0601

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

Block header dump: 0x03801d0d

Object id on Block? Y

seg/obj: 0x167cc csc: 0x01.856977e6 itc: 2 flg: E typ: 1 - DATA

brn: 0 bdba: 0x3801d09 ver: 0x01

inc: 0 exflg: 0

Itl Xid Uba Flag Lck Scn/Fsc

0x01 0x0012.018.0000034d 0x0e000c3e.0054.48 --U- 3 fsc 0x0000.8569780b

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

data_block_dump,data header at 0x80000001001a3864

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

tsiz: 0x1f98

hsiz: 0x18

pbl: 0x80000001001a3864

bdba: 0x03801d0d

76543210

flag=--------

ntab=1

nrow=3

frre=-1

fsbo=0x18

fseo=0x1f08

avsp=0x1ef0

tosp=0x1ef0

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

0x12:pri[0] offs=0x1f08

0x14:pri[1] offs=0x1f38

0x16:pri[2] offs=0x1f68

block_row_dump:

tab 0, row 0, @0x1f08

tl: 48 fb: --H-FL-- lb: 0x1 cc: 3

col 0: [ 2] c1 0b

col 1: [20] 6e 61 6d 65 31 30 31 30 31 30 31 30 31 30 31 30 31 30 31 30

col 2: [20] 6f 74 68 65 72 31 30 31 30 31 30 31 30 31 30 31 30 31 30 31

tab 0, row 1, @0x1f38

tl: 48 fb: --H-FL-- lb: 0x1 cc: 3

col 0: [ 2] c1 0c

col 1: [20] 6e 61 6d 65 31 31 31 31 31 31 31 31 31 31 31 31 31 31 31 31

col 2: [20] 6f 74 68 65 72 31 31 31 31 31 31 31 31 31 31 31 31 31 31 31

tab 0, row 2, @0x1f68

tl: 48 fb: --H-FL-- lb: 0x1 cc: 3

col 0: [ 2] c1 0d

col 1: [20] 6e 61 6d 65 31 32 31 32 31 32 31 32 31 32 31 32 31 32 31 32

col 2: [20] 6f 74 68 65 72 31 32 31 32 31 32 31 32 31 32 31 32 31 32 31

end_of_block_dump

End dump data blocks tsn: 11 file#: 14 minblk 7437 maxblk 7437

3，对FID＝10的记录做更新，但不提交

update trollsegment set fname = replace(fname,'name','eman')
where fid = 10 or fid = 20;

4， 查看当前事务所占回滚段和起始回滚块，并导出当前回滚块的内容与转储出去的表内容进行比较。

查看当前活动事务占用的回滚段信息

SELECT s.username,t.xidusn,t.ubafil,t.ubablk,t.used_ublk
FROM v$session s,v$transaction t
WHERE s.saddr=t.ses_addr;

1 SYS 19 56 2269 2

修改的内容在两个数据块中，但回滚段只占用了一个数据块。

转储回滚段内容

SQL> alter system dump datafile 56 block 2269;

System altered

转储结果

*-----------------------------

KDO undo record:

KTB Redo

op: 0x04 ver: 0x01

op: L itl: xid: 0x0018.004.00000357 uba: 0x0e000a5d.0053.08

flg: C--- lkc: 0 scn: 0x0001.856980a3

KDO Op code: LKR row dependencies Disabled

xtype: XA bdba: 0x00401ec2 hdba: 0x00401ec1

itli: 1 ispac: 0 maxfr: 4863

tabn: 0 slot: 0 to: 0

*-----------------------------

* Rec #0x34 slt: 0x24 objn: 92108(0x000167cc) objd: 92108 tblspc: 11(0x0000000b)

* Layer: 11 (Row) opc: 1 rci 0x00

Undo type: Regular undo Begin trans Last buffer split: No

Temp Object: No

Tablespace Undo: No

rdba: 0x00000000

*-----------------------------

uba: 0x0e000c46.0054.32 ctl max scn: 0x0001.85697df0 prv tx scn: 0x0001.85697df3

KDO undo record:

KTB Redo

op: 0x03 ver: 0x01

op: Z

KDO Op code: URP row dependencies Disabled

xtype: XA bdba: 0x03801d0d hdba: 0x03801d0b

itli: 2 ispac: 0 maxfr: 4858

tabn: 0 slot: 0(0x0) flag: 0x2c lock: 0 ckix: 88

ncol: 3 nnew: 1 size: 0

col 1: [20] 6e 61 6d 65 31 30 31 30 31 30 31 30 31 30 31 30 31 30 31 30

*-----------------------------

* Rec #0x35 slt: 0x24 objn: 92108(0x000167cc) objd: 92108 tblspc: 11(0x0000000b)

* Layer: 11 (Row) opc: 1 rci 0x34

Undo type: Regular undo Last buffer split: No

Temp Object: No

Tablespace Undo: No

rdba: 0x00000000

*-----------------------------

KDO undo record:

KTB Redo

op: 0x03 ver: 0x01

op: Z

KDO Op code: URP row dependencies Disabled

xtype: XA bdba: 0x03809e2f hdba: 0x03801d0b

itli: 2 ispac: 0 maxfr: 4858

tabn: 0 slot: 1(0x1) flag: 0x2c lock: 0 ckix: 88

ncol: 3 nnew: 1 size: 0

col 1: [20] 6e 61 6d 65 32 30 32 30 32 30 32 30 32 30 32 30 32 30 32 30

End dump data blocks tsn: 33 file#: 56 minblk 3142 maxblk 3142

测试2：设置pctfree较小的情况

drop table trollsegment;

create table trollsegment(
FID integer,
Fname varchar2(400),
Fothers varchar2(400)
) tablespace odsd
pctfree 1;

insert into trollsegment
select rownum, rpad('name',400,rownum),rpad('other',400,rownum)
from dba_objects
where rownum < 1000;

commit;

select dbms_rowid.rowid_block_number(rowid),count(*)
from trollsegment
group by dbms_rowid.rowid_block_number(rowid)

select dbms_rowid.rowid_block_number(rowid),dbms_rowid.rowid_relative_fno(rowid),fid
from trollsegment
where fid = 10

update trollsegment set fname = replace(fname,'name','eman')
where fid = 10 or fid = 30;

SELECT s.username,t.xidusn,t.ubafil,t.ubablk,t.used_ublk
FROM v$session s,v$transaction t
WHERE s.saddr=t.ses_addr;

转储回滚段内容

SELECT b.name,a.xidusn, xidslot, xidsqn
FROM v$transaction a, v$rollname b
where a.XIDUSN = b.usn;

1 _SYSSMU16$ 16 26 799

SQL> alter system dump undo block '_SYSSMU16$'

2 xid 16 26 799;

System altered

*** 2009-02-07 11:12:10.687

********************************************************************************

Undo Segment: _SYSSMU16$ (16)

xid: 0x0010.01a.0000031f

Low Blk : (0, 0)

High Blk : (3, 127)

Object Id : ALL

Layer : ALL

Opcode : ALL

Level : 2

********************************************************************************

UNDO BLK: Extent: 2 Block: 78 dba (file#, block#): 56,0x00000857

xid: 0x0010.01a.0000031f seq: 0x52 cnt: 0x4f irb: 0x4f icl: 0x0 flg: 0x0000

Rec Offset Rec Offset Rec Offset Rec Offset Rec Offset

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

0x01 0x1fac 0x02 0x1f54 0x03 0x1efc 0x04 0x1ec4 0x05 0x1e6c

0x06 0x1e14 0x07 0x1ddc 0x08 0x1d84 0x09 0x1d2c 0x0a 0x1cf4

0x0b 0x1c9c 0x0c 0x1c44 0x0d 0x1c0c 0x0e 0x1bb4 0x0f 0x1b5c

0x10 0x1b24 0x11 0x1acc 0x12 0x1a74 0x13 0x1a3c 0x14 0x1a04

0x15 0x19ac 0x16 0x1954 0x17 0x191c 0x18 0x18c4 0x19 0x186c

0x1a 0x1834 0x1b 0x17dc 0x1c 0x1784 0x1d 0x174c 0x1e 0x16f4

0x1f 0x169c 0x20 0x1664 0x21 0x160c 0x22 0x15b4 0x23 0x157c

0x24 0x1524 0x25 0x14cc 0x26 0x1494 0x27 0x143c 0x28 0x13e4

0x29 0x13ac 0x2a 0x1354 0x2b 0x12fc 0x2c 0x12c4 0x2d 0x126c

0x2e 0x1214 0x2f 0x11dc 0x30 0x1184 0x31 0x112c 0x32 0x10f4

0x33 0x109c 0x34 0x1044 0x35 0x100c 0x36 0x0fb4 0x37 0x0f5c

0x38 0x0f24 0x39 0x0ecc 0x3a 0x0e74 0x3b 0x0e3c 0x3c 0x0de4

0x3d 0x0d8c 0x3e 0x0d54 0x3f 0x0cfc 0x40 0x0ca4 0x41 0x0c6c

0x42 0x0c14 0x43 0x0bbc 0x44 0x0b84 0x45 0x0b2c 0x46 0x0ad4

0x47 0x0a9c 0x48 0x0a44 0x49 0x09ec 0x4a 0x09b4 0x4b 0x095c

0x4c 0x0904 0x4d 0x08cc 0x4e 0x06b4 0x4f 0x04b4

*-----------------------------

* Rec #0x4f slt: 0x1a objn: 92112(0x000167d0) objd: 92112 tblspc: 11(0x0000000b)

* Layer: 11 (Row) opc: 1 rci 0x4e

Undo type: Regular undo Last buffer split: No

Temp Object: No

Tablespace Undo: No

rdba: 0x00000000

*-----------------------------

KDO undo record:

KTB Redo

op: 0x04 ver: 0x01

op: L itl: xid: 0x0013.02e.00000354 uba: 0x0e0008de.0052.31

flg: C--- lkc: 0 scn: 0x0001.85698ef5

KDO Op code: URP row dependencies Disabled

xtype: XA bdba: 0x03801d10 hdba: 0x03801d0b

itli: 1 ispac: 0 maxfr: 4858

tabn: 0 slot: 0(0x0) flag: 0x2c lock: 0 ckix: 88

ncol: 3 nnew: 1 size: 0

col 1: [400]

65 6d 61 6e 31 30 31 30 31 30 31 30 31 30 31 30 31 30 31 30 31 30 31 30 31

30 31 30 31 30 31 30 31 30 31 30 31 30 31 30 31 30 31 30 31 30 31 30 31 30

31 30 31 30 31 30 31 30 31 30 31 30 31 30 31 30 31 30 31 30 31 30 31 30 31