Skip Headers
Oracle® Database SecureFiles and Large Objects Developer's Guide
11g Release 1 (11.1)

B28393-03
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
PDF · Mobi · ePub

4 Using Oracle SecureFiles

This chapter describes how to use SecureFiles, which were introduced to supplement the original LOBs implementation that is identified by the SQL parameter BASICFILE. This chapter contains these topics:

Storage of SecureFiles

This section summarizes LOB storage characteristics used to design tables with LOB column types. Beginning with Release 11.1, a new LOB storage paradigm is used by the database when the storage keyword SECUREFILE appears in the CREATE TABLE statement. The old storage paradigm is in effect if the keyword SECUREFILE is not used, or if the keyword BASICFILE is used. BASICFILE (the original LOB storage paradigm) is the default storage. The database administrator can use the following initialization parameter in init.ora to modify the preceding settings:

See Also:

"db_securefile"

Parameters for CREATE TABLE With SECUREFILE LOBs

A SecureFile can only be created in an automatic segment space management (ASSM) tablespace. The following parameter descriptions apply to the LOB storage paradigm using parameter SECUREFILE.

See Also:

Oracle Database SQL Language Reference, CREATE TABLE statement.

The new LOB features need new storage parameters. All the new parameters are described after the following BNF of CREATE TABLE.

CREATE [ GLOBAL TEMPORARY ] TABLE
   [ schema.]table OF
   [ schema.]object_type
   [ ( relational_properties ) ]
   [ ON COMMIT { DELETE | PRESERVE } ROWS ]
   [ OID_clause ]
   [ OID_index_clause ]
   [ physical_properties ]
   [ table_properties ] ;
 
