SecureFiles LOBs基础知识之存储篇

最新推荐文章于 2021-04-07 22:23:49 发布

长烟慢慢

最新推荐文章于 2021-04-07 22:23:49 发布

阅读量959

点赞数

分类专栏： oracle基础知识

oracle基础知识专栏收录该内容

167 篇文章

订阅专栏

转发自：http://blog.itpub.net/53956/viewspace-1299822/

作者：oliseh 时间：2014-10-15 17:39:26

SecureFiles LOBs相比于BasicFiles LOBs具有加密(encryption)、去重(deduplicaiton)、压缩(compression)等新功能，pctversion，chunksize等参数也仅仅为了向后兼容而保留，因此SecureFiles LOBs的自适应能力更强，在管理上更为简化，成为了clob、blob等大对象使用的首选，上面的这些功能描述可以参考官方文档来获得。

我们今天要讨论的是SecureFiles LOBs里与存储相关的知识，当你在使用SecureFiles LOBs的时候你是否了解它在磁盘上是如何存储的，在创建SecureFiles LOBs时对于存储有关的参数设定有何要求，lob如何管理自己的undo等问题，我们都将会通过实验来为大家解答这些问题。

? Securefile LOBs字段所在数据块的存储结构

--建立测试用表为dump作准备

create table lobt1 (id number,c1 clob) lob(c1) store as securefile lobt1_c1(disable storage in row);

insert into lobt1 values(1,lpad('A',10,'A'));

insert into lobt1 values(2,lpad('B',10,'B'));

commit;

alter system flush buffer_cache;

select dbms_rowid.rowid_to_absolute_fno(rowid,'SCOTT','LOBT1') absfno,dbms_rowid.rowid_block_number(rowid) blkno,count(1) from LOBT1 group by dbms_rowid.rowid_to_absolute_fno(rowid,'SCOTT','LOBT1'),dbms_rowid.rowid_block_number(rowid);

ABSFNO BLKNO COUNT(1)

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

131 723110 2

alter system dump datafile 131 block 723110;

-- lobt1表dump结果节选：

tab 0, row 0, @0x1f6c

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

col 0: [ 2] c1 02

col 1: [37]

00 54 00 01 02 0c 80 80 00 02 00 00 00 01 00 04 29 37 3d a5 00 11 40 90 00

0b 20 00 14 01 00 00 20 cb 0f 2f 01

LOB

Locator:

Length: 84(37)

Version: 1

Byte Length: 2

LobID: 00.00.00.01.00.04.29.37.3d.a5

Flags[ 0x02 0x0c 0x80 0x80 ]:

Type: CLOB

Storage: SecureFile

Characterset Format: IMPLICIT

Partitioned Table: No

Options: VaringWidthReadWrite

SecureFile Header:

Length: 17

Old Flag: 0x40 [ SecureFile ]

Flag 0: 0x90 [ INODE Valid ]

Layers:

Lengths Array: INODE:11

INODE:

20 00 14 01 00 00 20 cb 0f 2f 01

02 0c 80 80：字段类型是clob，如果是01 0c 00 80则为blob

00 00 00 01 00 04 29 37 3d a5：每一行都有一个唯一的一个lobid

00 11：securefile header的长度为17，从00 11开始到行尾红17 bytes

40：表示disable storage in row securefile，如果是48表示enable storage in row securefile

00 0b：inode长度

14：lob字段中数据所占的字节数为，0x14代表20bytes，我们插入的10个字母占用20 bytes(使用AL16UTF16字符集)

20 cb 0f 2f：chunk所在的data block address

01:以上述chunk地址为起始地址，所包含多少个连续的chunk

? SecureFiles LOBs对表与表空间存储参数上的要求

// 1、Maxsize的最小值测试 //

--创建一个表空间extent size设置为128K

create tablespace ts128k1 datafile '/oradata06/ts128k1.dbf' size 128M extent management local uniform size 128K segment space management auto;

--创建带有lob字段的表，并把lob segment的maxsize设置为512k，结果报错

create table tlob33 (id number, t33col2 clob) lob(t33col2) store as securefile tlob_tmp (tablespace ts128k1 storage(maxsize 512K) disable storage in row retention max);

ERROR at line 1:

ORA-60014: invalid MAXSIZE storage option value

create table tlob33 (id number, t33col2 clob) lob(t33col2) store as securefile tlob_tmp (tablespace ts128k1 storage(maxsize 768K) disable storage in row retention max);

ERROR at line 1:

ORA-60014: invalid MAXSIZE storage option value

--逐级增加maxsize的大小，来测试create 语句能否成功，直到maxsize=1024k才成功

create table tlob33 (id number,t33col2 clob) lob(t33col2) store as securefile tlob_33 (tablespace ts128k1 storage(maxsize 1024K) disable storage in row retention max);

Table created.

--记录lob segment的大小等信息

select bytes,segment_name from dba_segments where segment_name='TLOB_33';

BYTES SEGMENT_NAME

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

131072 TLOB_33

col segment_name format a20

col segment_type format a10

col tablespace_name format a15

set linesize 150

select segment_name,segment_type,tablespace_name,extent_id,file_id,relative_fno,block_id,blocks,bytes from dba_extents where segment_name='TLOB_33' and tablespace_name='TS128K1';

SEGMENT_NAME SEGMENT_TY TABLESPACE_NAME EXTENT_ID FILE_ID RELATIVE_FNO BLOCK_ID BLOCKS BYTES

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

TLOB_33 LOBSEGMENT TS128K1 0 1036 13 128 16 131072

// 2、表空间extent_size的最小值测试 //

--新创建一个表空间uniform size 缩小至64k，观察一下securefile对extent size大小是否有要求

create tablespace ts128k2 datafile '/oradata06/ts128k2.dbf' size 128M extent management local uniform size 64K segment space management auto;

--下面的错误表明Secure file对于表空间的extent size要求至少为112k(14*8k)，而实际Extent size只有64k(8*8k)，创建不成功

create table tlob44 (id number,t44col2 clob) lob(t44col2) store as securefile tlob_44 (tablespace ts128k2 storage(maxsize 1024K) disable storage in row retention max);

ERROR at line 1:

ORA-60019: Creating initial extent of size 14 in tablespace of extent size 8

--新建一个表空间uniform size 设置为112k，观察表空间的extent_size最小设为多少

create tablespace ts128k3 datafile '/oradata06/ts128k3.dbf' size 128M extent management local uniform size 112K segment space management auto;

--这回虽然112K了，但报了个ORA-00600错误

create table tlob55 (id number,t55col2 clob) lob(t55col2) store as securefile tlob_55 (tablespace ts128k3 storage(maxsize 2128K) disable storage in row retention max);

SQL> create table tlob55 (id number,t55col2 clob) lob(t55col2) store as securefile tlob_55 (tablespace ts128k3 storage(maxsize 2128K) disable storage in row retention max);

