4 Using Oracle SecureFiles LOBs

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 SecureFiles LOB storage architecture and functionality, explicitly specify the storage parameter SECUREFILE. A SecureFiles LOB can only be created in a tablespace managed with Automatic Segment Space Management (ASSM).

For BasicFiles LOBs, specifying any of the SecureFiles LOB options results in an error.

CHUNK

CHUNK is one or more Oracle blocks. For SecureFiles LOBs, CHUNK is an advisory size and is provided for backward compatibility purposes. For BasicFiles LOBs, you may specify the chunk size when creating a table that stores LOBs; it corresponds to the data size used by Oracle Database when accessing or modifying the LOB value.

RETENTION

In Oracle Database Release 11g, this parameter specifies the retention policy. A value of MAX tells the system to keep old versions of LOB data blocks until the space used by the segment has reached the size specified in the MAXSIZE parameter. If MAXSIZE is not specified, MAX behaves like AUTO.

A value of MIN tells the system to keep old versions of LOB data blocks for the specified number of 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 BasicFiles LOBs, see "RETENTION Parameter for BasicFiles 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 blocks are taken from the pool of old versions of LOB data blocks regardless of time requirements and as needed.

FREEPOOLS

Specifies the number of FREELIST groups for BasicFiles LOBs, if the database is in automatic undo mode. Under 11g compatibility, this parameter is ignored when SecureFiles LOBs are created.

LOGGING, NOLOGGING, or FILESYSTEM_LIKE_LOGGING

Specify LOGGING if you want the creation of the LOB, and subsequent inserts into the LOB, 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 non-partitioned 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 the system only logs the metadata. This option is invalid for BasicFiles LOBs. This setting is similar to metadata journaling of file systems, which reduces mean time to recovery from failures. The LOGGING setting for SecureFiles LOBs is similar to the data journaling of file systems. Both the LOGGING and FILESYSTEM_LIKE_LOGGING settings provide a complete transactional file system with SecureFiles LOBs.

For SecureFiles LOBs, the NOLOGGING setting is converted internally to FILESYSTEM_LIKE_LOGGING.

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

See "LOGGING / NOLOGGING Parameter for BasicFiles LOBs" and "Ensuring Read Consistency".

FREELISTS or FREELIST GROUPS

This parameter specifies the number of process freelists or freelist groups, respectively, allocated to the segment; NULL for partitioned tables. Under 11g compatibility, these parameters are ignored when SecureFiles LOBs are created.

PCTVERSION

This parameter specifies the percentage of all used BasicFiles LOB data space that can be occupied by old versions of BasicFiles LOB data pages. Under 11g compatibility, this parameter is ignored when SecureFiles LOBs are created.

COMPRESS or NOCOMPRESS

The COMPRESS option turns on SecureFiles Intelligent Compression, and NOCOMPRESS turns it off. Note that setting table or index compression does not effect SecureFiles Intelligent Compression.

Note that you must have a license for the Oracle Advanced Compression Option before implementing SecureFiles Intelligent Compression. See Oracle Database Licensing Information for more information.

DEDUPLICATE or KEEP_DUPLICATES

The DEDUPLICATE option enables SecureFiles Intelligent Deduplication; it specifies that SecureFiles LOB data that is identical in two or more rows in a LOB column, partition or subpartition must share the same data blocks. The database combines SecureFiles LOBs with identical content into a single copy, reducing storage and simplifying storage management. The opposite of this option is KEEP_DUPLICATES.

Note that you must have a license for the Oracle Advanced Compression Option before implementing SecureFiles Intelligent Deduplication. See Oracle Database Licensing Information for more information.

ENCRYPT or DECRYPT

The ENCRYPT option turns on SecureFiles Intelligent Encryption, and encrypts all SecureFiles LOB data using Oracle Transparent Data Encryption (TDE). The DECRYPT options turns off SecureFiles Intelligent Encryption.

