OracleSecureFile function page 1/4

OracleSecureFile

Oracle SecureFile Function

The SecureFile function is a completely re-designed implementation of the large object (LOB) storage format in oracle 11g. The original LOB storage format is now called BASIXFILE, and it is still the default storage method, however, the SECURFILE keyword enables a new storage method, which allows encryption, space saving by compression, and data duplication elimination.

　　Initialization parameters

The SecureFile function is available when the initialization parameter COMPATIBLE is set to 11.0.0.0.0 or higher.

The DB_SECUREFILE initialization parameter controls the default LOB storage format behavior of the database. The allowed values include:

◆ ALWAYS-all LOB objects in the ASSM tablespace are created in SecureFile LOB format, all LOB objects in non-ASSM tablespaces are created in the BasicFile LOB format (unless explicitly stated to be created in the SecureFile format), without specifying the option, the storage format option of BasicFile is ignored, and the default storage format option of SecureFile is used.

◆FORCE-All LOB objects are created in SecureFile LOB format. If a LOB is created in a non-ASSM tablespace, an error occurs. If no option is specified, the storage format option of BasicFile is ignored, and the default storage format option of SecureFile is used.

◆ PERMITTED-default setting. When the SECUREFILE keyword is used, it allows the storage format of SecureFile LOB. The default storage method is BASICFILE.

◆ NEVER-SecureFile LOB objects cannot be created.

◆ IGNORE-prevents SecureFile LOB from being created. All errors are ignored when the SecureFile storage option is used.

This parameter is dynamic, so it can use ALTERSYSTEMCommand settings.

The following example assumes that the DB_SECUREFILE initialization parameter is set to the default value PERMITTED.

Create SecureFile LOB

　Basic

SecureFile LOB is created by adding the SECUREFILE keyword after the LOB storage clause. The following Code shows that two tables are created. The first one uses the original storage format, the second is the SecureFile storage format.

LOB deduplication

The DEDUPLICATE option of SecureFile allows the elimination of duplicate data within a LOB at the table or partition level, as you expected, this technology and prevention of overwrite leads to an increase in system overhead, the KEEP_DUPLICATE option explicitly prevents repeated elimination. The following example compares the space usage of common SecureFile and deduplication SecureFile.

Note that the deduplication segments are much smaller, and space saving depends on the degree of repetition in the LOB segments. You can use the alter table command to reset the replication mode.