create table tlob55 (id number,t55col2 clob) lob(t55col2) store as securefile tlob_55 (tablespace ts128k3 storage(maxsize 2128K) disable storage in row retention max)

ERROR at line 1:

ORA-00600: internal error code, arguments: [ktsladdfcb-bsz], [3], [], [], [],

[], [], [], [], [], [], []

--测试下来最小的Extent size必须是8k*14+1=114689，因为一定要是8k的整数倍，实际就是120K(8k*15)作为最小的extent size

drop tablespace ts128k3 including contents and datafiles;

create tablespace ts128k3 datafile '/oradata06/ts128k3.dbf' size 128M extent management local uniform size 114689 segment space management auto;

Tablespace created.

drop tablespace ts128k3 including contents and datafiles;

create tablespace ts128k3 datafile '/oradata06/ts128k3.dbf' size 128M extent management local uniform size 120ksegment space management auto;

Tablespace created.

drop table tlob55;

create table tlob55 (id number,t55col2 clob) lob(t55col2) store as securefile tlob_55 (tablespace ts128k3 storage(maxsize 2128K) disable storage in row retention max);

Table created.

--下面来看一下为何oracle对存放lob segment的extent size大小有最低要求，以第一个测试中建立的TLOB_33这个segment为例，dump一下

alter system dump datafile 1036 block min 128 block max 143;

--从dump出来的内容里过滤出每个block的用途发现其中16个blocks中有11个是存放metadata的，我们知道lob是自己管理undo的所以这些都是存储上花费的开销，还有5个是存放数据用的

frmt: 0x02 chkval: 0x798b type: 0x45=NGLOB: Lob Extent Header

frmt: 0x02 chkval: 0xa7e5 type: 0x3f=NGLOB: Segment Header

frmt: 0x02 chkval: 0x798f type: 0x3d=NGLOB: Hash Bucket

frmt: 0x02 chkval: 0x798e type: 0x3d=NGLOB: Hash Bucket

frmt: 0x02 chkval: 0x798b type: 0x3d=NGLOB: Hash Bucket

frmt: 0x02 chkval: 0x07f8 type: 0x3d=NGLOB: Hash Bucket

frmt: 0x02 chkval: 0x798b type: 0x3d=NGLOB: Hash Bucket

frmt: 0x02 chkval: 0x7983 type: 0x3d=NGLOB: Hash Bucket

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

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

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

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

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

结论1：securefiles LOBs的最小大小必须>=1024k，其所在表空间的最小extent size为15个blocks，对于blocksize=8k来说， 1个 extent size就是120k

? SecureFiles LOBs在磁盘上的存储方式

Securefiles LOBs存储方式和BasicFiles LOBs一样有两种，一种是inline storage，另一种是out-of-line storage。我们知道对于BasicFiles LOBs来说如果lob字段长度<=3964个字节是和表存储在一起的，称为inline storage；>3964字节时会迁移到lob segment里，即out-of-line storage。对于SecureFiles LOBs来说这个值是多少？我们下面测试一下。

在测试之前有个概念需要明确一下，对于CLOB类型的字段其存储时所用的字符集有可能与数据库本身的字符集不一致，对于使用US7ASCII、WE8ISO8859P1等定长字符集的数据库来说clob字段存储时使用的字符集和数据库字符集一致；对于使用ZHS16GBK、UTF8等变长字符集的数据库clob字段存储时使用的字符集为UCS2（9i及以下版本）或者AL16UTF16(10g及以上版本)，UCS2、AL16UTF16都是定长的，长度为2bytes，举个例子对于字符A来说存储到varchar2字段占用1个字节，存储到clob字段时就会占用2个字节。以下测试数据库使用的是ZHS16GBK字符集

// 3、SecureFiles inline storage &out-of-line storage界限测试 //

--创建测试表

create table tsec_lob (id number,secol2 clob) lob(secol2) store as securefile lob_tsec (retention);

--先插入1982个字符，每个字符两个字节，总共占用是3964 bytes

insert into tsec_lob values(1,lpad('D',1982,'D'));

commit;

--dump出数据块的内容

select table_name,segment_name,securefile from dba_lobs where table_name='TSEC_LOB';

TABLE_NAME SEGMENT_NAME SEC

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

TSEC_LOB LOB_TSEC YES

select dbms_rowid.rowid_block_number(rowid),dbms_rowid.rowid_relative_fno(rowid) from tsec_lob;

DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID)

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

4284 302

select * from v$diag_info where name='Default Trace File'; --shzw1_ora_54592144.trc

alter system flush buffer_cache;

alter system dump datafile 302 block 4284;

--dump节选，目前字段总长度为3995bytes，其中前面31个bytes为metadata

block_row_dump:

tab 0, row 0, @0xff4

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

col 0: [ 2] c1 02

col 1: [3995]

00 54 00 01 02 0c 80 80 00 02 00 00 00 01 00 04 28 c9 b5 05 0f 87 48 90 0f

81 01 00 0f 7c 01 00 44 00 44 00 44 00 44 00 44 00 44 00 44 00 44 00 44 00

44 00 44 00 44 00 44 00 44 00 44 00 44 00 44 00 44 00 44 00 44 00 44 00 44

。。。。。。此处略去，一共有1982个”00 44”

--根据上面的推断4000-31=3969，如果字段里真正数据占用的长度达到3969 bytes时就会迁移到lob segments上去，在原来基础上增加两个字符的长度，总长度达到3999 bytes，其中数据部分为3968 bytes，Byte Length: 2表示一个字符占据2 bytes

update tsec_lob set secol2=lpad('D',1984,'D');

commit;

alter system flush buffer_cache;

select * from v$diag_info where name='Default Trace File'; --shzw1_ora_24903952.trc

alter system dump datafile 302 block 4284;

--shzw1_ora_24903952.trc内容

data_block_dump,data header at 0x11085d264

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

tsiz: 0x1f98

hsiz: 0x14

pbl: 0x11085d264

76543210

flag=--------

ntab=1

nrow=1

frre=-1

fsbo=0x14

fseo=0x4c

avsp=0xfdc

tosp=0xfdc

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

0x12:pri[0] offs=0x4c

block_row_dump:

tab 0, row 0, @0x4c

tl: 4008 fb: --H-FL-- lb: 0x2 cc: 2

col 0: [ 2] c1 02

col 1: [3999]

00 54 00 01 02 0c 80 80 00 02 00 00 00 01 00 04 28 c9 b5 9b 0f 8b 48 90 0f

85 01 00 0f 80 01 00 44 00 44 00 44 00 44 00 44 00 44 00 44 00 44 00 44 00

44 00 44 00 44 00 44 00 44 00 44 00 44 00 44 00 44 00 44 00 44 00 44 00 44

。。。省略

