4 Using Oracle SecureFiles

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.

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:

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.

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:

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

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_SPACE Package

This procedure is for SecureFiles.

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.

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

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

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.

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:

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;