COMMIT;
END;
/
EXEC DBMS_STATS.gather_table_stats(USER, 'nocompress_tab');
EXEC DBMS_STATS.gather_table_stats(USER, 'compress_tab');
COLUMN segment_name FORMAT A30
SELECT segment_name, bytes
FROM user_segments
WHERE segment_name IN ('COMPRESS_LOB', 'NOCOMPRESS_LOB');
SEGMENT_NAME BYTES
------------------------------ ----------
COMPRESS_LOB 131072
NOCOMPRESS_LOB 71565312
2 rows selected.
SQL>
We can see that the compressed LOB segment is significantly smaller than the uncompressed LOB segment, and the degree of space saving depends on the storage type of LOB segment data.
ALTER can be usedTABLECommand resets compression mode.
|
ALTER TABLE compress_tab MODIFY LOB(clob_data) ( NOCOMPRESS ); EXEC DBMS_STATS.gather_table_stats(USER, 'compress_tab'); COLUMN segment_name FORMAT A30 SELECT segment_name, bytes FROM user_segments WHERE segment_name IN ('COMPRESS_LOB', 'NOCOMPRESS_LOB'); SEGMENT_NAME BYTES ------------------------------ ---------- COMPRESS_LOB 76808192 NOCOMPRESS_LOB 71630848 2 rows selected. SQL> |
LOB encryption
The encryption function of SecureFile LOB relies on the wallet or hardware security model (HSM) to control the encryption key. The wallet settings are the same as those described in Transparent Data Encryption (TDE) and Tablespace Encryption, so those two experiments are completed before trying the following examples.
SecureFile's ENCRYPT option performs block-level LOB content encryption, an optional USING clause defines which encryption algorithm is used (3DES168, AES128, AES192, or AES256), and the default AES192 algorithm is used, NOSALTThe option is not available for SecureFile encryption, which is applied on each column, so it affects all partitions using LOBs, the DECRPT option is used to explicitly block encryption, and the example below shows the creation of a table using the encrypted SecureFile LOB data type.
|
CREATE TABLE encrypt_tab ( id NUMBER, clob_data CLOB ) LOB(clob_data) STORE AS SECUREFILE encrypt_lob( ENCRYPT USING 'AES256' ); |
ALTER TABLECommands can be used to encrypt or decrypt existing columns. To switch the encryption algorithm you must use the REKEY option.
|
ALTER TABLE encrypt_tab MODIFY ( clob_data CLOB DECRYPT ); ALTER TABLE encrypt_tab MODIFY ( clob_data CLOB ENCRYPT USING '3DES168' ); ALTER TABLE encrypt_tab REKEY USING 'AES192'; |
Encryption is not supported by the original import and export tool or tablespace transfer, so the data pump import and export tool must be used to transfer data.
LOB cache and log
BasicFile and SecureFile LOB share some basic cache and log options. Common cache options are:
◆CACHE- LOB data is placed in the buffer.
◆CACHE READES - It is placed in the buffer during the process of reading LOB data, and is not placed in during the write operation.
◆NOCACHE - LOB data is not placed in the buffer, which is the default value of BasicFile and SecureFile LOB.
Basic logging options are:
◆LOGGING - A complete redo log is generated when creating and modifying LOBs, which is the default setting.
◆NOLOGGING - The operation is not recorded in the redo log and therefore cannot be restored, useful during first creation and huge loading.
Moreover, SecureFile LOB also has a log option FILESYSTEM_LIKE_LOGGING, which only records metadata and still allows segment recovery after a failure.
The CACHE option means LOGGING, so you cannot use CACHE with NOLOGGING or FILESYSTEM_LIKE_LOGGING.
The following code shows an example where cache and log options are explicitly set during and after table creation.
|
CREATE TABLE caching_and_logging_tab ( id NUMBER, clob_data CLOB ) LOB(clob_data) STORE AS SECUREFILE( NOCACHE FILESYSTEM_LIKE_LOGGING ); ALTER TABLE caching_and_logging_tab MODIFY LOB(clob_data) ( CACHE ); PL/SQL API |