00 44 00 44 00 44 00 44 00 44 00 44 00 44 00 44 00 44 00 44 00 44 00 44

LOB

Locator:

Length: 84(3999)

Version: 1

Byte Length: 2

LobID: 00.00.00.01.00.04.28.c9.b5.9b

Flags[ 0x02 0x0c 0x80 0x80 ]:

Type: CLOB

Storage: SecureFile

Characterset Format: IMPLICIT

Partitioned Table: No

Options: VaringWidthReadWrite

SecureFile Header:

Length: 3979

Old Flag: 0x48 [ DataInRow SecureFile ]

Flag 0: 0x90 [ INODE Valid ]

Layers:

Lengths Array: INODE:3973

INODE:

--再增加一个字符的长度，发现字段会挪出table，放到数据dba:0x4b80b56f中，由此判断securefile字节数超过3969bytes时就会从in-line storage =>out-of-line storage

update tsec_lob set secol2=lpad('D',1985,'D');

commit;

alter system flush buffer_cache;

alter system dump datafile 302 block 4284; --shzw1_ora_24903952.trc

--shzw1_ora_24903952.trc内容

tab 0, row 0, @0x1f

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

col 0: [ 2] c1 02

col 1: [38]

00 54 00 01 02 0c 80 80 00 02 00 00 00 01 00 04 28 c9 b5 9c 00 12 40 90 00

0c 21 00 0f 82 01 00 01 4b 80 b5 6f 01

LOB

Locator:

Length: 84(38)

Version: 1

Byte Length: 2

LobID: 00.00.00.01.00.04.28.c9.b5.9c

Flags[ 0x02 0x0c 0x80 0x80 ]:

Type: CLOB

Storage: SecureFile

Characterset Format: IMPLICIT

Partitioned Table: No

Options: VaringWidthReadWrite

SecureFile Header:

Length: 18

Old Flag: 0x40 [ SecureFile ]

Flag 0: 0x90 [ INODE Valid ]

Layers:

Lengths Array: INODE:12

INODE:

21 00 0f 82 01 00 01 4b 80 b5 6f 01

--将inode中的4b 80 b5 6f转换成rdba地址，dump数据块内容

select dbms_utility.data_block_address_File(to_number(replace('4b 80 b5 6f',' '),'xxxxxxxx')) rfno ,dbms_utility.data_block_address_block(to_number(replace('4b 80 b5 6f',' '),'xxxxxxxx')) blkno from dual;

RFNO BLKNO

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

302 46447

select * from v$diag_info where name='Default Trace File'; --shzw1_ora_3146348.trc

alter system flush buffer_cache;

alter system dump datafile 302 block 46447;

--shzw1_ora_3146348.trc 内容，证明lob字段的内容已经迁移到lob segment里

seg/obj: 0x51a682 csc: 0xb89.32fd27ef itc: 1 flg: E typ: 5 - LOCAL LOBS

fsl: 0 fnx: 0xffffffff ver: 0x01

Itl Xid Uba Flag Lck Scn/Fsc

0x01 0x0406.01d.0019e032 0x00000000.0000.00 -B-- 0 fsc 0x0000.00000000

========

bdba [0x4b80b56f]

kdlich [11085d24c 56]

flg0 0x20 [ver=0 typ=data lock=n]

flg1 0x00

scn 0x0b89.32fd27ef

lid 00000001000428c9b59c

rid 0x00000000.0000

kdlidh [11085d264 24]

flg2 0x00 [ver=0 lid=short-rowid hash=n cmap=n pfill=n]

flg3 0x00

pskip 0

sskip 0

hash 0000000000000000000000000000000000000000

hwm 3970

spr 0

data [11085d280 52 8060]

00 44 00 44 00 44 00 44 00 44 00 44 00 44 00 44 00 44 00 44 00 44 00 44 00 44

。。。省略

00 44 00 44 00 44 00 44 00 44 00 44 00 44 00 44 00 44 00 4f 00 47 00 56 00 41

结论2：securefiles LOBs inline存储的字节数为<=3969 bytes，>3969 bytes时会整体迁移到lobsegment里，注意对于CLOB而言这个字节数仅仅表示存储上所占用的空间，具体对应多少个字符，还要根据数据库所采用的字符集来确定，对于ZHS16GBK等变长字符集的数据库来说，1个字符在存储上要占用两个字节，所以字符数<=1984时为inline存储，>1984时为out-of-line存储，对于其它定长字符集的数据库则不存在这个转换关系，对于BLOB类型的字段由于存储的都是二进制数据所以也无需进行换算

? 如何通过chunk address找到SecureFiles LOBs

SecureFiles LOBs是以chunk为单位存储的，要找到chunk必须先有chunk address，chunk address的存放有直接和间接两种方式，直接方式是指chunk address就保存在表里，通过这个chunk address能直接找到包含数据的chunk，但当一个SecureFiles LOBs较大且占用的空间连续性不是很好的时候就会以间接方式存放，间接方式是指表里的block指向包含chunk address列表的另外一个block，由这另外一个block去指向包含数据的chunk。看下面的图就很清楚了。

// 4、直接方式寻址 //

--创建测试表，disable storage in row

create tablespace lobtest_out datafile '/oradata06/lobtest_out.dbf' size 128m extent management local uniform size 128k segment space management auto;

drop table tout_lob;

create table tout_lob (id number,outcol2 clob) lob(outcol2) store as securefile lob_out (tablespace lobtest_out disable storage in row retention);

--为了能占用较多的chunk，采用loadclobfromfile将文本文件内容导入的方式，impmd.log.load.lob文件大小为1571061字节，使用存储过程进行导入

declare

v_bfile bfile:=bfilename('HISDMP','impmd.log.load.lob');

v_clob clob;

ncycle integer:=1;

i integer:=1;

v_dest_offset integer:=1;

v_src_offset integer:=1;

v_lang_context integer:=0;

v_warning integer;

begin

dbms_lob.fileopen(v_bfile);

v_clob:=empty_clob();

dbms_lob.createtemporary(v_clob,FALSE,dbms_lob.session);

dbms_lob.loadclobfromfile(v_clob,v_bfile,amount=>dbms_lob.lobmaxsize,dest_offset=>v_dest_offset,src_offset=>v_src_offset,bfile_csid=>0,lang_context=>v_lang_context,warning=>v_warning);

while ( i <= ncycle)

loop

insert into tout_lob values(i,v_clob);

i:=i+1;

end loop;

dbms_lob.fileclose(v_bfile);

end;

--导入完成后对表进行dump

select * from v$diag_info where name='Default Trace File'; --shzw1_ora_53936650.trc

alter system flush buffer_cache;