Note that you must have a license for the Oracle Advanced Security Option before implementing SecureFiles Intelligent Encryption. See Oracle Database Licensing Information for more information.

Usage Notes for CREATE TABLE Compression

• SecureFiles Intelligent Compressiondoes not enable table or index compression. Similarly, table and index compression does not enable SecureFiles Intelligent Compression.

• The LOW,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 compresses the data better. The default is MEDIUM.

  The LOW compression option introduces an extremely lightweight compression algorithm that removes the majority of the CPU cost that is typical with file compression. Compressed SecureFiles LOBs at the LOW level now provide a very efficient choice for SecureFiles LOB storage. SecureFiles LOBs compressed at LOW generally consume less CPU time than BasicFiles LOBs, consume less storage than BasicFiles LOBs, and typically help the application run faster because of a reduction in disk I/O.

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

• SecureFiles LOB compression is performed on the server and enables random reads and writes to LOB data. Compression utilities on the client, like utl_compress, cannot provide random access.

• The method DBMS_LOB.SETOPTIONS() can be used to enable and disable compression on individual SecureFiles LOBs. See "SETOPTIONS()"

• LOB compression is applicable only to SecureFiles LOBs.

Examples of CREATE TABLE Compression

The following examples demonstrate how to issue CREATE TABLE statements for specific compression scenarios.

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

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

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

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

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)
     );

Usage Notes for CREATE TABLE 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 LOBs.

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

Examples of CREATE TABLE Deduplication

The following examples demonstrate how to issue CREATE TABLE statements for specific deduplication scenarios.

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

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

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 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)
      );

Usage Notes for CREATE TABLE Encryption

• The current Transparent Data Encryption (TDE) syntax is used for enabling encryption on LOB data types. The LOB must be created with the SECUREFILE parameter.

• Encryption is performed at the block level.

• encrypt_algorithm indicates the name of the encryption algorithm. Valid algorithms are: AES192 (default), 3DES168, AES128, and 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 are encrypted.

• DECRYPT keeps the LOBs in clear text.

• LOBs can be encrypted only on a per-column basis, similar to TDE. All partitions within a LOB column are encrypted.

• Key management controls the ability to encrypt or decrypt.

• LOB encryption is allowed only with SecureFiles LOBs.

• TDE 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 information about creating and using Oracle wallet with TDE.

Examples of CREATE TABLE Encryption

The following examples demonstrate how to issue CREATE TABLE statements for specific encryption scenarios.

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

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 TABLE t1 ( a CLOB ENCRYPT IDENTIFIED BY foo)
    LOB(a) STORE AS SECUREFILE (
        CACHE
    );

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

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

RETENTION

Altering RETENTION only affects space created after the ALTER TABLE statement runs.

COMPRESS or NOCOMPRESS

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

DEDUPLICATE or 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 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.

ENCRYPT or DECRYPT

Enables or disables 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.

Usage Notes for ALTER TABLE Compression

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

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

• Compression may be specified either at the table level or the partition level.

• The LOW,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 compresses the data better. The default is MEDIUM. Decompression is simple and very fast. See "CREATE TABLE Compression".

• LOB compression applies only to SecureFiles LOBs.

Examples of ALTER TABLE Compression

The following examples demonstrate how to issue ALTER TABLE statements for specific compression scenarios.

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

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

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

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

Usage Notes for ALTER TABLE Deduplication

ALTER TABLE syntax can enable or disable LOB-level deduplication.

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

• 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 LOBs.

Examples of ALTER TABLE Deduplication

The following examples demonstrate how to issue ALTER TABLE statements for specific deduplication scenarios.

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

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

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

Usage Notes for ALTER TABLE Encryption

• ALTER TABLE is used to enable and disable LOB encryption for SecureFiles LOBs. This syntax also allows the user to re-key LOB columns with a new key or algorithm.

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

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

• The DECRYPT option converts encrypted columns to clear text 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 not encrypted.

• LOB encryption is applicable only to SecureFiles LOBs.

Examples of ALTER TABLE Encryption

