SQL> SELECT segment_name, segment_type,segment_subtype

2 FROM dba_segments

3 WHERE tablespace_name = 'SECF_TBS2'

4 AND segment_type = 'LOBSEGMENT'

5 /

SEGMENT_NAME SEGMENT_TYPE SEGMENT_SU

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

SYS_LOB0000071583C00004$$ LOBSEGMENTSECUREFILE

二．示例

2.1 修改DB_SECUREFILE参数

在1.3 小节提到这个参数，用来控制SecureFiles的使用。具体可设的参数参考1.3节。

启用SecureFile功能，数据的compatible 参数必须大于11.0.0.0. db_SecureFile 参数是个动态参数，我们可以直接修改，而不用重启实例。

[oracle@dave admin]$ ora paramdb_securefile

Session altered.

NAME ISDEFAULT SESMO SYSMOD VALUE

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

db_securefile TRUE TRUE IMMEDIATEPERMITTED

[oracle@dave admin]$ ora param compatible

Session altered.

NAME ISDEFAULT SESMO SYSMOD VALUE

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

compatible FALSE FALSE FALSE 11.2.0.0.0

SQL> alter system setdb_securefile='FORCE';

System altered.

SQL> !ora param db_securefile

Session altered.

NAME ISDEFAULT SESMO SYSMOD VALUE

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

db_securefile TRUE TRUE IMMEDIATE FORCE

SQL> alter system setdb_securefile='PERMITTED';

System altered.

SQL> !ora param db_securefile

Session altered.

NAME ISDEFAULT SESMO SYSMOD VALUE

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

db_securefile TRUE TRUE IMMEDIATE PERMITTED

2.2 创建 SecureFile LOBs

完整语法参考：

http://docs.oracle.com/cd/B28359_01/appdev.111/b28393/adlob_smart.htm

2.2.1 基本类型

SQL> CREATE TABLE bf_tab (

2 id NUMBER,

3 clob_data CLOB

4 )

5 LOB(clob_data) STORE AS BASICFILE;

Table created.

SQL> INSERT INTO bf_tab VALUES (1, 'MyCLOB data');

1 row created.

SQL> COMMIT;

Commit complete.

SQL> CREATE TABLE sf_tab (

2 id NUMBER,

3 clob_data CLOB

4 )

5 LOB(clob_data) STORE ASSECUREFILE;

CREATE TABLE sf_tab (

*

ERROR at line 1:

ORA-43853: SECUREFILElobs cannot be used in non-ASSM tablespace "SYSTEM"

--这里报错，创建securefile，必须是ASSM表空间。

SQL> selectTABLESPACE_NAME,SEGMENT_SPACE_MANAGEMENT from dba_tablespaces;

TABLESPACE_NAME SEGMEN

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

SYSTEM MANUAL

SYSAUX AUTO

UNDOTBS1 MANUAL

TEMP MANUAL

USERS AUTO

EXAMPLE AUTO

DAVE AUTO

7 rows selected.

SQL> CREATE TABLE sf_tab (

2 id NUMBER,

3 clob_data CLOB

4 )

5 LOB(clob_data) STORE ASSECUREFILE tablespace dave;

Table created.

SQL> INSERT INTO sf_tab VALUES (1, 'MyCLOB data');

1 row created.

SQL> commit;

Commit complete.

2.2.2 LOB Deduplication 重复值

LOB 重复值相关的2个选项：

（1）DEDUPLICATE：不允许出现重复值。

（2）KEEP_DUPLICATES： 允许出现重复值。

SQL> create user dave identified by"dave" default tablespace dave temporary tablespace temp;

User created.

SQL> grant connect,resource to dave;

Grant succeeded.

SQL> conn dave/dave;

Connected.

SQL> CREATE TABLE keep_duplicates_tab (

2 id NUMBER,

3 clob_data CLOB

4 )

5 LOB(clob_data) STORE ASSECUREFILE keepdup_lob(

6 KEEP_DUPLICATES

7 );

Table created.

SQL> CREATE TABLE deduplicate_tab (

2 id NUMBER,

3 clob_data CLOB

4 )

5 LOB(clob_data) STORE ASSECUREFILE dedup_lob (

6 DEDUPLICATE

7 );

Table created.

SQL> DECLARE

2 l_clob CLOB := RPAD('X',10000, 'X');

3 BEGIN

4 FOR i IN 1 .. 1000 LOOP

5 INSERT INTOkeep_duplicates_tab VALUES (i, l_clob);

6 END LOOP;

7 COMMIT;

8

9 FOR i IN 1 .. 1000 LOOP

10 INSERT INTO deduplicate_tab VALUES (i, l_clob);