The dynamic parameter DB_SECUREFILE is used to specify how to store large objects by default. LOBs can now be stored as securefiles or basicfiles. By default Oracle 11g allows for the use of both, securefile and basicfile lobs in the same database. The parameter can be changed with an ALTER SYSTEM statement.

Below is a listing of possible settings:

• Never: disallows creation lobs as securefiles

• Ignore: ignores all securefile and securefile options are ignored

• Always: attempts to create lobs as securefiles but falls back to basicfiles if specified other

• Force: enforces all lobs to be created as securefiles

• Permitted (DEFAULT): allows for securefile lobs to be created

LUTZ AS SYSDBA @ orcl SQL> show parameter securefile

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_securefile                        string      PERMITTED

The encryption wallet must be opened to use TDE for lobs:

LUTZ AS SYSDBA @ orcl SQL> ALTER SYSTEM SET ENCRYPTION WALLET OPEN IDENTIFIED BY "lutz1234";

System altered.

The status of the wallet is visible through v$encryption_wallet:

LUTZ AS SYSDBA @ orcl SQL> SELECT * FROM v$encryption_wallet;

WRL_TYPE             WRL_PARAMETER                                      STATUS
-------------------- -------------------------------------------------- -------
file                 /etc/ORACLE/WALLETS/oracle                         OPEN

LUTZ AS SYSDBA @ orcl SQL> SELECT tablespace_name,
                            encrypted,
                            extent_management,
                            segment_space_management
                          FROM dba_tablespaces;

TABLESPACE_NAME                ENC EXTENT_MAN SEGMENT
------------------------------ --- ---------- ------
SYSTEM                         NO  LOCAL      MANUAL
SYSAUX                         NO  LOCAL      AUTO
UNDOTBS1                       NO  LOCAL      MANUAL
TEMP                           NO  LOCAL      MANUAL
USERS                          NO  LOCAL      AUTO
ENCRYPT_TS                     NO  LOCAL      AUTO

6 rows selected.

SYTEM @ orcl SQL> ALTER USER lutz DEFAULT TABLESPACE encrypt_ts QUOTA UNLIMITED ON
encrypt_ts; 

Now let's create a table with an encrypted lob column:

LUTZ @ orcl SQL> SELECT CREATE TABLE encrypt_01(
    id NUMBER,
    resume_pdf CLOB ENCRYPT USING 'AES128' )
   LOB(resume_pdf) STORE AS SECUREFILE
   (DEDUPLICATE LOB CACHE NOLOGGING);

In the CREATE TABLE statement I have specified to use the 128 bit size Advanced Encryption Standard key for the column resume_pdf which will use securefile lobs with de-duplication.

We can check for securefile lob segments by using the view dba_segments:

LUTZ AS SYSDBA @ orcl SQL> SELECT segment_name, segment_type, segment_subtype
                             FROM dba_segments     
                              WHERE tablespace_name='ENCRYPT_TS';

SEGMENT_NAME                   SEGMENT_TYPE       SEGMENT_SU
------------------------------ ------------------ ----------
ENCRYPT_01                     TABLE              ASSM
SYS_IL0000060728C00002$$       LOBINDEX           ASSM
SYS_LOB0000060728C00002$$      LOBSEGMENT         SECUREFILE

The views *_encrypted_columns show the details about encrypted columns in the database:

LUTZ  @ orcl SQL> SELECT * FROM user_encrypted_columns;

TABLE_NAME      COLUMN_NAME          ENCRYPTION_ALG                SAL
-----------     -------------        ----------------              -----
ENCRYPT_01      RESUME_PDF           AES 128 bits key              YES

The properties of the lob segments are visible via the *_lobs views:

LUTZ  @ orcl SQL>  SELECT table_name,
                          securefile,
                          segment_name,
                          column_name,
                          encrypt,
                          compression,
                          deduplication
                     FROM user_lobs;
 

TABLE_NAME SEC SEGMENT_NAME              COLUMN_NAM ENCR COMPRE DEDUPLICATION
---------- --- ------------------------- ---------- ---- ------ --------------ENCRYPT_01 YES SYS_LOB0000060728C00002$$ RESUME_PDF YES  NO     LOB

The overloaded procedure space_usage from the package dbms_space can be used to monitor the lob segments. There is one procedure for basicfiles and one for securefile lob segments:

LUTZ  @ orcl SQL> DESC dbms_space
...
...

