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

Part Number E10645-01
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
View PDF

4 Using Oracle SecureFiles

This chapter describes how to use SecureFiles, which were introduced to extend 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 was 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.

Oracle recommends that you enable compression, deduplication, or encryption at table creation time. Enabling these features using ALTER TABLE will cause the DDL to read/modify/write the entire LOB column holding a DDL lock the entire time during this potentially long operation

The SHRINK option is not supported for SecureFiles.

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 | LOW ]
  | 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 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 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.

  • 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 will compress the data better. The default is MEDIUM. LOW option uses LZO block-based lossless compression algorithm. Decompression is simple and very fast.

  • 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 CLOB column having the low level of compression.

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

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 clear text.

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

Oracle recommends that you enable compression, deduplication, or encryption at table creation time. Enabling these features using ALTER TABLE will cause the DDL to read/modify/write the entire LOB column holding a DDL lock the entire time during this potentially long operation.

The SHRINK option is not supported for SecureFiles.

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) ( modify_LOB_parameters )
 
<modify_LOB_parameters> ::=
{ storage_clause
| PCTVERSION integer
| FREEPOOLS integer
| REBUILD FREEPOOLS
| LOB_retention_clause
| LOB_deduplicate_clause
| LOB_compression_clause
| { ENCRYPT encryption_spec | DECRYPT }
| { CACHE 
  | { NOCACHE | CACHE READS } [ logging_clause ]
  }
| allocate_extent_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.

  • 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 will compress the data better. The default is MEDIUM. LOW option uses LZO block-based lossless compression algorithm. Decompression is simple and very fast.

  • LOB compression is applicable only to SecureFiles.

ALTER TABLE Examples for Compression

Enable low level compression.

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

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

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

Note:

Oracle Streams does not support SecureFiles that are deduplicated or have been written to using the DBMS_LOB.FRAGMENT_* operations.

PL/SQL Packages for SecureFiles

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.

You cannot turn compression or deduplication on or off for a SecureFile column that does not have those features on. GetOptions and SetOptions work on individual SecureFiles. You can turn off a feature on a particular SecureFile and turn on a feature that has already been turned off by SetOptions, but you cannot turn on an option that has not been given to the SecureFile when the table was created.

These OCI LOB functions for SecureFiles are available:

See Also:

Oracle Call Interface Programmer's Guide "LOB Functions" for descriptions of OCILobSetContentType() and OCILobGetContentType().

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 SecureFiles.

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.

Database File System Links

Database File System Links are references from SecureFiles to data stored outside of the segment in which the SecureFile resides. The path name given must reference a path available through the Oracle Database File System (DBFS) Content API.

This capability allows SecureFiles to be used to implement Hierarchical Storage Management (HSM) when used in conjunction with the DBFS Hierarchical Store (DBFS HS). HSM is a process by which the database moves less-used or unused data from faster, more expensive, and smaller storage to slower, cheaper, and larger stores.

Creating Database File System Links

Database File System Links require the creation of a Database File System through the use of the DBFS Content package (DBMS_DBFS_CONTENT).

There are two ways to create a DBFS Link. The first way moves the SecureFile data from the SecureFile into the specified DBFS path name and then stores a reference to that path name so that it can be retrieved at a later time.

To create a new DBFS Link, the application calls the DBMS_LOB.MOVE_TO_DBFS_LINK subprogram with the LOB and the DBFS path name. The data is copied from the SecureFile into the given DBMS file name, the file is created if it does not exist, the data is removed from the SecureFile, and the file path name is stored for later access through this LOB.

The second way is to copy or create a reference to an already existing DBFS file. You can copy a link from an already existing DBFS Link using the DBMS_LOB.COPY_DBFS_LINK subprogram. If there is any data in the destination SecureFile, that data is removed, and a copy of the reference to the link stored in the source SecureFile is made in the destination SecureFile. The other way to create a reference is to call the DBMS_LOB.SET_DBFS_LINK subprogram. With this method, it is assumed that the data for the link is already stored in the specified DBFS path name. Any data in the specified SecureFile is removed, and the DBFS path name is linked to.

For the database, it is also possible that a DBA may not want to store all of the data stored in a SecureFile HSM during export and import. Oracle has the capability to export and import only the Database File System Link. This link is a fully qualified identifier that when entered back into a SecureFile (or registered on a different database SecureFile) provides access to the stored data. This ability to export and import a link is similar in functionality to the common file system functionality of symbolic links.

The newly imported link is only available as long as the source (the stored) data is available, or until the first retrieval happens on the imported system. The retention of stored data is the responsibility of the application. If the application system removes data from the store that still has a reference to it, the database will throw an exception when the referencing SecureFile(s) attempt to access the data. Oracle also provides the capability to continue to keep the data in the database after migration out to a DBFS store as a cached copy. It is up to the application to purge these according to its retention policies.

Copying Database File System Links

The API DBMS_LOB.COPY_DBFS_LINK(DSTLOB, SRCLOB, FLAGS) provides the ability to copy a linked SecureFile. By default, the LOB will not be obtained from DBFS store during this operation; this is a copy-by-reference operation that is possible by making use of exporting (at source side) and importing (at destination side) the DBFS path name. The flags argument can dictate that the destination also has a local copy in the database as well as referencing the LOB data in DBFS store.

Copying a Linked LOB from one Table to Another

CREATE TABLE ... AS SELECT (CTAS) and INSERT TABLE ... AS SELECT (ITAS) will copy any DBFS Links that are stored in any SecureFile LOBs in the source table to the destination table.

Online Redefinition and DBFS Links

Online redefinition will copy any DBFS Links that are stored in any SecureFiles in the table being redefined.

Using DBFS Links

Constants:

-- Link State Values
dbfs_link_never          CONSTANT PLS_INTEGER     := 0;
dbfs_link_yes            CONSTANT PLS_INTEGER     := 1;
dbfs_link_no             CONSTANT PLS_INTEGER     := 2;
 
-- copy_dbfs_link/move_to_dbfs_link flags
dbfs_link_nocache        CONSTANT PLS_INTEGER     := 0;
dbfs_link_cache          CONSTANT PLS_INTEGER     := 1;
 
-- maximum 1-byte ASCII characters for contenttype
contenttype_max_size     CONSTANT PLS_INTEGER     := 128;
-- maximum length of DBFS pathnames
dbfs_link_path_max_size  CONSTANT PLS_INTEGER     := 1024;

DBMS_LOB.MOVE_TO_DBFS_LINK

MOVE_TO_DBFS_LINK will put (from the database) the specified LOB data to DBFS store. If the LOB is already 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 will be 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 already stored will cause the LOB to be cached (MOVE_TO_DBFS_LINK_CACHE) or removed (DBFS_LINK_NOCACHE) according to the flags setting.

PROCEDURE MOVE_TO_DBFS_LINK(
  lob_loc       IN OUT BLOB,
  storage_path  IN     VARCHAR2(dbfs_link_path_max_size),
  flags         IN     BINARY INTEGER 
                       DEFAULT DBFS_LINK_NOCACHE
);
 
PROCEDURE MOVE_TO_DBFS_LINK(
  lob_loc       IN OUT CLOB CHARACTER SET ANY_CS,
  storage_path  IN     VARCHAR2(dbfs_link_path_max_size),
  flags         IN     BINARY INTEGER 
                       DEFAULT DBFS_LINK_NOCACHE
);

Parameters:

lob_loc - The LOB to be stored in the DBFS.

storage_path - The path where the LOB will be be stored.

flags - Either DBFS_LINK_CACHE or DBFS_LINK_NOCACHE. If DBFS_LINK_CACHE is specified, the LOB data will continue to be stored in the database as well as being written to DBFS store. DBFS_LINK_NOCACHE specifies that the LOB data will be deleted from the database once written to DBFS.

Exceptions:

SECUREFILE_BADLOB if lob_loc is not a SecureFile.

DBMS_LOB.COPY_FROM_DBFS_LINK

COPY_FROM_DBFS_LINK will get (to the database) the specified LOB data from DBFS store.

If the LOB has already been retrieved, COPY_FROM_DBFS_LINK will silently return success.

PROCEDURE COPY_FROM_DBFS_LINK(
  lob_loc       IN OUT BLOB
);
PROCEDURE COPY_FROM_DBFS_LINK(
  lob_loc       IN OUT CLOB CHARACTER SET ANY_CS
);

Parameters:

lob_loc - The LOB to be retrieved from DBFS.

Exceptions:

ORA-01555 if the LOB has already been retrieved and has been modified since retrieval, if the LOB has been retrieved and stored (modified or not) since the locator was seleted, or the LOB has never been stored.

SECUREFILE_BADLOB if lob_loc is not a SecureFile.

DBMS_LOB.COPY_DBFS_LINK

COPY_DBFS_LINK will result in a destination LOB referring to the same DBFS path name as the source LOB without causing an intervening get. An optional flag parameter will result in the LOB being read into the destination.

PROCEDURE COPY_DBFS_LINK(
  lob_loc_dst    IN OUT BLOB,
  lob_loc_src    IN     BLOB,
  flags          IN     PLS_INTEGER DEFAULT DBFS_LINK_NOCACHE
);
PROCEDURE COPY_DBFS_LINK (
  lob_loc_dst    IN OUT CLOB CHARACTER SET ANY_CS,
  lob_loc_src    IN     CLOB CHARACTER SET ANY_CS,
  flags          IN     PLS_INTEGER DEFAULT DBFS_LINK_NOCACHE
);

Parameters:

lob_loc_dst - The LOB to be made to reference the same storage data as lob_loc_src .

lob_loc_src - The LOB to copy the reference from.

flags - Options to COPY_DBFS_LINK. DBFS_LINK_NOCACHE specifies to only copy the DBFS Link. DBFS_LINK_CACHE specifies to copy the DBFS Link and read the data into the database LOB specified by lob_loc_dst so that the data is cached.

Exceptions:

ORA-01555 if the source LOB has been retrieved, never stored, or if the LOB has been retrieved and stored (modified or not) since the locator was gotten. INVALID_ARGVAL if the lob_loc_src LOB has not been stored. SECUREFILE_BADLOB if lob_loc_dst is not a SecureFile.

DBMS_LOB.GET_DBFS_LINK

GET_DBFS_LINK returns the DBFS path name for the given LOB.

PROCEDURE GET_DBFS_LINK(
  lob_loc             IN     BLOB,
  storage_path        OUT VARCHAR2(dbfs_link_path_max_size),
  lob_length          OUT NUMBER
)
PROCEDURE GET_DBFS_LINK(
  lob_loc             IN     CLOB CHARACTER SET ANY_CS,
  storage_path        OUT VARCHAR2(dbfs_link_path_max_size),
  lob_length          OUT NUMBER
)

Parameters:

lob_loc - The LOB to be retrieved from DBFS.

storage_path - The path where the lob is stored in DBFS.

lob_length - The length of the lob at the time of write to DBFS.

Exceptions:

ORA-01555 if the LOB has been retrieved never linked, or if the LOB has been unlinked and linked since the locator was gotten.

INVALID_ARGVAL if the LOB has not been linked.

SECUREFILE_BADLOB if lob_loc is not a SecureFile.

DBMS_LOB.SET_DBFS_LINK

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

PROCEDURE SET_DBFS_LINK(
  lob_loc      IN OUT BLOB,
  pathname     IN     VARCHAR2(dbfs_link_path_max_size)
);
PROCEDURE SET_DBFS_LINK(
  lob_loc      IN OUT CLOB CHARACTER SET ANY_CS,
  pathname     IN     VARCHAR2(dbfs_link_path_max_size)
);

Parameters:

lob_loc - The LOB to linked.

pathname - DBFS file path name to link to.

Exceptions:

SECUREFILE_BADLOB if lob_loc is not a SecureFile.

DBMS_LOB.GET_DBFS_LINK_STATE

GET_DBFS_LINK_STATE will get the current link state for the specified LOB.

PROCEDURE GET_DBFS_LINK_STATE(
  lob_loc       IN BLOB,
  storage_path  OUT VARCHAR2(dbfs_link_path_max_size),
  state         OUT NUMBER,
 
  cached        OUT BOOLEAN
);

PROCEDURE GET_DBFS_LINK_STATE(
  lob_loc       IN CLOB CHARACTER SET ANY_CS
  pathname      OUT VARCHAR2(dbfs_link_path_max_size),
  state         OUT NUMBER,
  cached        OUT BOOLEAN
);

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

Parameters:

lob_loc - The LOB to be retrieved from DBFS.

pathname - The database-specific unique identifier.

state - One of DBFS_LINK_NEVER, DBFS_LINK_YES, or DBFS_LINK_NO.

cached - If the LOB is a link(state = DBFS_LINK_YES), and the data was retained by the database as a cache.

Exceptions:

SECUREFILE_BADLOB if lob_loc is not a SecureFile.

DBMS_LOB.DBFS_LINK_GENERATE_PATHNAME

DBFS_LINK_GENERATE_PATHNAME will return a unique file path name for use in creating a DBFS Link.

FUNCTION DBFS_LINK_GENERATE_PATH(
  lob_loc       IN BLOB,
  storage_dir   IN VARCHAR2
) RETURN VARCHAR2;
 
PROCEDURE DBFS_LINK_GENERATE_PATH(
  lob_loc       IN CLOB CHARACTER SET ANY_CS,
  storage_dir   IN VARCHAR2
) RETURN VARCHAR2;

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 will always be the same for the same LOB and version.

Parameters:

lob_loc - The LOB to be retrieved from DBFS.

storage_dir - The DBFS directory that will be the parent directory of the file.

Exceptions:

SECUREFILE_WRONGTYPE if lob_loc is not a SecureFile.

DBMS_LOB.SETCONTENTTYPE

SETCONTENTTYPE sets the content type string for the data in the LOB to something useful by an application.

PROCEDURE setcontenttype(
    lob_loc     IN OUT NOCOPY BLOB,
    contenttype IN            VARCHAR2
  );
PROCEDURE setcontenttype(
    lob_loc     IN OUT NOCOPY CLOB CHARACTER SET ANY_CS,
    contenttype IN            VARCHAR2 
  );

To clear an existing contenttype associated with a SecureFile, invoke SETCONTENTTYPE with contenttype set to an empty string.

Parameters:

lob_loc - The LOB to be assigned the content type

contenttype - The string to be assigned.

Exceptions:

SECUREFILE_BADLOB if lob_loc is not a SecureFile

DBMS_LOB.GETCONTENTTYPE

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

FUNCTION getcontenttype(
    lob_loc  IN BLOB
  ) RETURN VARCHAR2;
    PRAGMA RESTRICT_REFERENCES(getcontenttype, WNDS, RNDS, WNPS, RNPS);

  FUNCTION getcontenttype(
    lob_loc  IN CLOB CHARACTER SET ANY_CS
  ) RETURN VARCHAR2;
    PRAGMA RESTRICT_REFERENCES(getcontenttype, WNDS, RNDS, WNPS, RNPS);

If the SecureFile does not have a contenttype associated with it, GETCONTENTTYPE will return NULL.

Parameters:

lob_loc - The LOB whose content type is to be retrieved.

contenttype - The returned content type.

Exceptions:

SECUREFILE_BADLOB if lob_loc is not a SecureFile.

Changes to Existing Behavior of DBMS_LOB

Here are the changes to functions and procedures that existed before 11g release 2 (11.2):

DBMS_LOB.APPEND

If APPEND is called on a SecureFile that is linked, an exception will be thrown.

DBMS_LOB.COMPARE

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

DBMS_LOB.CONVERTTOBLOB/CONVERTTOCLOB

If CONVERTTOBLOB or CONVERTTOCLOB are called and the source LOB has been linked, an exception will be thrown.

DBMS_LOB.COPY

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

DBMS_LOB.ERASE

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

DBMS_LOB.FRAGMENT_*

If the LOB is linked, an exception will be thrown.

DBMS_LOB.LOAD*FROMFILE

If the LOB is linked, an exception will be thrown.

DBMS_LOB.READ

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

DBMS_LOB.SUBSTR

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

DBMS_LOB.TRIM

If the LOB is linked, an exception will be thrown.

DBMS_LOB.WRITE

If the LOB is linked, an exception will be thrown.

DBMS_LOB.WRITEAPPEND

If the LOB is linked, an exception will be thrown.

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 | 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), 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 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.2.0.0.0 or higher. There will be no downgrade capability after 11.2.0.0.0 is set.

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.

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