select dbms_rowid.rowid_to_absolute_fno(rowid,'SCOTT','TOUT_LOB') absfno,dbms_rowid.rowid_relative_fno(rowid) rfno,dbms_rowid.rowid_block_number(rowid) blkno,count(1) from tout_lob group by dbms_rowid.rowid_to_absolute_fno(rowid,'SCOTT','TOUT_LOB'),dbms_rowid.rowid_relative_fno(rowid),dbms_rowid.rowid_block_number(rowid);

ABSFNO RFNO BLKNO COUNT(1)

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

115 115 441493 1

select id,dbms_lob.getlength(outcol2) from tout_lob;

ID DBMS_LOB.GETLENGTH(OUTCOL2)

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

1 1571061

alter system dump datafile 115 block 441493;

--shzw1_ora_53936650.trc dump内容，其中03 80 02 a1记录的是包含chunk地址列表的dba地址

tab 0, row 0, @0x1f6d

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

col 0: [ 2] c1 02

col 1: [36]

00 54 00 01 02 0c 80 80 00 02 00 00 00 01 00 04 28 c9 b8 ed 00 10 40 90 00

0a 42 00 2f f1 ea 01 03 80 02 a1

LOB

Locator:

Length: 84(36)

Version: 1

Byte Length: 2

LobID: 00.00.00.01.00.04.28.c9.b8.ed

Flags[ 0x02 0x0c 0x80 0x80 ]:

Type: CLOB

Storage: SecureFile

Characterset Format: IMPLICIT

Partitioned Table: No

Options: VaringWidthReadWrite

SecureFile Header:

Length: 16

Old Flag: 0x40 [ SecureFile ]

Flag 0: 0x90 [ INODE Valid ]

Layers:

Lengths Array: INODE:10

INODE:

42 00 2f f1 ea 01 03 80 02 a1

--将03 80 02 a1转换为rdba地址

select dbms_utility.data_block_address_File(to_number(replace('03 80 02 a1',' '),'xxxxxxxx')) rfno ,dbms_utility.data_block_address_block(to_number(replace('03 80 02 a1',' '),'xxxxxxxx')) blkno from dual;

RFNO BLKNO

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

14 673

select name,file#,rfile# from v$datafile where rfile#=14;

NAME FILE# RFILE#

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

/oradata02/undo/undo202.dbf 14 14

/oradata06/lobtest_out.dbf 1037 14

---证明1037/673位于lob segment
select segment_name,segment_type,tablespace_name,extent_id,file_id,relative_fno,block_id,blocks,bytes from dba_extents where segment_name='LOB_OUT' and block_id<=673 and block_id+blocks>=673;

SEGMENT_NAME SEGMENT_TY TABLESPACE_NAME EXTENT_ID FILE_ID RELATIVE_FNO BLOCK_ID BLOCKS BYTES

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

LOB_OUT LOBSEGMENT LOBTEST_OUT 33 1037 14 672 16 131072

--对1037/673进行dump的结果显示一共使用了390个block，从dba:0x038000a7开始

select * from v$diag_info where name='Default Trace File'; --shzw1_ora_8652048.trc

alter system dump datafile 1037 block 673; --shzw1_ora_8652048.trc

--shzw1_ora_8652048.trc dump内容节选

Itl Xid Uba Flag Lck Scn/Fsc

0x01 0x0405.018.00165fd5 0x0105a321.128c.10 ---- 1 fsc 0x0000.00000000

========

bdba [0x038002a1]

kdlich [11085d24c 56]

flg0 0x18 [ver=0 typ=lhb lock=y]

flg1 0x00

scn 0x0b89.32fddadb

lid 00000001000428c9b8ed

rid 0x00000000.0000

kdlihh [11085d264 24]

flg2 0x00 [ver=0 lid=short-rowid hash=n it=n bt=n xfm=n ovr=n aux=n]

flg3 0x80 [vll=y]

flg4 0x00

flg5 0x00

hash 0000000000000000000000000000000000000000

llen 0.3142122

ver 0.1

#ext 40

asiz 40

hwm 40

ovr 0x00000000.0

dba0 0x00000000

dba1 0x00000000

dba2 0x00000000

dba3 0x00000000

auxp 0x00000000

ldba 0x03800251

nblk 390

[0] 0x00 0x00 9 0x038000a7

[1] 0x00 0x00 3 0x038000bd

[2] 0x00 0x00 2 0x038000a5

[3] 0x00 0x00 15 0x038000c1

[4] 0x00 0x00 9 0x038000d7

[5] 0x00 0x00 6 0x038000d1

[6] 0x00 0x00 9 0x038000e7

[7] 0x00 0x00 6 0x038000e1

[8] 0x00 0x00 9 0x038000f7

[9] 0x00 0x00 6 0x038000f1

[10] 0x00 0x00 15 0x03800101

[11] 0x00 0x00 9 0x03800117

[12] 0x00 0x00 6 0x03800111

[13] 0x00 0x00 9 0x03800127

[14] 0x00 0x00 6 0x03800121

[15] 0x00 0x00 9 0x03800137

[16] 0x00 0x00 6 0x03800131

[17] 0x00 0x00 9 0x03800147

[18] 0x00 0x00 6 0x03800141

[19] 0x00 0x00 15 0x03800151

[20] 0x00 0x00 15 0x03800161

[21] 0x00 0x00 9 0x03800177

[22] 0x00 0x00 6 0x03800171

[23] 0x00 0x00 9 0x03800187

[24] 0x00 0x00 6 0x03800181

[25] 0x00 0x00 15 0x03800191

[26] 0x00 0x00 9 0x038001a7

[27] 0x00 0x00 6 0x038001a1

[28] 0x00 0x00 15 0x038001b1

[29] 0x00 0x00 9 0x038001c7

[30] 0x00 0x00 6 0x038001c1

[31] 0x00 0x00 15 0x038001d1

[32] 0x00 0x00 15 0x038001e1

[33] 0x00 0x00 15 0x03800201

[34] 0x00 0x00 15 0x038001f1

[35] 0x00 0x00 15 0x03800211

[36] 0x00 0x00 15 0x03800221

[37] 0x00 0x00 15 0x03800231

[38] 0x00 0x00 15 0x03800241

[39] 0x00 0x00 1 0x03800251

--dump一下0x038000a7的内容，包含的就是impmd.log.load.lob文件开头的4030个字符

alter system dump datafile 1037 block 167; --shzw1_ora_54067770.trc

--dump内容节选：

hwm 8060

spr 0

data [11085d280 52 8060]

00 0a 00 43 00 6f 00 6e 00 6e 00 65 00 63 00 74 00 65 00 64 00 20 00 74 00 6f

00 3a 00 20 00 4f 00 72 00 61 00 63 00 6c 00 65 00 20 00 44 00 61 00 74 00 61

// 5、满足什么条件会采用间接方式寻址 //

--新建测试表，准备好大小为82957 bytes的文本文件 impmd.log.load.12c1

drop table tout_lob;

