Creating a New LOB Column

11.1 Creating a New LOB Column

You can provide the LOB storage characteristics when creating a LOB column using the CREATE TABLE statement or the ALTER TABLE ADD COLUMN statement.

For most users, default values for these storage characteristics are sufficient. However, if you want to fine-tune LOB storage, then consider the guidelines discussed in this section.

When defining LOBs in a table, you can explicitly indicate the tablespace and storage characteristics for each persistent LOB column. It is common to use separate tablespaces for large LOBs. SecureFiles is the default storage for LOBs, so the SECUREFILE keyword is optional, but is shown for clarity in the following example. The example assumes that TABLESPACE lobtbs1 is managed with ASSM, because SecureFile LOBs can only be created in tablespaces managed with Automatic Segment Space Management (ASSM).:

CREATE TABLE lobtab1 (n NUMBER, c CLOB)
      lob (c) STORE AS SECUREFILE sfsegname 
      ( TABLESPACE lobtbs1
        ENABLE STORAGE IN ROW 
        CACHE LOGGING
        RETENTION AUTO 
        COMPRESS
        STORAGE (MAXEXTENTS 5)
      );

To create a BasicFiles LOB, replace the SECUREFILE keyword with the BASICFILE keyword in the preceding example, and remove the COMPRESS keyword, which is specific to SecureFiles.

The data dictionary views USER_LOBS, ALL_LOBS, and DBA_LOBS provide information specific to a LOB column.

Note:

Oracle recommends Securefile LOBs for storing persistent LOBs, so this chapter focuses only on Securefile storage. All mentions of LOBs in the persistent LOB context is for Securefile LOBs, unless mentioned otherwise.

Note:

There are no tablespace or storage characteristics that you can specify for BFILEs as they are not stored in the database.

Assigning a LOB Data Segment Name

As shown in the previous example, specifying a name for the LOB data segment (sfsegname in the example) makes for a much more intuitive working environment. When querying the LOB data dictionary views USER_LOBS, ALL_LOBS, and DBA_LOBS, you see the LOB data segment that you chose instead of system-generated names.

11.1.1 CREATE TABLE BNF

The CREATE TABLE statement works with LOB storage using parameters that are specific to SecureFiles, BasicFiles LOB storage, or both.

The following is the syntax for CREATE TABLE in Backus Naur (BNF) notation, parts of which have been simplified to keep the focus on LOB-specific parameters.

11.1.2 ENABLE or DISABLE STORAGE IN ROW

LOB columns store locators that reference the location of the actual LOB value. This section describes how to enable or disable storage in a table row.

Actual LOB values are stored either in the table row (inline) or outside of the table row (out-of-line), depending on the column properties you specify when you create the table, and depending the size of the LOB. The ENABLE | DISABLE STORAGE IN ROW clause is used to indicate whether the LOB should be stored inline (in the row) or out-of-line. The default is ENABLE STORAGE IN ROW because it provides a performance benefit for small LOBs.

ENABLE STORAGE IN ROW

If ENABLE STORAGE IN ROW is set, the maximum amount of LOB data stored in the row is 4000 bytes. This includes the control information and the LOB value.

If the LOB is stored IN ROW,

Exadata pushdown is enabled for LOBs, including when using securefile compression and encryption.
In-Memory is enabled for LOBs without securefile compression and encryption.

LOBs larger than approximately 4000 bytes are stored out-of-line. However, the control information is still stored in the row, thus enabling us to read the out-of-line LOB data faster.

DISABLE STORAGE IN ROW

In some cases DISABLE STORAGE IN ROW is a better choice. This is because storing the LOB in the row increases the size of the row. This impacts performance if you are doing a lot of base table processing, such as full table scans, multi-row accesses (range scans), or many UPDATE/SELECT to columns other than the LOB columns.

11.1.3 CACHE, NOCACHE, and CACHE READS

This section discusses the guidelines to follow while creating tables that contain LOBs.

Use the cache options according to the guidelines in the following table:

