SecureFiles LOBs基礎知識之儲存篇
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 120k segment 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: 0x798b type: 0x3d=NGLOB: Hash Bucket
frmt: 0x02 chkval: 0x7983 type: 0x3d=NGLOB: Hash Bucket
frmt: 0x02 chkval: 0x7983 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 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;
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)
---------- ---------- ---------- ----------
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(*)
----------
44
--繼續刪除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(*)
----------
2
--往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;
ID
----------
T33COL2
--------------------------------------------------------------------------------
2
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
Set transaction read only
Select * from tlobnone where id=1;
ID
----------
NONECOL2
--------------------------------------------------------------------------------
1
A
--刪除這唯一的一行
Delete tlobnone where id=1;
Commit;
--unexpired blocks為0，被刪除的資料對應的block直接歸到了空閒的block裡
SQL> exec check_space_securefile('SCOTT','TLOB_NONE');
Segment Blocks/Bytes = 48 / 393216
Unused Blocks/Bytes = 33 / 270336
Used Blocks/Bytes = 1 / 8192
Expired Blocks/Bytes = 14 / 114688
Unexpired Blocks/Bytes = 0 / 0
===========================================================================
NON Data Blocks/Bytes = 47 / 385024
--session 2：還是能讀到修改前的映象，因為被刪除的行所對應的block只是被標記為了空閒塊的屬性，block裡的內容尚未被清理或者覆蓋，所以session 2能夠實現read consistency
Set transaction read only
Select * from tlobnone where id=1;
ID
----------
NONECOL2
--------------------------------------------------------------------------------
1
A
--執行下列語句15次，用盡14個expired blocks
insert into tlobnone values(1,'B');
--再回到session 2執行時報了ORA-01555，因為資料塊被真正覆蓋了
select * from tlobnone;
*
ERROR:
ORA-01555: snapshot too old: rollback segment number with name "" too small
ORA-22924: snapshot too old
結論6：retention none的情況下無法實現read consistency，隨時會導致ORA-01555
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/8494287/viewspace-1354978/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 基礎儲存知識
- Using Oracle SecureFiles LOBsOracle
- 儲存基礎知識白皮書
- HTML5學習之離線儲存基礎知識HTML
- 易失性儲存器SRAM基礎知識
- WebSocket系列之基礎知識入門篇Web
- MS(2)：Android之基礎知識篇Android
- 儲存基礎知識(1)--主要技術DAS、SAN、NAS
- Linux基礎知識複習之命令篇Linux
- 知識乾貨：基礎儲存服務新手體驗營
- 基礎知識1——資料物理,邏輯儲存結構
- 明解STM32—GPIO理論基礎知識篇之暫存器原理
- Java基礎知識回顧之七 —– 總結篇Java
- Java基礎知識回顧之七 ----- 總結篇Java
- Java基礎知識篇05——方法Java
- MySQL指南之基礎知識MySql
- 儲存知識websiteWeb
- Flutter 知識點總結-基礎篇Flutter
- Java基礎知識篇02——封裝Java封裝
- Python基礎知識之字典Python
- Python基礎知識之集合Python
- Java基礎知識整理之this用法Java
- Java基礎知識之概述（一）Java
- php基礎知識之$GET$POSTPHP
- oracle spatial 基礎知識之五Oracle
- 介面測試之基礎知識
- 高階儲存知識
- 儲存知識小結
- 儲存器的知識
- SQL Server基礎之儲存過程SQLServer儲存過程
- 移動端適配 - 基礎知識篇
- Java個人知識點總結（基礎篇）Java
- 前端開發基礎知識整理–css篇前端CSS
- Java基礎知識篇——Java基本介紹Java
- 《MySQL 基礎篇》十一：索引的儲存結構MySql索引
- 基礎知識
- 儲存基礎（一）
- 儲存基礎（二）