SecureFiles Lobs overview

Secure File LOBs was an Oracle 11g database new feature that provides faster access to unstructured data stored in LOB columns.

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.

License Notes:
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
      RETENTION MIN 3600

Query SecureFile Lobs

You can query the dba_lobs view to get more Information about your SecureFile Lobs.


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

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.


Kommentar verfassen

Trage deine Daten unten ein oder klicke ein Icon um dich einzuloggen:

Du kommentierst mit Deinem Abmelden / Ändern )


Du kommentierst mit Deinem Twitter-Konto. Abmelden / Ändern )


Du kommentierst mit Deinem Facebook-Konto. Abmelden / Ändern )

Google+ Foto

Du kommentierst mit Deinem Google+-Konto. Abmelden / Ändern )

Verbinde mit %s