create table tout_lob (id number,outcol2 clob) lob(outcol2) store as securefile lob_out (tablespace lobtest_out disable storage in row retention);

--执行存储过程用impmd.log.load.12c1文件填充lob字段

declare

v_bfile bfile:=bfilename('HISDMP','impmd.log.load.12c1');

v_clob clob;

ncycle integer:=1;

i integer:=1;

v_dest_offset integer:=1;

v_src_offset integer:=1;

v_lang_context integer:=0;

v_warning integer;

begin

dbms_lob.fileopen(v_bfile);

v_clob:=empty_clob();

dbms_lob.createtemporary(v_clob,FALSE,dbms_lob.session);

dbms_lob.loadclobfromfile(v_clob,v_bfile,amount=>dbms_lob.lobmaxsize,dest_offset=>v_dest_offset,src_offset=>v_src_offset,bfile_csid=>0,lang_context=>v_lang_context,warning=>v_warning);

while ( i <= ncycle)

loop

insert into tout_lob values(i,v_clob);

i:=i+1;

end loop;

dbms_lob.fileclose(v_bfile);

end;

--填充后dump tout_lob表内容

select * from v$diag_info where name='Default Trace File'; --shzw1_ora_11994174.trc

alter system flush buffer_cache;

ABSFNO RFNO BLKNO COUNT(1)

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

441 441 17562 1

alter system dump datafile 441 block 17562;

--shzw1_ora_11994174.trc内容，一共使用了21个blocks，分别是0x038000a9起始的7个blocks、0x038000bd起始的3个blocks、0x038000a5起始的4个blocks、0x038000ca起始的6个block、0x038000c1起始的1个block，采用的是直接寻址的方式

tab 0, row 0, @0x1f52

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

col 0: [ 2] c1 02

col 1: [63]

00 54 00 01 02 0c 80 80 00 02 00 00 00 01 00 04 28 ca e5 51 00 2b 40 90 00

25 22 00 02 88 1a 01 04 01 03 80 00 a9 07 01 03 80 00 bd 03 01 03 80 00 a5

04 01 03 80 00 ca 06 01 03 80 00 c1 01

--为了模拟出间接寻址的情况，使用如下过程往lob字段里每次增加4000字符，每个字符占用两个字节，所以每追加一次最多只会占用一个block，便于我们观察效果

select id,dbms_lob.getlength(outcol2) from tout_lob;

ID DBMS_LOB.GETLENGTH(OUTCOL2)

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

1 82957

--下面的过程执行6次，每次执行后都dump一下观察是否转为了间接寻址

declare

v_buffer varchar2(6000):=lpad('AB',4000,'AB');

v_clob clob;

i integer:=0;

ncycle integer:=1;

begin

select outcol2 into v_clob from tout_lob for update;

while (i < ncycle) loop

dbms_lob.writeappend(v_clob,4000,v_buffer);

i:=i+1;

end loop;

commit;

end;

--最新一次执行后的dump结果结果如下，blocks数量已经增加到了27个，表里存放的

chunk address数量已经达到了6个，使用的还是直接寻址：

select * from v$diag_info where name='Default Trace File'; --shzw1_ora_11994176.trc

alter system dump datafile 441 block 17562;

tab 0, row 0, @0x1f06

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

col 0: [ 2] c1 02

col 1: [69]

00 54 00 01 02 0c 80 80 00 02 00 00 00 01 00 04 28 ca e5 51 00 31 40 90 00

2b 22 00 03 43 9a 07 05 01 03 80 00 a9 07 01 03 80 00 bd 03 01 03 80 00 a5

04 01 03 80 00 ca 06 01 03 80 00 c1 02 01 03 80 00 c4 05

**最后再执行一次存储过程后,dump 441/17562的结果里终于呈现出间接寻址的迹象了

--再执行一次过程

declare

v_buffer varchar2(6000):=lpad('AB',4000,'AB');

v_clob clob;

i integer:=0;

ncycle integer:=1;

begin

select outcol2 into v_clob from tout_lob for update;

while (i < ncycle) loop

dbms_lob.writeappend(v_clob,4000,v_buffer);

i:=i+1;

end loop;

commit;

end;

--block 441/17562 dump结果，明显可以看出地址变短了，原来存放的6个chunk address变成了0x038000e1这一个地址

tab 0, row 0, @0x1edb

tl: 43 fb: --H-FL-- lb: 0x2 cc: 2

col 0: [ 2] c1 02

col 1: [36]

00 54 00 01 02 0c 80 80 00 02 00 00 00 01 00 04 28 ca e5 51 00 10 40 90 00

0a 42 00 03 df da 08 03 80 00 e1

LOB

Locator:

Length: 84(36)

Version: 1

Byte Length: 2

LobID: 00.00.00.01.00.04.28.ca.e5.51

Flags[ 0x02 0x0c 0x80 0x80 ]:

Type: CLOB

Storage: SecureFile

Characterset Format: IMPLICIT

Partitioned Table: No

Options: VaringWidthReadWrite

SecureFile Header:

Length: 16

Old Flag: 0x40 [ SecureFile ]

Flag 0: 0x90 [ INODE Valid ]

Layers:

Lengths Array: INODE:10

INODE:

42 00 03 df da 08 03 80 00 e1

-- dba:0x038000e1的dump结果显示该块中包含了前面直接寻址时的chunk地址,

Object id on Block? Y

seg/obj: 0x51a69a csc: 0xb89.32fe6c01 itc: 1 flg: E typ: 5 - LOCAL LOBS

fsl: 0 fnx: 0xffffffff ver: 0x01

Itl Xid Uba Flag Lck Scn/Fsc

0x01 0x03fb.012.001414b3 0x030693ac.fae5.07 ---- 1 fsc 0x0000.00000000

========

bdba [0x038000e1]

kdlich [11085d24c 56]

flg0 0x18 [ver=0 typ=lhb lock=y]

flg1 0x00

scn 0x0b89.32fe6c01

lid 00000001000428cae551

rid 0x00000000.0000

kdlihh [11085d264 24]

flg2 0x00 [ver=0 lid=short-rowid hash=n it=n bt=n xfm=n ovr=n aux=n]

flg3 0x80 [vll=y]

flg4 0x00

flg5 0x00

hash 0000000000000000000000000000000000000000

llen 0.253914

ver 0.8

#ext 9

asiz 9

hwm 9

ovr 0x00000000.0

dba0 0x00000000

dba1 0x00000000

dba2 0x00000000

dba3 0x00000000

auxp 0x00000000

ldba 0x038000c9

nblk 32

[0] 0x00 0x00 7 0x038000a9

[1] 0x00 0x00 3 0x038000bd

[2] 0x00 0x00 4 0x038000a5

[3] 0x00 0x00 6 0x038000ca

[4] 0x00 0x00 2 0x038000c1

[5] 0x00 0x00 5 0x038000c4