The following examples demonstrate how to issue ALTER TABLE statements for specific encryption scenarios.

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

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

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

ALTER TABLE t1 REKEY USING '3DES168';

Online Redefinition Advantages

• No requirement 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 must be rebuilt

GETOPTIONS()

This function obtains the compression, deduplication and encryption settings of individual SecureFiles LOBs. An integer corresponding to a pre-defined constant based on the option type is returned.

Note that you cannot turn compression or deduplication on or off for an entire SecureFiles LOB column that has these features disabled.

See the Oracle Database PL/SQL Packages and Types Reference for more details on this function. See the Oracle Call Interface Programmer's Guide for more information on the corresponding OCI LOB function OCILobGetContentType().

SETOPTIONS()

This procedure sets compression, deduplication and encryption 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.

You cannot turn compression or deduplication on or off for a SecureFiles LOB column that does not have those features enabled. GETOPTIONS() and SETOPTIONS() work on individual SecureFiles LOBs. You can turn off a feature on a particular SecureFiles LOB and turn on a feature that has been turned off by SETOPTIONS(), but you cannot turn on an option that has not been given to the SecureFiles LOB when the table was created.

See the Oracle Database PL/SQL Packages and Types Reference for more details on this function. See the Oracle Call Interface Programmer's Guide for more information on the corresponding OCI LOB function OCILobSetContentType().

ISSECUREFILE()

This function returns TRUE if the LOB locator passed to it is for a SecureFiles LOB. It returns FALSE otherwise.

See the Oracle Database PL/SQL Packages and Types Reference for more details on this function.

MOVE_TO_DBFS_LINK()

MOVE_TO_DBFS_LINK() places the specified LOB data (from the database) into the DBFS HSM Store. If the LOB is stored, MOVE_TO_DBFS_LINK() silently returns as if the move was successful. In this case, if DBFS_LINK_NOCACHE is specified, or the default flags value is set, the LOB data is removed from the database.

Calling MOVE_TO_DBFS_LINK() multiple times on the same LOB with the same flags has no effect. Calling MOVE_TO_DBFS_LINK() on a LOB that is stored causes the LOB to be cached (MOVE_TO_DBFS_LINK_CACHE) or removed (DBFS_LINK_NOCACHE) according to the flags setting.

See Oracle Database PL/SQL Packages and Types Reference for more information.

COPY_FROM_DBFS_LINK()

COPY_FROM_DBFS_LINK() retrieves the specified LOB data from DBFS HSM Store to the database.

If the LOB is successfully retrieved, COPY_FROM_DBFS_LINK silently returns success.

See Oracle Database PL/SQL Packages and Types Reference for more information.

COPY_DBFS_LINK()

COPY_DBFS_LINK() results in a destination LOB referring to the same the DBFS path name as the source LOB without causing an intervening get. An optional flag parameter causes the LOB to be read into the destination.

See Oracle Database PL/SQL Packages and Types Reference for more information.

GET_DBFS_LINK()

GET_DBFS_LINK() returns the DBFS path name for the given LOB.

See Oracle Database PL/SQL Packages and Types Reference for more information.

SET_DBFS_LINK()

SET_DBFS_LINK links the specified LOB with the given DBFS path name.

GET_DBFS_LINK_STATE()

GET_DBFS_LINK_STATE retrieves the current link state for the specified LOB.

Returns the current link state of the specified LOB. If the LOB has never been linked, the state is set to DBFS_LINK_NEVER. If the LOB has been linked, the state is set to DBFS_LINK_YES. If the LOB has been retrieved from DBFS HSM Store, the state is set to DBFS_LINK_NO. If the LOB was linked, but the data was left in the database, cached is set to TRUE. Cached is set to FALSE if the data was removed after the link was created, and NULL if state is DBFS_LINK_NEVER. The pathname argument is set to the DBFS path name that is used to identify the LOB in the DBFS HSM Store.

See Oracle Database PL/SQL Packages and Types Reference for more information.