Table 11-1 Using CACHE, NOCACHE, and CACHE READS Options

Cache Mode	Frequency of Read	Buffer Cache Behavior
`NOCACHE` (default)	Once or occasionally	LOB values are never brought into the buffer cache.
`CACHE READS`	Frequently	LOB values are brought into the buffer cache only during read operations and not during write operations.
`CACHE`	Read the LOB soon after write	LOB pages are placed in the buffer cache during both read and write operations. For storing semi-structured data consider turning on `CACHE` option.

Caution:

If your application frequently writes to LOBs, then using the CACHE option can potentially age other non-LOB pages out of the buffer cache prematurely.

11.1.4 LOGGING and FILESYSTEM_LIKE_LOGGING

You can apply the LOGGING parameter to LOBs in the same manner as you apply it for other table operations.

The default value of this parameter is LOGGING. For SecureFiles, the FILESYSTEM_LIKE_LOGGING parameter is equivalent to the NOLOGGING option.

If you set the LOGGING option, then Oracle Database determines the most efficient way to generate the REDO and UNDO logs for the change. Oracle recommends that you keep the LOGGING parameter turned on.

The FILESYSTEM_LIKE_LOGGING or the NOLOGGING option is useful for bulk loads and inserts. When loading data into the LOB, if you do not care about the REDO logs and can restart a failed load, then set the LOB data segment storage characteristics to FILESYSTEM_LIKE_LOGGING. This provides good performance for the initial load of data. Once you have completed loading data, Oracle recommends that you use the ALTER TABLE statement to modify the LOB storage characteristics for the LOB data segment for normal LOB operations. For example, set the cache option to CACHE or CACHE READS, along with the LOGGING option.

11.1.5 The RETENTION Parameter

The RETENTION parameter for SecureFile LOBs specifies how the database manages the old versions of the LOB data blocks.

Unlike other data types, the old versions of the LOB data blocks for SecureFile LOBs are stored in the LOB segment itself and are used to support consistent read operations. Without the corresponding old versions of the LOB data blocks, reading of a LOB at an earlier SCN may fail with ORA-1555. Set the RETENTION parameter as per the following guidelines:

Table 11-2 RETENTION parameter behavior

RETENTION Parameter value	Behavior
`MAX`	Allows the old versions of the LOB data blocks to fill the entire LOB segment. This minimizes the likelihood of an `ORA-1555`, if space usage is not a concern. With this setting, the old versions of the LOB data blocks may cause the LOB segment to grow. If you do not set the `MAXSIZE` attribute, then `MAX` behaves like `AUTO`.
`MIN`	Limits the retention of old versions of the LOB data blocks to `n` seconds. With this setting, you must also specify the retention duration in number of seconds as `n`. The old versions of the LOB data blocks may also cause the LOB segment to grow.
`AUTO`	Oracle Database manages the space as efficiently as possible, weighing both time and space needs.
NONE	Set this value if no old version of the LOB data blocks is required for consistent read purposes. This is the most efficient setting in terms of space utilization.
not set (sets to DEFAULT)	Uses the `UNDO_RETENTION` setting can be set dynamically or manually. If the `UNDO_RETENTION` parameter is set to a positive value, then it is equivalent to setting the `RETENTION` parameter to `MIN` with the same value for retention duration. If the `UNDO_RETENTION` parameter is set to zero (0), then it is equivalent to setting the `RETENTION` parameter to `NONE`.

The SHRINK feature for SecureFile LOBs partially deletes old versions of the LOB data blocks to free extents, regardless of the RETENTION parameter setting. Therefore, it is recommended to have the SHRINK feature only when the RETENTION parameter is set to NONE.

The following SQL code snippet helps you determine the RETENTION parameter for a LOB segment.

SELECT RETENTION_TYPE, RETENTION_VALUE FROM USER_LOBs WHERE ...;

11.1.6 SecureFiles Compression, Deduplication, and Encryption

This section discusses the features supported by SecureFiles in addition to those supported by BasicFiles.