[6] 0x00 0x00 1 0x038000c3

[7] 0x00 0x00 3 0x038000dd

[8] 0x00 0x00 1 0x038000c9

但dba:0x038000e1这个起到地址索引效果的块位于lob segment里，并不在lob index里，oracle何时会使用lob index有待进一步考证

结论3：当表里直接寻址的chunk Address地址数大于6个时，会转换为间接寻址的模式

? SecureFiles LOBs的read consistency特性

当lob字段和表中其它非lob字段分开存放的时候，lob字段的读一致性实现依赖于lob segment本身，所有的修改前镜像会保存在lob segment里，和undo表空间没有任何关系，这样设计也是为了避免lob字段过大时产生的before-image过大从而影响undo表空间中的其它非lob对象的读一致性。Lob segment自己的读一致性管理机制由LOB_retention_clause所定义的值决定，这个值在建表的时候指定，之后也可以使用alter table进行修改，retention有以下四种取值，

MAX：在lob segment达到最大值之后，才开始覆盖before-image所占用的block，前提是在建lob segment时必须指定maxsize；

MIN：数据库工作在闪回模式下，限定特定的lob segment能够闪回到多久时间以前的状态

AUTO：before-image的保留时间参照数据库的undo_retention参数

NONE：不保存before-image，用于不需要读一致性的环境

深入lob读一致性之前，我们先要了解一下一个lob segment中的数据块有哪些类型，MOS 1453350.1中提供了check_space_securefile存储过程，里面封装的是DBMS_SPACE.SPACE_USAGE，用来检测lob segment中数据块的使用情况。以owner和lobsegment名作为输入，例如：exec check_space_securefile('SCOTT','TLOB_AUTO'); 输出为：

Segment Blocks/Bytes = 64 / 524288

Unused Blocks/Bytes = 34 / 278528

Used Blocks/Bytes = 8 / 65536

Expired Blocks/Bytes = 13 / 106496

Unexpired Blocks/Bytes = 9 / 73728

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

NON Data Blocks/Bytes = 56 / 458752

它将数据块分为unused、used、expired、unexpired四种，

Unused block：这个命名有点误导，指的是起到存放metadata的block，诸如：Segment Header、CFS Hash Bucket、Committed Free Space、Uncommit Free Space、Persistent Undo，这些类型的的block都是lob segment所特有的，既然能有自己的读一致性机制这些控制信息必不可少。所以Unused block并非指空闲的数据块。

Used block：已经包含有用户数据的block

Expired block：空闲的数据库块(包括从未被使用的和曾经被使用过但按照现有的retention策略可以被覆盖使用的)

Unexpired blocks：存放修改前镜像，为满足读一致性需要暂时保留不能被覆盖的数据块

以上输出中的NON Data Blocks是将segment_blocks-used_blocks而得到

我们分别体验一下MAX、AUTO、NONE三个参数的作用

// 6、retention MAX //

###先测试一下在没有达到maxsize的情况下，修改前镜像会一直保留着

--建表，指定lob segment最大为1024k

drop table tlob44;

create table tlob44 (id number,t44col2 clob) lob(t44col2) store as securefile tlob_44 (tablespace ts128k3 storage(maxsize 1024K) disable storage in row retention max);

--插入若干条记录

declare

v_str varchar2(6000);

i number;

j number;

begin

i:=101;

j:=1;

while ( i < 123 ) loop

v_str:=lpad(chr(i),6000,chr(i));

insert into tlob44 values(j,v_str);

i:=i+1;

j:=j+1;

end loop;

commit;

end;

--观察lob segment的空间使用情况

exec check_space_securefile('SCOTT','TLOB_44');

Segment Blocks/Bytes = 96 / 786432

Unused Blocks/Bytes = 36 / 294912

Used Blocks/Bytes = 45 / 368640

Expired Blocks/Bytes = 15 / 122880

Unexpired Blocks/Bytes = 0 / 0

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

NON Data Blocks/Bytes = 51 / 417792

--删除其中id<10的9条记录，有18个block变成了unexpired

delete tlob44 where id<10;

commit;

exec check_space_securefile('SCOTT','TLOB_44');

Segment Blocks/Bytes = 96 / 786432

Unused Blocks/Bytes = 36 / 294912

Used Blocks/Bytes = 27 / 221184

Expired Blocks/Bytes = 15 / 122880

Unexpired Blocks/Bytes = 18 / 147456

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

NON Data Blocks/Bytes = 69 / 565248

--再插入9条记录，由于没有达到maxsize所以unexpired blocks不会被重用

declare

v_str varchar2(6000);

i number;

j number;

begin

i:=98;

j:=1;

while ( i < 107 ) loop

v_str:=lpad(chr(i),6000,chr(i));

insert into tlob44 values(j,v_str);

i:=i+1;

j:=j+1;

end loop;

commit;

end;

---unexpired blocks依然保持18个，新进来的数据使用新分配的空间

exec check_space_securefile('SCOTT','TLOB_44');

Segment Blocks/Bytes = 112 / 917504

Unused Blocks/Bytes = 37 / 303104

Used Blocks/Bytes = 45 / 368640

Expired Blocks/Bytes = 12 / 98304

Unexpired Blocks/Bytes = 18 / 147456

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

NON Data Blocks/Bytes = 67 / 548864

###接着测试在达到maxsize的情况下，如果有新进数据会覆盖修改前镜像

drop table tlob33;

create table tlob33 (id number,t33col2 clob) lob(t33col2) store as securefile tlob_33 (tablespace ts128k3 storage(maxsize 1024K) disable storage in row retention max);

--插入44行记录，都是大小写英文字母，每行6000个字符，占据2个blocks

declare

v_str varchar2(6000);

j number:=1;

i number;

begin

i:=101;

while ( i < 123 ) loop

v_str:=lpad(chr(i),6000,chr(i));

insert into tlob33 values(j,v_str);

i:=i+1;

j:=j+1;

end loop;

i:=65;

while ( i < 87 ) loop

v_str:=lpad(chr(i),6000,chr(i));

insert into tlob33 values(j,v_str);

i:=i+1;

j:=j+1;

end loop;

commit;

end;

--统计space usage，还剩1个空闲的block

exec check_space_securefile('SCOTT','TLOB_33');

Segment Blocks/Bytes = 128 / 1048576

Unused Blocks/Bytes = 38 / 311296

Used Blocks/Bytes = 89 / 729088

Expired Blocks/Bytes = 1 / 8192

Unexpired Blocks/Bytes = 0 / 0

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

NON Data Blocks/Bytes = 39 / 319488

--只能再插入一个block，插入第二个block时就报错了，因为已经达到1024k上限了，说明maxsize设置生效

declare

v_str45 varchar2(3000):=lpad('S',3000,'S');

begin

insert into tlob33 values(45,v_str45);