DBFS_LINK_GENERATE_PATHNAME()

DBFS_LINK_GENERATE_PATHNAME() returns a unique file path name for creating a DBFS Link.

Returns a globally unique file pathname that can be used for archiving. This is guaranteed to be globally unique across all calls to this function for different LOBs and versions of that LOB. It is always the same for the same LOB and version.

SETCONTENTTYPE()

SETCONTENTTYPE() sets the content type string for the data in the LOB.

To clear an existing contenttype associated with a SecureFiles LOB, invoke SETCONTENTTYPE() with contenttype set to an empty string.

See Oracle Database PL/SQL Packages and Types Reference for more information.

GETCONTENTTYPE()

GETCONTENTTYPE() gets the content type string for the data in the LOB if set.

If the SecureFiles LOB does not have a contenttype associated with it, GETCONTENTTYPE() returns NULL.

See Oracle Database PL/SQL Packages and Types Reference for more information.

APPEND()

If APPEND() is called on a SecureFiles LOB that is linked, an exception is thrown.

See Oracle Database PL/SQL Packages and Types Reference for more information.

COMPARE()

If COMPARE() is called on a LOB that is a DBFS Link, the linked LOB is streamed from the DBFS, if possible, otherwise an exception is thrown.

See Oracle Database PL/SQL Packages and Types Reference for more information.

CONVERTTOBLOB()

If CONVERTTOBLOB is called and the source LOB has been linked, an exception is thrown.

See Oracle Database PL/SQL Packages and Types Reference for more information.

CONVERTTOCLOB()

If CONVERTTOCLOB is called and the source LOB has been linked, an exception is thrown.

See Oracle Database PL/SQL Packages and Types Reference for more information.

COPY()

If the source LOB is linked, the data is streamed from the DBFS, if possible, otherwise an exception is thrown. If the destination LOB is linked, an exception is thrown.

See Oracle Database PL/SQL Packages and Types Reference for more information.

ERASE()

If the LOB to be erased is linked, an exception is thrown.

See Oracle Database PL/SQL Packages and Types Reference for more information.

FRAGMENT_DELETE()

If the LOB is linked, an exception is thrown.

See Oracle Database PL/SQL Packages and Types Reference for more information.

FRAGMENT_INSERT()

If the LOB is linked, an exception is thrown.

See Oracle Database PL/SQL Packages and Types Reference for more information.

FRAGMENT_MOVE()

If the LOB is linked, an exception is thrown.

See Oracle Database PL/SQL Packages and Types Reference for more information.

FRAGMENT_REPLACE()

If the LOB is linked, an exception is thrown.

See Oracle Database PL/SQL Packages and Types Reference for more information.

LOADBLOBFROMFILE()

If the BLOB is linked, an exception is thrown.

See Oracle Database PL/SQL Packages and Types Reference for more information.

LOADCLOBFROMFILE()

If the CLOB is linked, an exception is thrown.

See Oracle Database PL/SQL Packages and Types Reference for more information.

LOADFROMFILE()

If the LOB is linked, an exception is thrown.

See Oracle Database PL/SQL Packages and Types Reference for more information.

READ()

If the LOB is linked, the data is streamed from the DBFS, if possible, otherwise an exception is thrown.

See Oracle Database PL/SQL Packages and Types Reference for more information.

SUBSTR()

If the LOB is linked, the data is streamed from the DBFS, if possible, otherwise an exception is thrown.

See Oracle Database PL/SQL Packages and Types Reference for more information.

TRIM()

If the LOB is linked, an exception is thrown.

See Oracle Database PL/SQL Packages and Types Reference for more information.

WRITE()

If the LOB is linked, an exception is thrown.

See Oracle Database PL/SQL Packages and Types Reference for more information.

WRITEAPPEND()

If the LOB is linked, an exception is thrown.

See Oracle Database PL/SQL Packages and Types Reference for more information.

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. See Oracle Database PL/SQL Packages and Types Reference for more information.