;
12 COMMIT;
13 END;
14 /
PL/SQL procedure successfully completed.
SQL> EXECDBMS_STATS.gather_table_stats(USER, 'keep_duplicates_tab');
PL/SQL procedure successfully completed.
SQL> EXECDBMS_STATS.gather_table_stats(USER, 'deduplicate_tab');
PL/SQL procedure successfully completed.
SQL> COLUMN segment_name FORMAT A30
SQL> SELECT segment_name, bytes
2 FROM user_segments
3 WHERE segment_name IN ('KEEPDUP_LOB', 'DEDUP_LOB');
SEGMENT_NAME BYTES
------------------------------ ----------
DEDUP_LOB 1179648
KEEPDUP_LOB 28442624
2 rows selected.
SQL>
允许重复值的情况下，占用的空间要大很多。我们可以使用alter table 命令来将deplicates改成keep_duplicates:
SQL> ALTER TABLE deduplicate_tab MODIFYLOB(clob_data) (
2 KEEP_DUPLICATES
3 );
Table altered.
SQL> EXECDBMS_STATS.gather_table_stats(USER, 'deduplicate_tab');
PL/SQL procedure successfully completed.
SQL> COLUMN segment_name FORMAT A30
SQL> SELECT segment_name, bytes
2 FROM user_segments
3 WHERE segment_name IN ('KEEPDUP_LOB', 'DEDUP_LOB');
SEGMENT_NAME BYTES
------------------------------ ----------
DEDUP_LOB 25296896
KEEPDUP_LOB 28442624
SQL>
2.2.3 LOB Compression 压缩
SecureFIles的COMPRESS 选项允许在表或者分区级别进行压缩。 压缩的级别也分medium 和 high。
默认使用medium。 压缩会消耗一些资源，所以如果使用high 的压缩，那么可能会影响系统的性能。 SecureFiles的压缩对表的压缩是没有影响的。
SQL> CREATE TABLE nocompress_tab (
2 id NUMBER,
3 clob_data CLOB
4 )
5 LOB(clob_data) STORE AS SECUREFILEnocompress_lob(
6 NOCOMPRESS
7 );
Table created.
SQL> CREATE TABLE compress_tab (
2 id NUMBER,
3 clob_data CLOB
4 )
5 LOB(clob_data) STORE ASSECUREFILE compress_lob (
6 COMPRESS HIGH
7 );
Table created.
SQL> DECLARE
2 l_clob CLOB := RPAD('X',10000, 'X');
3 BEGIN
4 FOR i IN 1 .. 1000 LOOP
5 INSERT INTO nocompress_tabVALUES (i, l_clob);
6 END LOOP;
7 COMMIT;
8
9 FOR i IN 1 .. 1000 LOOP
10 INSERT INTO compress_tab VALUES (i, l_clob);
11 END LOOP;
12 COMMIT;
13 END;
14 /
PL/SQL procedure successfully completed.
SQL> EXECDBMS_STATS.gather_table_stats(USER, 'nocompress_tab');
PL/SQL procedure successfully completed.
SQL> EXEC DBMS_STATS.gather_table_stats(USER,'compress_tab');
PL/SQL procedure successfully completed.
SQL> COLUMN segment_name FORMAT A30
SQL> SELECT segment_name, bytes
2 FROM user_segments
3 WHERE segment_name IN ('COMPRESS_LOB','NOCOMPRESS_LOB');
SEGMENT_NAME BYTES
------------------------------ ----------
COMPRESS_LOB 131072
NOCOMPRESS_LOB 28442624
SQL>
--使用压缩后，使用空间小很多。
将表compress_tab从压缩改成非压缩：
SQL> ALTER TABLE compress_tab MODIFY LOB(clob_data)(
2 NOCOMPRESS
3 );
Table altered.
SQL> EXECDBMS_STATS.gather_table_stats(USER, 'compress_tab');
PL/SQL procedure successfully completed.
SQL> COLUMN segment_name FORMAT A30
SQL> SELECT segment_name, bytes
2 FROM user_segments
3 WHERE segment_name IN ('COMPRESS_LOB','NOCOMPRESS_LOB');
SEGMENT_NAME BYTES
------------------------------ ----------
COMPRESS_LOB 26345472
NOCOMPRESS_LOB 28442624
--压缩的空间又释放出来了。
2.2.4 LOB Encryption 加密
SecureFileLobs 的加密依赖wallet 或者HardwareSecurity Model (HSM)来保存encryption key。所以在创建加密的SecureFile之前，必须先创建并打开一个wallet。
2.2.4.1 创建wallet
在sqlnet.ora 文件里添加: ENCRYPTION_WALLET_LOCATION 和WALLET_LOCATION参数。
默认位置是：$ORACLE_BASE/admin/$ORACLE_SID/wallet。
--指定ORACLE WALLET的位置，这里使用ORACLE_HOME/network/admin,在sqlnet.ora里添加如下内容：
WALLET_LOCATION =
(SOURCE =
(METHO