commit;

end;

PL/SQL procedure successfully completed.

declare

v_str46 varchar2(3000):=lpad('T',3000,'T');

begin

insert into tlob33 values(46,v_str46);

commit;

end;

ERROR at line 1:

ORA-60010: adding (144) blocks to LOB segment SCOTT.TLOB_33 with MAXSIZE (128)

ORA-06512: at line 4

--expired block=0 说明没有空闲空间来容纳新的记录

SQL> exec check_space_securefile('SCOTT','TLOB_33');

Segment Blocks/Bytes = 128 / 1048576

Unused Blocks/Bytes = 38 / 311296

Used Blocks/Bytes = 90 / 737280

Expired Blocks/Bytes = 0 / 0

Unexpired Blocks/Bytes = 0 / 0

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

NON Data Blocks/Bytes = 38 / 311296

select bytes,segment_name from dba_segments where segment_name='TLOB_33';

BYTES SEGMENT_NAME

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

1048576 TLOB_33

-- session 2：为测试一致性读另开的

set transaction read only;

--删除刚刚插入的id=45的记录，释放出一个block

delete tlob33 where id=45;

commit;

--删除的记录放在Unexpired Blocks里

exec check_space_securefile('SCOTT','TLOB_33');

Segment Blocks/Bytes = 128 / 1048576

Unused Blocks/Bytes = 38 / 311296

Used Blocks/Bytes = 89 / 729088

Expired Blocks/Bytes = 0 / 0

Unexpired Blocks/Bytes = 1 / 8192

--session 2 能一致性读到id=45的记录，

select id,dbms_lob.getlength(t33col2) from tlob33 where id=45;

ID DBMS_LOB.GETLENGTH(T33COL2)

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

45 3000

--往tlob33表中再次插入一条记录，unexpired的block会被重用

declare

v_str47 varchar2(3000):=lpad('U',3000,'U');

begin

insert into tlob33 values(47,v_str47);

commit;

end;

--unexpired blocks变为0，Used Blocks增加1

exec check_space_securefile('SCOTT','TLOB_33');

Segment Blocks/Bytes = 128 / 1048576

Unused Blocks/Bytes = 38 / 311296

Used Blocks/Bytes = 90 / 737280

Expired Blocks/Bytes = 0 / 0

Unexpired Blocks/Bytes = 0 / 0

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

NON Data Blocks/Bytes = 38 / 311296

--session 2，再次查询tlob33收到ORA-1555错误：

select * from tlob33 where id=45;

ERROR:

ORA-01555: snapshot too old: rollback segment number with name "" too small

ORA-22924: snapshot too old

select count(*) from tlob33 where id<45;

COUNT(*)

----------

--继续删除id<3的记录

delete tlob33 where id<3;

2 rows deleted.

commit;

--空出来4个blocks

exec check_space_securefile('SCOTT','TLOB_33');

Segment Blocks/Bytes = 128 / 1048576

Unused Blocks/Bytes = 38 / 311296

Used Blocks/Bytes = 86 / 704512

Expired Blocks/Bytes = 0 / 0

Unexpired Blocks/Bytes = 4 / 32768

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

NON Data Blocks/Bytes = 42 / 344064

--session 2中能一致性读到这两条记录

select count(*) from tlob33 where id<3;

COUNT(*)

----------

--往tlob33表中再次插入一条记录，4个expired blocks里有两个会被重用，根据先进先出的原则被重用的应该是先插入的id=1的记录所在的block被覆盖

declare

v_str48 varchar2(6000):=lpad('V',6000,'V');

begin

insert into tlob33 values(48,v_str48);

commit;

end;

--session 2，测试结果验证了上面的结论:id=1的记录不能读取，id=2的记录能读到

SQL>select count(*) from tlob33 where id<3;

ERROR:

ORA-01555: snapshot too old: rollback segment number with name "" too small

ORA-22924: snapshot too old

SQL> select * from tlob33 where id=1;

ERROR:

ORA-01555: snapshot too old: rollback segment number with name "" too small

ORA-22924: snapshot too old

SQL> select * from tlob33 where id=2;

----------

T33COL2

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

ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff

结论4：retention=max时当lob segment大小增加到maxsize值时，会挪用unexpired blocks作为可用空间存放新入数据，挪用unexpired blocks时遵循先进先出的原则，即优先覆盖较早生成的block。在未触及maxsize的情况下修改前镜像会一直保留着。

// 7、retention AUTO //

--设置undo_retention=90，retention auto时会参照undo_retention设置时间保留修改前镜像

alter system set undo_retention=90;

SQL> show parameter undo_retention

NAME TYPE VALUE

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

undo_retention integer 90

--创建测试表

create table tlobauto (id number,autocol2 clob) lob(autocol2) store as securefile tlob_auto

(tablespace ts128k1 storage(maxsize 1024K) disable storage in row retention auto);

--检查空间使用情况

Set serveroutput on

exec check_space_securefile('SCOTT','TLOB_AUTO');

Segment Blocks/Bytes = 16 / 131072

Unused Blocks/Bytes = 11 / 90112

Used Blocks/Bytes = 0 / 0

Expired Blocks/Bytes = 5 / 40960

Unexpired Blocks/Bytes = 0 / 0

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

NON Data Blocks/Bytes = 16 / 131072

--填充16行，每行50个字符

declare

v_str_length integer:=50; --指定随机字串的单位长度

v_nrows integer:=16; --指定插入行数

v_conn_num integer:=1; --指定随机字串的单元数

i integer:=0;

j integer:=0;

v_str varchar2(32767);

begin

while (i < v_nrows) loop

v_str:=dbms_random.string('U',v_str_length);

while (j < v_conn_num-1) loop

v_str:=v_str||dbms_random.string('U',v_str_length);

j:=j+1;

end loop;

dbms_output.put_line(length(v_str));

insert into tlobauto values(i,v_str);

commit;

i:=i+1;

j:=0;

end loop;

commit;

end;

--17个used block，13个expired blocks

SQL> exec check_space_securefile('SCOTT','TLOB_AUTO');

Segment Blocks/Bytes = 64 / 524288

Unused Blocks/Bytes = 34 / 278528

Used Blocks/Bytes = 17 / 139264

Expired Blocks/Bytes = 13 / 106496

Unexpired Blocks/Bytes = 0 / 0

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

NON Data Blocks/Bytes = 47 / 385024

--删除id<5的5行记录

Delete tlobauto where id<5;

Commit;

--5个unexpired blocks，存放了被删除的5行记录

SQL> exec check_space_securefile('SCOTT','TLOB_AUTO');

Segment Blocks/Bytes = 64 / 524288

Unused Blocks/Bytes = 34 / 278528

Used Blocks/Bytes = 12 / 98304

Expired Blocks/Bytes = 13 / 106496

Unexpired Blocks/Bytes = 5 / 40960

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