SecureFiles LOB storage supports the following three features that are not available with the BasicFiles LOB storage option:

Compression
Deduplication
Encryption

Oracle recommends that you enable compression, deduplication, and encryption through the CREATE TABLE statement.

Caution:

Enabling table or column level compression or encryption does not compress or encrypt the LOB data. To compress or encrypt the LOB data, use SecureFiles compression or encryption by specifying it in the LOB_storage_clause.

Note:

You can enable the compression, deduplication, and encryption features using the ALTER TABLE statement. However, if you enable these features using the ALTER TABLE statement, then all the data in the SecureFiles LOB storage is read, modified, and written. This can cause the database to lock the table during a potentially lengthy operation. There are online capabilities in the ALTER TABLE statement that can help you avoid this issue.

Advanced LOB Compression

Advanced LOB Compression transparently analyzes and compresses SecureFiles LOB data to save disk space and improve performance.

License Requirement: You must have a license for the Oracle Advanced Compression Option to implement Advanced LOB Compression.

Consider the following issues when using the CREATE TABLE statement with Advanced LOB Compression:

Advanced 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.
Advanced LOB Compression does not enable table or index compression. Conversely, table and index compression do not enable Advanced LOB 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 uses 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 provide a very efficient choice for SecureFiles LOB storage. SecureFiles LOBs compressed at LOW generally consume less CPU time and 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 CREATE TABLE lob_storage_clause enables specification of compression for partitioned tables on a per-partition basis.
The DBMS_LOB.SETOPTIONS procedure can enable and disable compression on individual SecureFiles LOBs.

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

Example 11-2 Creating a SecureFiles LOB Column with LOW Compression

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

Example 11-3 Creating a SecureFiles LOB Column with MEDIUM (default) Compression

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

Example 11-4 Creating a SecureFiles LOB Column with HIGH Compression

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

Example 11-5 Creating a SecureFiles LOB Column with Disabled Compression

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

Example 11-6 Creating a SecureFiles LOB Column with Compression on One Partition

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

Advanced LOB Deduplication

Advanced LOB Deduplication enables Oracle Database to automatically detect duplicate LOB data within a LOB column or partition, and conserve space by storing only one copy of the data.

License Requirement: You must have a license for the Oracle Advanced Compression Option to implement Advanced LOB Deduplication.

Consider these issues when using CREATE TABLE and Advanced LOB Deduplication.

Identical LOBs are good candidates for deduplication. Copy operations can avoid data duplication by enabling deduplication.
Duplicate detection happens within a LOB segment. Duplicate detection does not span partitions or subpartitions for partitioned and subpartitioned LOB columns.
Deduplication can be specified at a partition level. The CREATE TABLE lob_storage_clause enables specification for partitioned tables on a per-partition basis.
The DBMS_LOB.SETOPTIONS procedure can enable or disable deduplication on individual LOBs.

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

Example 11-7 Creating a SecureFiles LOB Column with Deduplication

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

Example 11-8 Creating a SecureFiles LOB Column with Disabled Deduplication

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

Example 11-9 Creating a SecureFiles LOB Column with Deduplication on One Partition

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

Example 11-10 Creating a SecureFiles LOB column with Deduplication Disabled on One Partition

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

SecureFiles Encryption

SecureFiles Encryption introduces a new encryption facility for LOBs. The data is encrypted using Transparent Data Encryption (TDE), which allows the data to be stored securely, and still allows for random read and write access.

License Requirement: You must have a license for the Oracle Advanced Security Option to implement SecureFiles Encryption.

Consider the following issues when using CREATE TABLE statement with SecureFiles Encryption:

Securefile Encryption encrypts the data stored in the SecureFile LOB column, irrespective of whether the data is stored in-row or out-of-line in the LOB segment. Note that table or column level encryption will not encrypt the data stored out-of-line in the LOB segment.
SecureFile Encryption relies on a wallet, or Hardware Security Model (HSM), to hold the encryption key. The wallet setup is the same as that described for Transparent Data Encryption (TDE) and Tablespace Encryption, so complete that before using SecureFile encryption.