<relational_properties> ::= 
{ column_definition
| { out_of_line_constraint
  | out_of_line_ref_constraint
  | supplemental_logging_props
  }
}
  [, { column_definition
     | { out_of_line_constraint
       | out_of_line_ref_constraint
       | supplemental_logging_props
       }
  ]...
 
<column_definition> ::= 
column data_type [ SORT ]
      [ DEFAULT expr ]
      [ ENCRYPT encryption_spec ]
      [ ( inline_constraint [ inline_constraint ] ... )
      | inline_ref_constraint 
      ]
 
<data_type> ::=
{ Oracle_built_in_datatypes
| ANSI_supported_datatypes
| user_defined_types
| Oracle_supplied_types
}
 
<Oracle_built_in_datatypes> ::=
{ character_datatypes
| number_datatypes
| long_and_raw_datatypes
| datetime_datatypes
| large_object_datatypes
| rowid_datatypes
}
 
<large_object_datatypes> ::= 
{ BLOB | CLOB | NCLOB| BFILE }
 
 <table_properties> ::=
  [ column_properties ]
  [ table_partitioning_clauses ]
  [ CACHE | NOCACHE ]
  [ parallel_clause ]
  [ ROWDEPENDENCIES | NOROWDEPENDENCIES ]
  [ enable_disable_clause ]
  [ enable_disable_clause ]...
  [ row_movement_clause ]
  [ AS subquery ]
 
<column_properties> ::=
  { object_type_col_properties
  | nested_table_col_properties
  | { varray_col_properties | LOB_storage_clause }
    [ (LOB_partition_storage
        [, LOB_partition_storage ]...
      )
    ]
  | XMLType_column_properties
  }
  [ { object_type_col_properties
    | nested_table_col_properties
    | { varray_col_properties | LOB_storage_clause }
      [ ( LOB_partition_storage
          [, LOB_partition_storage ]...
        )
      ]
    | XMLType_column_properties
    }
  ]...
 
<LOB_partition_storage> ::=
  PARTITION partition
  { LOB_storage_clause | varray_col_properties }
    [ LOB_storage_clause | varray_col_properties ]...
  [ ( SUBPARTITION subpartition
     { LOB_storage_clause | varray_col_properties }
       [ LOB_storage_clause
       | varray_col_properties
       ]...
    )
  ]
 
<LOB_storage_clause> ::=
  LOB
  { (LOB_item [, LOB_item ]...)
      STORE AS [ SECUREFILE | BASICFILE ] (LOB_storage_parameters)
  | (LOB_item)
      STORE AS [ SECUREFILE | BASICFILE ]
        { LOB_segname (LOB_storage_parameters)
        | LOB_segname
        | (LOB_storage_parameters)
        }
  }
 
<LOB_storage_parameters> ::=
  { TABLESPACE tablespace
  | { LOB_parameters [ storage_clause ]
    }
  | storage_clause
  }
    [ TABLESPACE tablespace
    | { LOB_parameters [ storage_clause ]
      }
    ]...
 
<LOB_parameters> ::=
  [ { ENABLE | DISABLE } STORAGE IN ROW
  | CHUNK integer
  | PCTVERSION integer
  | RETENTION [ { MAX | MIN integer | AUTO | NONE } ]
  | FREEPOOLS integer
  | LOB_deduplicate_clause
  | LOB_compression_clause
  | LOB_encryption_clause
  | { CACHE | { { NOCACHE | CACHE READS } [ logging_clause ] } }
  ]
 
<logging_clause> ::=
  { LOGGING | NOLOGGING | FILESYSTEM_LIKE_LOGGING }
 
<storage_clause> ::=
  STORAGE
  ({ INITIAL integer [ K | M ]
   | NEXT integer [ K | M ]
   | MINEXTENTS integer
   | MAXEXTENTS { integer | UNLIMITED }
   | PCTINCREASE integer
   | FREELISTS integer
   | FREELIST GROUPS integer
   | OPTIMAL [ integer [ K | M ]
             | NULL
             ]
   | BUFFER_POOL { KEEP | RECYCLE | DEFAULT }
   }
     [ INITIAL integer [ K | M ]
     | NEXT integer [ K | M ]
     | MINEXTENTS integer
     | MAXEXTENTS { integer | UNLIMITED }
     | MAXSIZE { { integer { K | M | G | T | P } } | UNLIMITED }
     | PCTINCREASE integer
     | FREELISTS integer
     | FREELIST GROUPS integer
     | OPTIMAL [ integer [ K | M ]
               | NULL
               ]
     | BUFFER_POOL { KEEP | RECYCLE | DEFAULT }
     ]...
  )
 
<LOB_deduplicate_clause> ::=
  { DEDUPLICATE 
  | KEEP_DUPLICATES
  }
 
<LOB_compression_clause> ::=
  { COMPRESS [ HIGH | MEDIUM ]
  | NOCOMPRESS }
 
<LOB_encryption_clause> ::=
  { ENCRYPT [ USING 'encrypt_algorithm' ] 
    [ IDENTIFIED BY password ]
  | DECRYPT 
  }
 
<XMLType_column_properties> ::= 
XMLTYPE [ COLUMN ] column
   [ XMLType_storage ]
   [ XMLSchema_spec ]
 
<XMLType_storage> ::=
STORE AS
   { OBJECT RELATIONAL
   | [ SECUREFILE | BASICFILE ] { CLOB | BINARY XML }
       [ { LOB_segname [ (LOB_parameters) ]
         | LOB_parameters
         }
         ]
 
<varray_col_properties> ::=
VARRAY varray_item 
   { [ substitutable_column_clause ]
     STORE AS [ SECUREFILE | BASICFILE ] LOB
        { [ LOB_segname ] (LOB_parameters)
        | LOB_segname 
        }
   | substitutable_column_clause
   }

Where:

BASICFILE

When the compatibility mode is set to 10g, the LOB storage clause is identical to that used in 10g (keyword BASICFILE is not valid). When the 11g compatibility mode (or greater) is set, the original, pre-11.1 release LOB functionality is enabled by default and this parameter is specified for completeness.

SECUREFILE

To use the SecureFile storage paradigm and functionality, explicitly specify the storage parameter SECUREFILE. A SecureFile can only be created in an automatic segment space management (ASSM) tablespace.

For BASICFILE LOBs, specifying any of the SECUREFILE options results in an error.

CHUNK

For SECUREFILE LOBs CHUNK is an advisory size and is provided for backward compatibility purposes.

RETENTION

Under 11g compatibility for SecureFiles, this parameter name specifies the retention policy to be used. The value of MAX specifies keeping old versions of LOB data blocks until the space used by segment has reached the size specified in the MAXSIZE parameter. If MAXSIZE is not specified, MAX behaves like AUTO.

A value of MIN means to use a retention time of the specified seconds. A value of NONE means that there is no retention period and space can be reused in any way deemed necessary. A value of AUTO tells the system to manage the space as efficiently as possible weighing both time and space needs.

For details of the RETENTION parameter used with BASICFILE LOBs:

MAXSIZE

Limits the amount of space that can be used by the LOB segment to the given size. If this size is consumed, new LOB data is taken from the old version disk space regardless of time requirements and as needed.

FREEPOOLS

Under 11g compatibility, this parameter is ignored for SECUREFILE LOBs.

LOGGING/NOLOGGING/FILESYSTEM_LIKE_LOGGING

Specify LOGGING if you want the creation of a database object, as well as subsequent inserts into the object, to be logged in the redo log file. LOGGING is the default.

Specify NOLOGGING if you do not want these operations to be logged.

For a nonpartitioned object, the value specified for this clause is the actual physical attribute of the segment associated with the object. For partitioned objects, the value specified for this clause is the default physical attribute of the segments associated with all partitions specified in the CREATE statement (and in subsequent ALTER ... ADD PARTITION statements), unless you specify the logging attribute in the PARTITION description.

FILESYSTEM_LIKE_LOGGING means that SecureFiles only log the metadata. This option is invalid for BasicFiles. This setting is similar to the metadata journaling of file systems, which reduces mean time to recovery from failures. The LOGGING setting for SecureFile LOBs is similar to the data journaling of file systems. Both the LOGGING and FILESYSTEM_LIKE_LOGGING settings provide a complete transactional file system by way of SecureFiles.

CACHE and NOLOGGING are not supported together. CACHE and FILESYSTEM_LIKE_LOGGING are not supported together.

FILESYSTEM_LIKE_LOGGING ensures that data is completely recoverable after a server failure.

Note:

For LOB segments, with the NOLOGGING and FILESYSTEM_LIKE_LOGGING settings it is possible for data to be changed on disk during a backup operation, resulting in read inconsistency. To avoid this situation, ensure that changes to LOB segments are saved in the redo log file by setting LOGGING for LOB storage.

FREELISTS/FREELIST GROUPS

Under 11g compatibility, these parameters are ignored when SECUREFILE LOBs are being created.

PCTVERSION/FREEPOOLS

Under 11g compatibility, these parameters are ignored when SECUREFILE LOBs are being created.

COMPRESS/NOCOMPRESS

Turns on or turns off LOB compression. LOB compression is orthogonal to index and table compression. In other words, setting table or index compression doesn't effect LOB compression and vice versa.

ENCRYPT/DECRYPT

Turns on or turns off LOB encryption and optionally selects which of the encryption algorithms is to be used.

DEDUPLICATE/KEEP_DUPLICATES

The option DEDUPLICATE enables you to specify that LOB data which is identical in two or more rows in a LOB column should all share the same data blocks. The database combines SecureFiles with identical content into a single copy, reducing storage and simplifying storage management. The opposite of this option is KEEP_DUPLICATES.

CREATE TABLE Usage Notes for Deduplication

  • Identical LOBs are good candidates for deduplication. Copy operations can avoid data duplication by enabling deduplication.

  • Duplicate detection happens within a LOB segment. For partitioned and subpartitioned LOB columns duplicate detection does not span partitions or subpartitions.

  • Deduplication can be specified at a partition level. The lob_storage_clause enables specification for partitioned tables on a per-partition basis.

  • Deduplication is applicable only to SecureFiles.

  • DBMS_LOB.SETOPTIONS can be used to enable or disable deduplication on individual LOBs.

CREATE TABLE Examples for Deduplication

Create a table with SECUREFILE and LOB-level deduplication:

CREATE TABLE t1 ( a CLOB)
    LOB(a) STORE AS SECUREFILE (
        DEDUPLICATE
        CACHE
    );

Create a table with a SECUREFILE LOB column and LOB deduplication enabled on only one partition. Only LOBs that belong to partition p1 are deduplicated.

CREATE TABLE t1 ( REGION VARCHAR2(20), a BLOB) 
     LOB(a) STORE AS SECUREFILE (
           CACHE
)
PARTITION BY LIST (REGION) (
     PARTITION p1 VALUES ('x', 'y')
          LOB(a) STORE AS SECUREFILE (
               DEDUPLICATE
          ),
     PARTITION p2 VALUES (DEFAULT)
);

Create a table with a SECUREFILE LOB column and disable deduplication. LOBs will be created with deduplication disabled.

CREATE TABLE t1 ( a CLOB)
    LOB(a) STORE AS SECUREFILE (
         KEEP_DUPLICATES
         CACHE
    );

Create a table with a SecureFile column and SecureFile deduplication enabled on the column except for one partition. All LOBs except those that belong to p2 will be deduplicated:

CREATE TABLE t1 ( REGION VARCHAR2(20), ID NUMBER, a BLOB)
     LOB(a) STORE AS SECUREFILE (
           DEDUPLICATE
           CACHE
)
PARTITION BY RANGE (REGION)
      SUBPARTITION BY HASH(ID) SUBPARTITIONS 2 (
        PARTITION p1 VALUES LESS THAN (51)
           lob(a) STORE AS a_t2_p1
           (SUBPARTITION t2_p1_s1 lob(a) STORE AS a_t2_p1_s1,
            SUBPARTITION t2_p1_s2 lob(a) STORE AS a_t2_p1_s2),
        PARTITION p2 VALUES LESS THAN (MAXVALUE)
           lob(a) STORE AS a_t2_p2 ( KEEP_DUPLICATES ) 
           (SUBPARTITION t2_p2_s1 lob(a) STORE AS a_t2_p2_s1,
            SUBPARTITION t2_p2_s2 lob(a) STORE AS a_t2_p2_s2)
      );

CREATE TABLE Usage Notes for Compression

  • SecureFile compression does not entail table or index compression and vice-versa.

  • A server-wide default SecureFile compression algorithm is used.

  • MEDIUM and HIGH options provide varying degrees of compression. The higher the degree of compression, the higher the latency incurred. The HIGH setting incurs more work, but will compress the data better. The default is MEDIUM.

  • Compression can be specified at a partition level. The lob_storage_clause enables specification for partitioned tables on a per-partition basis.

  • SecureFile compression is performed on the server-side and enables random reads and writes to LOB data. Client side compression utilities like utl_compress cannot provide random access.

  • DBMS_LOB.SETOPTIONS can be used to enable and disable compression on individual LOBs.

  • LOB compression is applicable only to SECUREFILE LOBs.

CREATE TABLE Examples for Compression

Create a table with a SECUREFILE compressed LOB column. The compression level is MEDIUM by default.

CREATE TABLE t1 ( a CLOB)
    LOB(a) STORE AS SECUREFILE (
         COMPRESS
         CACHE
         NOLOGGING
    );

Create a table with a SECUREFILE LOB column having the high level of compression.

CREATE TABLE t1 ( a CLOB)
    LOB(a) STORE AS SECUREFILE (
         COMPRESS HIGH
         CACHE
    );

Create a table with a SECUREFILE LOB column and LOB compression enabled on only one partition. Only LOBs that belong to partition p1 are compressed.

CREATE TABLE t1 ( REGION VARCHAR2(20), a BLOB) 
     LOB(a) STORE AS SECUREFILE (
        CACHE
     )
     PARTITION BY LIST (REGION) (
          PARTITION p1 VALUES ('x', 'y')
               LOB(a) STORE AS SECUREFILE (
                   COMPRESS
                ),
          PARTITION p2 VALUES (DEFAULT)
     );

Create a table with a SECUREFILE LOB column and LOB compression disabled.

CREATE TABLE t1 ( a CLOB)
    LOB(a) STORE AS SECUREFILE (
         NOCOMPRESS
         CACHE
    );

CREATE TABLE Usage Notes for Encryption

Create a table with encryption enabled or disabled on a LOB column. The current TDE (Transparent Data Encryption) syntax is used for extending encryption to LOB data types. The LOB must be created with SECUREFILE parameter.

  • Encryption is performed at block level.

  • 'encrypt_algorithm' indicates the name of the encryption algorithm. Valid algorithms are:

    • 3DES168

    • AES128

    • AES192 (default)

    • AES256

  • The column encryption key is derived from PASSWORD, if specified.

  • SALT is the default for LOB encryption. NO SALT is not supported.

  • All LOBs in the LOB column will be encrypted.

  • DECRYPT keeps the LOBs in cleartext.

  • LOBs can be encrypted only on a per-column basis (same as Transparent Data Encryption). All partitions within a LOB column will be encrypted.

  • Key management controls the ability to encrypt or decrypt.

  • LOB encryption is allowed only with SECUREFILE LOBs.

  • Transparent data encryption is not supported by the traditional import and export utilities or by transportable-tablespace-based export. Use the Data Pump import and export utilities with encrypted columns instead.

See Also:

The chapter on "Using Oracle Wallet Manager" in Oracle Database Advanced Security Administrator's Guide for how to create and use Oracle Wallet with TDE.

CREATE TABLE Examples for Encryption

Create a table with SECUREFILE LOB column and LOB encryption enabled using AES128.

CREATE TABLE t1 ( a CLOB ENCRYPT USING 'AES128')
    LOB(a) STORE AS SECUREFILE (
         CACHE
    );

Create a table with SECUREFILE LOB column and with LOB encryption enabled on all partitions.

CREATE TABLE t1 ( REGION VARCHAR2(20), a BLOB)
LOB(a) STORE AS SECUREFILE (
ENCRYPT USING 'AES128'
NOCACHE
FILESYSTEM_LIKE_LOGGING
)
PARTITION BY LIST (REGION) (
PARTITION p1 VALUES ('x', 'y'),
PARTITION p2 VALUES (DEFAULT)
);

Create a table with a SECUREFILE LOB column and LOB encryption key built with a password. By default AES192 bit encryption is used.

CREATE TABLE t1 ( a CLOB ENCRYPT IDENTIFIED BY foo)
    LOB(a) STORE AS SECUREFILE (
        CACHE
    );

The following example has the same result because the encryption option can be set in the LOB_deduplicate_clause section of the statement:

CREATE TABLE t1 (a CLOB)
    LOB(a) STORE AS SECUREFILE (
        CACHE
        ENCRYPT
        IDENTIFIED BY foo
    );

Create a SECUREFILE LOB with encryption disabled.

CREATE TABLE t1 ( a CLOB )
    LOB(a) STORE AS SECUREFILE (
        CACHE DECRYPT
    );

Parameters for ALTER TABLE With SECUREFILE LOBs

You can modify LOB storage with an ALTER TABLE statement or with online redefinition by using the DBMS_REDEFINITION package. If you have not enabled LOB encryption, compression, or deduplication at create time, Oracle recommends that you use online redefinition to enable them after creation, because this process is more disk-space efficient for changes to these three parameters.

Keywords are in bold.

ALTER TABLE [ schema.]table
  [ alter_table_properties
  | column_clauses
  | constraint_clauses
  | alter_table_partitioning
  | alter_external_table_clauses
  | move_table_clause
  ]
    [ enable_disable_clause
    | { ENABLE | DISABLE }
      { TABLE LOCK | ALL TRIGGERS }
    [ enable_disable_clause
    | { ENABLE | DISABLE }
      { TABLE LOCK | ALL TRIGGERS }
    ]...
  ] ;
 
 
<column_clauses> ::=
  { { add_column_clause
    | modify_column_clause
    | drop_column_clause
    }
    [ add_column_clause
    | modify_column_clause
    | drop_column_clause
    ]...
  | rename_column_clause
  | modify_collection_retrieval
    [ modify_collection_retrieval ]...
  | modify_LOB_storage_clause
    [ modify_LOB_storage_clause ] ...
  | alter_varray_col_properties
    [ alter_varray_col_properties ]
  }
 
<modify_LOB_storage_clause> ::=
MODIFY LOB (LOB_item) (LOB_deduplicate_clause [LOB_deduplicate_clause ] )
 
<LOB_deduplicate_clause> ::=
  { storage_clause
  | PCTVERSION integer
  | RETENTION [ { MAX | MIN integer | AUTO | NONE } ]
  | FREEPOOLS integer
  | REBUILD FREEPOOLS
  | LOB_deduplicate_clause
  | LOB_compression_clause
  | LOB_encryption_clause
  | { CACHE | { { NOCACHE | CACHE READS } [ logging_clause ] } }
  | allocate_extent_clause
  | shrink_clause
  | deallocate_unused_clause
  }
    [ storage_clause
    | PCTVERSION integer
    | RETENTION
    | FREEPOOLS integer
    | REBUILD FREEPOOLS
    | modify_LOB_sharing_clause
    | LOB_compression_clause
    | LOB_encryption_clause
  | { CACHE | { NOCACHE | CACHE READS } [ logging_clause ] }
    | shrink_clause
    | deallocate_unused_clause
    ]...

Where these keywords are defined for this statement as:

DEDUPLICATE/KEEP_DUPLICATES

The option DEDUPLICATE enables you to specify that LOB data which is identical in two or more rows in a LOB column should all share the same data blocks. The database combines LOBs with identical content into a single copy, reducing storage and simplifying storage management. The opposite of this option is KEEP_DUPLICATES.

COMPRESS/NOCOMPRESS

Enables or disables LOB compression. All LOBs in the LOB segment are altered with the new setting.

ENCRYPT/DECRYPT

Turns on or turns off LOB encryption. All LOBs in the LOB segment are altered with the new setting. A LOB segment can be altered only to enable or disable LOB encryption. That is, ALTER cannot be used to update the encryption algorithm or the encryption key. The encryption algorithm or encryption key can be updated using the ALTER TABLE REKEY syntax.

RETENTION

Altering RETENTION only effects space created after the ALTER TABLE statement was executed.

ALTER TABLE Usage Notes for Deduplication

ALTER TABLE syntax enables or disables LOB-level deduplication.

  • This syntax alters the deduplication mode of the LOB column.

  • Deduplication on existing LOBs can add significant latency.

  • DBMS_LOB.SETOPTIONS can be used to enable or disable deduplication on individual LOBs.

  • Deduplication can be specified at a table level or partition level. Deduplication does not span across partitioned LOBs.

  • Deduplication is applicable only to SecureFiles.

ALTER TABLE Examples for Deduplication

Disable deduplication on a SECUREFILE LOB.

ALTER TABLE t1 MODIFY 
     LOB(a) (
         KEEP_DUPLICATES 
    );

Enable LOB-level deduplication on a SECUREFILE LOB.

ALTER TABLE t1 MODIFY 
    LOB(a) (
         DEDUPLICATE
    );

Enable LOB-level deduplication on a SECUREFILE LOB column within a single partition.

ALTER TABLE t1 MODIFY PARTITION p1
    LOB(a) (
         DEDUPLICATE
    );

ALTER TABLE Usage Notes for Compression

  • This syntax alters the compression mode of the LOB column.

  • Compression on existing LOBs can add significant latency.

  • DBMS_LOB.SETOPTIONS can be used to enable or disable compression on individual LOBs.

  • Compression can be specified at a table level or partition level.

  • MEDIUM, and HIGH options provide varying degrees of compression. The higher the compression, the higher the latency incurred. The HIGH setting incurs more work, but will compress the data better. The default is MEDIUM.

  • LOB compression is applicable only to SecureFiles.

ALTER TABLE Examples for Compression

Disable compression on a SECUREFILE LOB.

ALTER TABLE t1 MODIFY 
    LOB(a) (
         NOCOMPRESS
    );

Enable compression on a SECUREFILE LOB with a high degree of compression.

ALTER TABLE t1 MODIFY 
    LOB(a) (
         COMPRESS HIGH
    );

Enable lob compression on a SECUREFILE LOB column within a single partition.

ALTER TABLE t1 MODIFY PARTITION p1
    LOB(a) (
         COMPRESS HIGH
    );

ALTER TABLE Usage Notes for Encryption

ALTER TABLE is used to enable and disable LOB encryption for SECUREFILE LOBs. This syntax also enables LOB columns to be re-keyed with a new key or algorithm.

  • ENCRYPT/DECRYPT options enable or disable encryption on all LOBs in the SecureFile column.

  • SALT is the default for LOB encryption. NO SALT is not supported.

  • The DECRYPT option converts encrypted columns to its cleartext form.

  • Key management controls the ability to encrypt or decrypt.

  • LOBs can be encrypted only on a per-column basis. A partitioned LOB has either all partitions encrypted or unencrypted.

  • LOB encryption is applicable only to SecureFiles.

ALTER TABLE Examples for Encryption

Enable LOB encryption using 3DES168.

ALTER TABLE t1 MODIFY 
      ( a CLOB ENCRYPT USING '3DES168');

Or, this second example of enabling LOB encryption using 3DES168.

ALTER TABLE t1 MODIFY LOB(a)
      (ENCRYPT USING '3DES168');

Enable encryption on a SECUREFILE LOB column and build the encryption key using a password.

ALTER TABLE t1 MODIFY 
    ( a CLOB ENCRYPT IDENTIFIED BY foo);

To re-encrypt the LOB column with a new key, re-key the table.

ALTER TABLE t1 REKEY USING '3DES168';

PL/SQL Packages for SecureFiles: Column Settings

The column settings packages for SecureFiles are outlined.

DBMS_LOB Package

LOBs inherit the LOB column settings for deduplication, encryption, and compression, which can also be configured on a per-LOB level using the LOB locator API. However, the LONG API cannot be used to configure these LOB settings. DBMS_LOB package additions for these features are described in the following sections.

DBMS_LOB.GETOPTIONS

The settings can be obtained using this function. An integer corresponding to a pre-defined constant based on the option type is returned.

DBMS_LOB.SETOPTIONS

This procedure sets features. It enables the features to be set on a per-LOB basis, overriding the default LOB settings. This call incurs a round trip to the server to make the changes persistent.

DBMS_LOB.ISSECUREFILE

This function returns TRUE or FALSE if the LOB locator (BLOB or CLOB) passed to it is or is not for a SecureFile.

DBMS_SPACE Package

This procedure is for SECUREFILE LOBs.

DBMS_SPACE.SPACE_USAGE

The existing SPACE_USAGE procedure is overloaded to return information about LOB space usage. It returns the amount of disk space in blocks used by all the LOBs in the LOB segment. This procedure can only be used on tablespaces that are created with auto segment space management.

Initialization Parameter for SecureFiles

The db_securefile parameter is set in the file init.ora:

db_securefile

Parameter Name: db_securefile

Parameter Type: text

Allowable Values: { ALWAYS | FORCE | PERMITTED | NEVER | IGNORE }

Default Value: PERMITTED

Description: This parameter enables the database administrator to either allow SECUREFILE LOBs to be created (PERMITTED), disallow SECUREFILE LOBs from being created going forward (NEVER), force all LOBs created going forward to be SECUREFILE LOBs (FORCE), attempt to create SECUREFILE LOBs but fall back to BASICFILE LOBs (ALWAYS), or disallow SECUREFILE LOBs and ignore any errors that would otherwise be caused by forcing BASICFILE LOBs with SECUREFILE options (IGNORE).

If NEVER is specified, any LOBs that are specified as SECUREFILE LOBs are created as BASICFILE LOBs. All SECUREFILE specific storage options and features (for example, compress, encrypt, deduplicate) will throw an exception. The BASICFILE LOB defaults are used for storage options not specified.

ALWAYS attempts to create all LOBs as SECUREFILE LOBs but creates any LOBs not in ASSM tablespaces as BASICFILE LOBs, unless SECUREFILE is explicitly specified. Any BASICFILE LOB storage options specified will be ignored, and the SECUREFILE defaults are used for all storage options not specified.

If FORCE is specified, all LOBs created in the system will be created as SECUREFILE LOBs. If the LOB is being created in an MSSM tablespace, an error will be thrown. Any BASICFILE LOB storage options specified will be ignored, and the SECUREFILE defaults are used for all storage options not specified.

If IGNORE is specified, the SECUREFILE keyword and all SECUREFILE options are ignored.

Dynamic: The parameter is dynamic and the scope is ALTER SYSTEM.

Example: ALTER SYSTEM SET db_securefile = 'ALWAYS';

Compatibility and Upgrading

All features described in this document will be enabled with compatibility set to 11.0.0.0.0 or higher. There will be no downgrade capability after 11.0.0.0.0 is set.

The BASICFILE LOB format is still supported under 11.0.0.0.0 compatibility.

If you want to upgrade your BASICFILE LOBs to SECUREFILE LOBs, you need to upgrade by the normal methods typically used to upgrade data (for example, CTAS/ITAS, online redefinition, export/import, column to column copy, or using a view and a new column). Most of these solutions mean using two times the disk space used by the data in the input LOB column. However, partitioning and taking these actions on a partition-by-partition basis may help lower the disk space required.

Note that Streams does not support SecureFiles in the current release.

Migrating Columns from BasicFile LOBs to SecureFiles

The method of migrating LOBs columns is presented in this section.

Preventing Generation of Redo Space when Migrating to SecureFile LOBs

Generation of redo space can cause performance problems during the process of migrating BasicFile LOB columns. Redo changes for the table are logged during the migration process only if the table has LOGGING set.

Redo changes for the column being converted from BasicFile LOB to SecureFile are logged only if the storage characteristics of the SecureFile LOB column indicate LOGGING. The logging setting (LOGGING or NOLOGGING) for the LOB column is inherited from the tablespace in which the LOB is created.

To prevent generation of redo space during migration make sure that you specify the NOLOGGING storage parameter for the new SecureFile LOB column(s). You can turn LOGGING on once your migration is complete.

Online Redefinition for BasicFiles

Online redefinition is the only recommended method for migration of BasicFile LOBs to SecureFiles. It can be done at the table or partition level.

Online Redefinition Advantages

  • No need to take the table or partition offline.

  • Can be done in parallel.

Online Redefinition Disadvantages

  • Additional storage equal to the entire table or partition and all LOB segments must be available.

  • Global indexes need to be rebuilt.

Using Online Redefinition for Migrating Tables with BasicFiles

You can also migrate a table using Online Redefinition. Online Redefinition has the advantage of not requiring the table to be off line, but it requires additional free space equal to or even slightly greater than the space used by the table. To migrate a table using Online Redefinition:

Example 4-1 Example of Online Redefinition

REM Grant privileges required for online redefinition.
GRANT EXECUTE ON DBMS_REDEFINITION TO pm;
GRANT ALTER ANY TABLE TO pm;
GRANT DROP ANY TABLE TO pm;
GRANT LOCK ANY TABLE TO pm;
GRANT CREATE ANY TABLE TO pm;
GRANT SELECT ANY TABLE TO pm;
REM Privileges required to perform cloning of dependent objects.
GRANT CREATE ANY TRIGGER TO pm;
GRANT CREATE ANY INDEX TO pm;
CONNECT pm
DROP TABLE cust;
CREATE TABLE cust(c_id NUMBER PRIMARY KEY,
c_zip NUMBER,
c_name VARCHAR(30) DEFAULT NULL,
c_lob CLOB
);
INSERT INTO cust VALUES(1, 94065, 'hhh', 'ttt');
-- Creating Interim Table
-- There is no need to specify constraints because they are
-- copied over from the original table.
CREATE TABLE cust_int(c_id NUMBER NOT NULL,
c_zip NUMBER,
c_name VARCHAR(30) DEFAULT NULL,
c_lob CLOB
) LOB(c) STORE AS SECUREFILE (NOCACHE FILESYSTEM_LIKE_LOGGING);
DECLARE
col_mapping VARCHAR2(1000);
BEGIN
-- map all the columns in the interim table to the original table
col_mapping :=
'c_id c_id , '||
'c_zip c_zip , '||
'c_name c_name, '||
'c_lob c_lob';
DBMS_REDEFINITION.START_REDEF_TABLE('pm', 'cust', 'cust_int', col_mapping);
END;
/
DECLARE
error_count pls_integer := 0;
BEGIN
DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS('pm', 'cust', 'cust_int',
    1, TRUE,TRUE,TRUE,FALSE, error_count);
DBMS_OUTPUT.PUT_LINE('errors := ' || TO_CHAR(error_count));
END;
/
EXEC DBMS_REDEFINITION.FINISH_REDEF_TABLE('pm', 'cust', 'cust_int');
-- Drop the interim table
DROP TABLE cust_int;
DESC cust;
-- The following insert statement fails. This illustrates
-- that the primary key constraint on the c_id column is
-- preserved after migration.
INSERT INTO cust VALUES(1, 94065, 'hhh', 'ttt');
SELECT * FROM cust;

Parallel Online Redefinition

On a system with sufficient resources for parallel execution, redefinition of a BASICFILE LOB column to a SECUREFILE LOB column can be executed in parallel under the following conditions:

  • In the case where the destination table is non-partitioned:

    The segment used to store the LOB column in the destination table belongs to a locally managed tablespace with Automatic Segment Space Management (ASSM) enabled, which is now the default and is a requirement for SecureFiles.

    There is a simple mapping from one LOB column to one LOB column, and the destination table has only one LOB column.

  • In the case where the destination table is partitioned:

    The normal methods for parallel execution for partitioning apply. When the destination table is partitioned, then online redefinition is executed in parallel.

For parallel execution of online redefinition add the following statement after the connect statement in Example 4-1, "Example of Online Redefinition" in the last section:

ALTER SESSION FORCE PARALLEL DML;