PROCEDURE SPACE_USAGE

 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 SEGMENT_OWNER                  VARCHAR2                IN
 SEGMENT_NAME                   VARCHAR2                IN
 SEGMENT_TYPE                   VARCHAR2                IN
 UNFORMATTED_BLOCKS             NUMBER                  OUT
 UNFORMATTED_BYTES              NUMBER                  OUT
 FS1_BLOCKS                     NUMBER                  OUT
 FS1_BYTES                      NUMBER                  OUT
 FS2_BLOCKS                     NUMBER                  OUT
 FS2_BYTES                      NUMBER                  OUT
 FS3_BLOCKS                     NUMBER                  OUT
 FS3_BYTES                      NUMBER                  OUT
 FS4_BLOCKS                     NUMBER                  OUT
 FS4_BYTES                      NUMBER                  OUT
 FULL_BLOCKS                    NUMBER                  OUT
 FULL_BYTES                     NUMBER                  OUT
 PARTITION_NAME                 VARCHAR2                IN     DEFAULT
 

PROCEDURE SPACE_USAGE

 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 SEGMENT_OWNER                  VARCHAR2                IN
 SEGMENT_NAME                   VARCHAR2                IN
 SEGMENT_TYPE                   VARCHAR2                IN
 SEGMENT_SIZE_BLOCKS            NUMBER                  OUT
 SEGMENT_SIZE_BYTES             NUMBER                  OUT
 USED_BLOCKS                    NUMBER                  OUT
 USED_BYTES                     NUMBER                  OUT
 EXPIRED_BLOCKS                 NUMBER                  OUT
 EXPIRED_BYTES                  NUMBER                  OUT
 UNEXPIRED_BLOCKS               NUMBER                  OUT
 UNEXPIRED_BYTES                NUMBER                  OUT
 PARTITION_NAME                 VARCHAR2                IN     DEFAULT

# **** This is the procedure to use for securefile lob segments

PROCEDURE UNUSED_SPACE

 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 SEGMENT_OWNER                  VARCHAR2                IN
 SEGMENT_NAME                   VARCHAR2                IN
 SEGMENT_TYPE                   VARCHAR2                IN
 TOTAL_BLOCKS                   NUMBER                  OUT
 TOTAL_BYTES                    NUMBER                  OUT
 UNUSED_BLOCKS                  NUMBER                  OUT
 UNUSED_BYTES                   NUMBER                  OUT
 LAST_USED_EXTENT_FILE_ID       NUMBER                  OUT
 LAST_USED_EXTENT_BLOCK_ID      NUMBER                  OUT
 LAST_USED_BLOCK                NUMBER                  OUT
 PARTITION_NAME                 VARCHAR2                IN     DEFAULT
...

Here is an example how to use the dbms_space.space_usage:

LUTZ  @ orcl SQL> 

BEGIN
  dbms_space.space_usage
  (
    'LUTZ',
     SYS_LOB0000060728C00002$$',
     'LOB',
   segment_size_blocks => :seg_size_blcks,
   segment_size_bytes  => :seg_size_bytes,
   used_blocks         => :u_blcks,
   used_bytes          => :u_bytes,
   expired_blocks      => :exp_blcks,
   expired_bytes       => :exp_bytes,
   unexpired_blocks    => :unexp_blcks,
   unexpired_bytes     => :unexp_bytes
  );
END;
 /

PL/SQL procedure successfully completed.

LUTZ  @ orcl SQL> PRINT seg_size_bytes u_blcks u_bytes exp_blcks exp_bytes unexp_blcks unexp_bytes;

SEG_SIZE_BYTES U_BLCKS U_BYTES EXP_BLCKS EXP_BYTES UNEXP_BLCKS UNEXP_BYTES
-------------- ------- ------- --------- --------- ----------- -----------
        131072    5   40960    0       0           0      0

Here are a few more examples for securefile lob columns:

LUTZ  @ orcl SQL> CREATE TABLE encrypt_02 (
 id NUMBER, resume_xls  CLOB)

 LOB(resume_xls) STORE AS SECUREFILE           -- store as securefiles
       (COMPRESS HIGH                          -- compress
         RETENTION MIN 3600                    -- keep undo for at least 1 hour
                                               -- DEFAULT is RETENTION AUTO
         KEEP_DUPLICATES                            -- do not de-duplicate 
         CACHE READS                           -- cache only when reading
         NOLOGGING                                -- do not create redo for DML
       );                     

LUTZ  @ orcl SQL> CREATE TABLE encrypt_03        
 (id NUMBER, resume_doc  CLOB)                   -- store as encrypted      
 LOB(resume_doc) STORE AS SECUREFILE (ENCRYPT);  -- securefiles using default
                                                 -- algorithm AES192

LUTZ  @ orcl SQL> CREATE TABLE encrypt_04

(id NUMBER, resume_doc  CLOB ENCRYPT)            -- store as encrypted
 LOB(resume_doc) STORE AS SECUREFILE;            -- securefiles using default
                                                 -- algorithm AES192

The second and third example have the same results, it is just different syntax!