Securefile Lobs have several benefits than traditional Lobs. In addition, it features data compression, encryption and data deduplication.
SecureFiles supports advanced file system features such as:
- Deduplication – detecting identical securefile data, and storing only one copy
- Compression – Saves storage, I/O, redo and encryption overhead.
- Encryption – Uses TDE syntax, and current encryption algorithms.
- Journaling – File system-like logging.
Securefile provide feature like deduplication ,compression and encryption. This features needs a extra cost option and are only avaible in the Enterprise Edition (EE) of the Database. A little exception here is the compression. Basic Table/RMAN compression is a feature of the Oracle Enterprise Edition base, all other compression methods such:
- OLTP Table Compression
- SecureFiles Compression
- SecureFiles Deduplication
- Compression for Backup Data
are features of the Advanced Compression Option of the EE Database. Also new that the Oracle Total Recall Option is also a Feature the Advanced Compression Option and not longer a standalone Option. Total Recall provides the Table Archive Feature which i describe here.
Hybrid Columnar Compression also known as HCC Compression is a feature of the EE Database >= 11G that also dependents on the underlying storage system.
Only Exadata storage, ZFS storage, and Pillar Axiom 600 storage support Hybrid Columnar Compression. If you try to use HCC on a not supported configuration you will get this error ORA-64307.
64307, 00000, "hybrid columnar compression is only supported in tablespaces residing on Exadata storage" // *Document: YES // *Cause: An attempt was made to use hybrid columnar compression on unsupported storage. // *Action: Create this table in a tablespace residing on Exadata storage or use a different compression type. //
Currently there is no option to deinstall or disable the Advanced Compression Feature in a Database. Basic and Advanced compression are installed by default without the need of explicitly selecting the component for install or while creating the database with DBCA.
A SecureFile Lob can only be created in a ASSM (Automatic Segment Space Management) Tablespace. If you try to create a SecureFile Lob in a non ASSM Tablespace you will get:
ORA-43853: SECUREFILE lobs cannot be used in non-ASSM tablespace "<Tablespace Name>"
DB_SECUREFILE init.ora Parameter
The DBA can change the DB_SECUREFILE Parameter to control the behavior how LOBs are created in the database. The Parameter can changed with the ALTER SYSTEM command.
|ALWAYS||Attempt to create SecureFile, if not possible, creates BasicFile LOB|
|FORCE||Forces all LOBs created from that point forward to be SecureFiles|
|PERMITTED||Enable SecureFile creation (Default Value)|
|NEVER||Disable SecureFile LOB creation|
|IGNORE||Disable SecureFile LOBs, and ignores errors if a BasicFile LOB is created with forced SecureFile options|
Create a Table with SecureFile Lob
Here is a exmaple how to create a table with deduplication and compression.
CREATE TABLE demolob (emp_id NUMBER(38),lob_col CLOB) LOB (lob_col) STORE AS SECUREFILE dedup_comp_lob -- store as securefile (segment_name=>dedup_comp_lob) (TABLESPACE lobtbs2 -- store in tablespace lobtbs2 COMPRESS HIGH -- compress RETENTION MIN 3600 -- keep undo for at least 1 hour (DEFAULT is RETENTION AUTO) DEDUPLICATE -- de-duplicate CACHE READS -- cache only when reading NOLOGGING -- do not create redo for DML );
If you want to encrypt your SecureFile Lob you must first create a wallet like described in my Tablespace Encryption in Oracle 11g article and open it.
Now create your encrypted Lob
CREATE TABLE demolob (emp_id NUMBER(38),lob_col CLOB) LOB (lob_col) STORE AS SECUREFILE dedup_comp_lob (TABLESPACE lobtbs2 COMPRESS HIGH encrypt RETENTION MIN 3600 DEDUPLICATE CACHE READS NOLOGGING );
Query SecureFile Lobs
You can query the dba_lobs view to get more Information about your SecureFile Lobs.
select table_name,SEGMENT_NAME,ENCRYPT,COMPRESSION,DEDUPLICATION,FORMAT,SECUREFILE from dba_lobs where table_name = 'DEMOLOB' TABLE_NA SEGMENT_NAME ENC COMP DEDUPLICATION FORMAT SEC -------- --------------- --- ---- --------------- --------------- --- DEMOLOB DEDUP_COMP_LOB NO HIGH LOB ENDIAN NEUTRAL YES
If you have created a encrypted Lob you can query the user|dba_encrypted_columns to get more information.
SQL> select table_name,COLUMN_NAME,ENCRYPTION_ALG from user_encrypted_columns; TABLE_NAME COLUMN_NAME ENCRYPTION_ALG ------------------------------ ------------------------------ ----------------- DEMOLOB LOB_COL AES 192 bits key
SecureFiles are a very good thing, and i did not see any reason why not to use it. Because SecureFiles have some Performance benefits like prefetching and less Fragmentation against traditional LOB.