See Also:

"Oracle Database Advanced Security Guide for information about creating and using Oracle wallet with TDE.
The encrypt_algorithm indicates the name of the encryption algorithm. Valid algorithms are: AES192 (default), AES128, and AES256.
The column encryption key is derived from PASSWORD, if specified.
The default for LOB encryption is SALT. NO SALT is not supported.
SecureFile Encryption is only supported at the table level on a per-column basis, and not at the per-partition level. Hence all partitions within a LOB column are encrypted.
DECRYPT keeps the LOBs in clear text.
Key management controls the ability to encrypt or decrypt.
TDE is not supported by the traditional import and export utilities or by transportable-tablespace-based export. Use the Data Pump expdb and impdb utilities with encrypted columns instead.

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

Example 11-11 Creating a SecureFiles LOB Column with a Specific Encryption Algorithm

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

Example 11-12 Creating a SecureFiles LOB column with encryption for 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)
);

Example 11-13 Creating a SecureFiles LOB Column with Encryption Based on a Password Key

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_encryption_clause section of the statement:

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

Example 11-14 Creating a SecureFiles LOB Column with Disabled Encryption

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

11.1.7 BasicFile Specific Parameters

This section discusses the storage parameters specific to BasicFiles.

The following storage parameters are specific to BasicFiles:

Caution:

Oracle strongly recommends that you use SecureFile LOBs for all your LOB needs.

PCTVERSION

When a BasicFiles LOB is modified, a new version of the BasicFiles LOB page is produced in order to support consistent read operations of prior versions of the BasicFiles LOB value. The PCTVERSION parameter is the percentage of all used BasicFiles LOB data space that can be occupied by old versions of BasicFiles LOB data pages. As soon as old versions of BasicFiles LOB data pages start to occupy more than the PCTVERSION amount of used BasicFiles LOB space, Oracle Database tries to reclaim the old versions and reuse them. The PCTVERSION parameter has the following preset values:

Default: 10%
Minimum: 0
Maximum: 100

If your application requires several BasicFiles LOB updates that are concurrent with heavy reads of BasicFiles LOB columns, then consider using a higher value for the PCTVERSION parameter, such as 20%. If persistent BasicFiles LOB instances in your application are created and written just once and are primarily read-only afterward, then updates are infrequent. In this case, consider using a lower value for the PCTVERSION parameter, such as 5% or lower. If existing BasicFiles LOBs are known to be read-only, then you can safely set the PCTVERSION parameter to 0% because there will never be any pages needed for old versions of data.

Note:

The PCTVERSION parameter and the RETENTION parameter are mutually exclusive for BasicFiles LOBs, that is, you can specify either the PCTVERSION parameter or the RETENTION parameter, but not both.

CHUNK

A chunk is one or more Oracle blocks. You can specify the chunk size for the BasicFiles LOB when creating the table that contains the LOB. This corresponds to the data size used by Oracle Database when accessing or modifying the LOB value. Part of the chunk is used to store system-related information and the rest stores the LOB value. The APIs that you use to retrieve the chunk size, return the amount of space used in the LOB chunk to store the LOB value. You can use the following APIs to retrieve the chunk size:

The DBMS_LOB.GETCHUNKSIZE procedure in PL/SQL
The OCILobGetChunkSize() function in OCI

Once you specify the value of the CHUNK parameter (when the LOB column is created), you cannot change it without moving the LOB. You can set the CHUNK parameter to the data size most frequently accessed or written. It is more efficient to access LOBs in big chunks. If you explicitly specify storage characteristics for the LOB, then make sure that you set the INITIAL parameter and the NEXT parameter for the LOB data segment storage to a size that is larger than the CHUNK size.

For SecureFiles, the CHUNK size is an advisory size and is provided for backward compatibility purposes.

FREEPOOLS

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

FREELISTS or FREELIST GROUPS

