跟BasicFile一样，SecureFile同样也有enable storage in row和disable storage in row的区别，在SecureFile的LOB里默认设置同样也是enable storage in row。LOB控制结构size加上LOB数据size一共未满4000字节时，enable storage in row的情况下就存储在源表的LOB列内，超出时就存放在LOB segment里；而disable storage in row的情况下则无论是否超过4000字节，LOB数据均存放在LOB segment里。
《CLOB的物理存储结构及语言编码详解》一文中提到过，enable storage in row的情况下源表的LOB列最多能存放3964字节；而在DB11gR1的SecureFile LOB中，变成了3740字节；DB11gR2时又变成了3952字节。均为引用的数据，具体区别尚未弄清。个人认为Size的变化都是因为LOB的控制信息发生了细微的变化。注意这里的size都是在未使用重复消除、加密、压缩选项的情况下得出的。
4. SecureFile的物理存储结构
上文里创建过T11表，T11表没指定storage in row选项，因此就是默认的enable storage in row。给T11表插入两条测试数据，一条是in-line方式存储的，一条是out-of-line方式存储的：
SELECT id,dbms_lob.getlength(c1) FROM tst.t11;
ID DBMS_LOB.GETLENGTH(C1)
---------- ----------------------
1 56
2 25583
ALTER databas FLUSH buffer_cache;
SELECT dbms_rowid.rowid_to_absolute_fno(rowid,'TST','T11') fno,
dbms_rowid.rowid_block_number(rowid) bno,id FROM tst.t11;
FNO BNO ID
---------- ---------- ----------
5 132 1
5 132 2
ALTER system dump datafile 5 block 132;
SELECT value FROM v$diag_info WHERE name='Default Trace File';
VALUE
--------------------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/dbeuc/dbeuc/trace/dbeuc_ora_25611.trc
CREATE TABLE tst.t12 (id number,c2 CLOB) LOB (c2) STORE AS SECUREFILE(disable storage IN row);
插入一条很大的LOB数据：
SELECT id,dbms_lob.getlength(c2) FROM tst.t12;
ID DBMS_LOB.GETLENGTH(C2)
---------- ----------------------
1 49498672
ALTER databas FLUSH buffer_cache;
SELECT dbms_rowid.rowid_to_absolute_fno(rowid,'TST','T12') fno,
dbms_rowid.rowid_block_number(rowid) bno,id FROM tst.t12;
FNO BNO ID
---------- ---------- ----------
5 173 1
ALTER system dump datafile 5 block 173;
SELECT DBMS_UTILITY.data_block_address_block(TO_NUMBER(LTRIM('01403b81'),'xxxxxxxx'))
BNO FROM dual;
BNO
----------
15233
ALTER system dump datafile 5 block 15233;
附录：
测试表（只写了一种，其他的选项组合类似）：
create table tst.LOBTAB(ARTICLE_ID NUMBER PRIMARY KEY,ARTICLE_NAME VARCHAR2(50),
ARTICLE_DATA CLOB) tablespace data lob (ARTICLE_DATA)
store as SECUREFILE (tablespace DATA cache) LOGGING;
插入CLOB数据的存储过程insert_clob：
create or replace procedure tst.insert_clob (fromid in number,endid in number)
AS
i NUMBER;
V_LOB CLOB;
V_FILE BFILE := BFILENAME('HOME_DIR', 'lob.txt');
V_SOURCE NUMBER := 1;
V_DEST NUMBER := 1;
V_LANG NUMBER := 0;
V_WARN NUMBER;
BEGIN
for i in fromid..endid loop
V_SOURCE := 1;
V_DEST := 1;
INSERT INTO tst.LOBTAB VALUES (i, 'ABC'||to_char(i), 'TEST');
UPDATE tst.LOBTAB SET ARTICLE_DATA = EMPTY_CLOB where ARTICLE_ID=i RETURN ARTICLE_DATA INTO V_LOB;
DBMS_LOB.FILEOPEN(V_FILE);
DBMS_LOB.OPEN(V_LOB, DBMS_LOB.LOB_READWRITE);
DBMS_LOB.CLOSE(V_LOB);
DBMS_LOB.FILECLOSEALL;
COMMIT;
end loop;
END;
/
计算CLOB的INSERT操作的时间差是使用以下的PL/SQL：
declare
a VARCHAR2(50);
b VARCHAR2(50);
begin
select to_char(systimestamp,'HH24:MI:SS.FF3') into a from dual;
TST.insert_clob(1,20);
select to_char(systimestamp,'HH24:MI:SS.FF3') into b from dual;
dbms_output.put_line(a);
dbms_output.put_line(b);
end;
/