NON Data Blocks/Bytes = 52 / 425984

--等待90秒后再检验,unexpired blocks为0，5个blocks都加到了expired blocks上面

SQL> exec check_space_securefile('SCOTT','TLOB_AUTO');

Segment Blocks/Bytes = 64 / 524288

Unused Blocks/Bytes = 34 / 278528

Used Blocks/Bytes = 12 / 98304

Expired Blocks/Bytes = 18 / 147456

Unexpired Blocks/Bytes = 0 / 0

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

NON Data Blocks/Bytes = 52 / 425984

--上面的结果看似验证了retention auto时before-image在lob segment里的保留时间跟随undo_retention的设置，但这也不是绝对的，比如下面的场景

--重新建立表tlobauto

Drop table tlobauto;

create table tlobauto (id number,autocol2 clob) lob(autocol2) store as securefile tlob_auto

(tablespace ts128k1 storage(maxsize 1024K) disable storage in row retention auto);

--初始空间使用情况

exec check_space_securefile('SCOTT','TLOB_AUTO');

Segment Blocks/Bytes = 16 / 131072

Unused Blocks/Bytes = 11 / 90112

Used Blocks/Bytes = 0 / 0

Expired Blocks/Bytes = 5 / 40960

Unexpired Blocks/Bytes = 0 / 0

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

NON Data Blocks/Bytes = 16 / 131072

--修改undo_retention为7200

Alter system set undo_retention=7200 scope=memory;

SQL> show parameter undo_retention

NAME TYPE VALUE

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

undo_retention integer 7200

--第一轮填充tlobauto表，16行，每行50个字符

set serveroutput on

declare

v_str_length integer:=50; --指定随机字串的单位长度

v_nrows integer:=16; --指定插入行数

v_conn_num integer:=1; --指定随机字串的单元数

i integer:=0;

j integer:=0;

v_str varchar2(32767);

begin

while (i < v_nrows) loop

v_str:=dbms_random.string('U',v_str_length);

while (j < v_conn_num-1) loop

v_str:=v_str||dbms_random.string('U',v_str_length);

j:=j+1;

end loop;

dbms_output.put_line(length(v_str));

insert into tlobauto values(i,v_str);

commit;

i:=i+1;

j:=0;

end loop;

commit;

end;

--check space usage

exec check_space_securefile('SCOTT','TLOB_AUTO');

Segment Blocks/Bytes = 64 / 524288

Unused Blocks/Bytes = 34 / 278528

Used Blocks/Bytes = 17 / 139264

Expired Blocks/Bytes = 13 / 106496

Unexpired Blocks/Bytes = 0 / 0

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

NON Data Blocks/Bytes = 47 / 385024

--删除 9行记录

delete tlobauto where id>6 and id<16;

commit;

--check space usage

exec check_space_securefile('SCOTT','TLOB_AUTO');

Segment Blocks/Bytes = 64 / 524288

Unused Blocks/Bytes = 34 / 278528

Used Blocks/Bytes = 8 / 65536

Expired Blocks/Bytes = 13 / 106496

Unexpired Blocks/Bytes = 9 / 73728

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

NON Data Blocks/Bytes = 56 / 458752

--第二轮继续填充tlobauto表，插入11行，每行50个字符

set serveroutput on

declare

v_str_length integer:=50; --指定随机字串的单位长度

v_nrows integer:=18; --指定插入行数

v_conn_num integer:=1; --指定随机字串的单元数

i integer:=7;

j integer:=0;

v_str varchar2(32767);

begin

while (i < v_nrows) loop

v_str:=dbms_random.string('U',v_str_length);

while (j < v_conn_num-1) loop

v_str:=v_str||dbms_random.string('U',v_str_length);

j:=j+1;

end loop;

dbms_output.put_line(length(v_str));

insert into tlobauto values(i,v_str);

commit;

i:=i+1;

j:=0;

end loop;

commit;

end;

--check space usage发现used blocks从8增加到了19，expired blocks从13下减到了11

exec check_space_securefile('SCOTT','TLOB_AUTO');

Segment Blocks/Bytes = 64 / 524288

Unused Blocks/Bytes = 34 / 278528

Used Blocks/Bytes = 19 / 155648

Expired Blocks/Bytes = 2 / 16384

Unexpired Blocks/Bytes = 9 / 73728

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

NON Data Blocks/Bytes = 45 / 368640

--第三轮又插入三行记录，每行50个字符

set serveroutput on

declare

v_str_length integer:=50; --指定随机字串的单位长度

v_nrows integer:=21; --指定插入行数

v_conn_num integer:=1; --指定随机字串的单元数

i integer:=18;

j integer:=0;

v_str varchar2(32767);

begin

while (i < v_nrows) loop

v_str:=dbms_random.string('U',v_str_length);

while (j < v_conn_num-1) loop

v_str:=v_str||dbms_random.string('U',v_str_length);

j:=j+1;

end loop;

dbms_output.put_line(length(v_str));

insert into tlobauto values(i,v_str);

commit;

i:=i+1;

j:=0;

end loop;

commit;

end;

--最终检查空间使用情况发现虽然没有达到undo_retention所指定的7200秒，lob segment也没有达到其maxsize所定义的上限值1024k，但还是挪用了1个unexpired blocks，unexpired blocks从9减少到了8，可见在retention auto的情况下并不是完全听从undo_retention的指挥，为何会出现这种情况还下不了结论，但至少证明了retention auto并不像文档中说的那么简单，还是有自己的一套算法

exec check_space_securefile('SCOTT','TLOB_AUTO');

Segment Blocks/Bytes = 80 / 655360

Unused Blocks/Bytes = 35 / 286720

Used Blocks/Bytes = 22 / 180224

Expired Blocks/Bytes = 15 / 122880

Unexpired Blocks/Bytes = 8 / 65536

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

NON Data Blocks/Bytes = 58 / 475136

结论5：retention auto的情况下before-image的保留时间不完全遵循与undo_retention参数的设定值，可能会引起ORA-01555错误

// 8、retention none //

--retention none比较好理解就是永远不保存修改前的镜像，这种情况下无法实现read consistency

create table tlobnone (id number,nonecol2 clob) lob(nonecol2) store as securefile tlob_none (tablespace ts128k1 storage(maxsize 1024K) disable storage in row retention none);

insert into tlobnone values(1,'A');

commit;

--插入一行后空间使用情况

exec check_space_securefile('SCOTT','TLOB_NONE');

Segment Blocks/Bytes = 48 / 393216

Unused Blocks/Bytes = 33 / 270336

Used Blocks/Bytes = 2 / 16384

Expired Blocks/Bytes = 13 / 106496

Unexpired Blocks/Bytes = 0 / 0

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

NON Data Blocks/Bytes = 46 / 376832

--session 2：测试一致性读所开的session