Specifies the number of process freelists or freelist groups, respectively, allocated to the segment; NULL for partitioned tables. Under Release 12c compatibility, these parameters are ignored when SecureFiles LOBs are created.

11.1.8 Restriction on First Extent of a LOB Segment

This section discusses the first extent requirements on SecureFiles and BasicFiles.

First Extent of a SecureFile LOB Segment

A SecureFile LOB segment can only be created in Locally Managed Tablespace with Automatic Segment Space Management (ASSM). The number of blocks required in the first extent depends on the release. Before 21c, the first extent requires at least 16 blocks. After 21c, the number is 32 if the compatible parameter is greater than or equal to 20.1.0.0.0. Segments created in the previous release will continue to work in the new release. However, they will not be automatically upgraded.

The actual size of the first extent depends on the database block_size. If the tablespace is configured to use uniform extent, the extent must be bigger than the aforementioned number. For example, with block_size = 8k, the uniform extent size must be at least 128K pre-21c, or 256K on 21c with compatible parameter set. If the tablespace is configured to use uniform extent that is less than this number, the LOB segment creation will fail.

First Extent of a BasicFile LOB Segment

A BasicFile LOB segment can be created in Dictionary Managed or Locally Managed Tablespaces. The segment requires at least 3 blocks in the first extent. This translates into different extent sizes based on the database block_size. If the tablespace is configured to use uniform extent that contains fewer than 3 blocks, the LOB segment creation will fail.

11.1.9 Summary of CREATE TABLE LOB Storage Parameters for Securefile LOBs

The table in this section summarizes the parameters of the CREATE TABLE statement that relate to Securefile LOB storage.

Table 11-3 Parameters of CREATE TABLE Statement Related to LOBs

Parameter	Description
`SECUREFILE`	Specifies SecureFiles LOBs storage. Starting with Oracle Database 12c, the SecureFiles LOB storage type, specified by the parameter `SECUREFILE`, is the default. A SecureFiles LOB can only be created in a tablespace managed with Automatic Segment Space Management (ASSM).
`BASICFILE`	Specifies BasicFiles LOB storage, the original architecture for LOBs. You must explicitly specify the parameter `BASICFILE` to use the BasicFiles LOB storage type. For BasicFiles LOBs, specifying any of the SecureFiles LOB options results in an error.
`RETENTION`	Specifies the retention policy for storing old versions of LOB data to support consistent read. Possible values are: `MAX`, `MIN`, `AUTO` and `NONE`.
`MAXSIZE`	Specifies the upper limit of storage space that a LOB may use. If this amount of space is consumed, new LOB data blocks are taken from the pool of old versions of LOB data blocks as needed, regardless of time requirements.
`CACHE, NOCACHE, CACHE READS`	Specifies when the LOB data in brought into the buffer cache. `NOCACHE:` Never brought into buffer cache. `CACHE READS:` Only during reads. `CACHE:` During reads and writes. The default is `NOCACHE`.
`LOGGING`, `NOLOGGING`, or `FILESYSTEM_LIKE_LOGGING`	Specifies whether to generate REDO and UNDO for changes to the LOB: `LOGGING:` Generate REDO and UNDO for the change `FILESYSTEM_LIKE_LOGGING/NOLOGGING:` Log only the metadata. The default is `LOGGING.`
`COMPRESS` or `NOCOMPRESS`	The `COMPRESS` option turns on Advanced LOB Compression, and `NOCOMPRESS` turns it off. The default is `NOCOMPRESS`.
`DEDUPLICATE` or `KEEP_DUPLICATES`	The `DEDUPLICATE` option enables Advanced LOB 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`. The default is `KEEP_DUPLICATES`.
`ENCRYPT` or `DECRYPT`	The `ENCRYPT` option turns on SecureFiles Encryption, and encrypts all SecureFiles LOB data using Oracle Transparent Data Encryption (TDE). The `DECRYPT` options turns off SecureFiles Encryption. The default is `DECRYPT`.