Oracle securefile function page 1/4

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.

SQL> alter SystemSetDb_securefile = 'force '; System altered. SQL> alter system set db_securefile = 'permitted '; System altered. SQL>

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.CodeTwo tables are created. The first is the original storage format, and the second is the securefile storage format.

CreateTableBf_tab ( ID number, Clob_data clob ) Lob (clob_data) store as basicfile; Insert into bf_tab values (1, 'My clobData'); Commit; Create Table sf_tab ( ID number, Clob_data clob ) Lob (clob_data) store as securefile; Insert into sf_tab values (1, 'My clob data '); Commit;

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.

Create Table Keep_duplicates_tab ( ID number, Clob_data clob ) Lob (clob_data) store as securefile keepdup_lob ( Keep_duplicates ); Create Table deduplicate_tab ( ID number, Clob_data clob ) Lob (clob_data) store as securefile dedup_lob ( Deduplicate ); Declare Rochelle clob: = rpad ('x', 10000, 'x '); Begin For I in 1000 Loop Insert into keep_duplicates_tab values (I, l_clob ); End loop; Commit ; For I in 1 .. 1000 Loop Insert into deduplicate_tab values (I, l_clob ); End loop; Commit; End; / Exec dbms_stats.gather_table_stats (user, 'keep _ duplicates_tab '); Exec dbms_stats.gather_table_stats (user, 'duplicate _ tab '); Column segment_name Format A30 Select segment_name, bytes From user_segments Where segment_name in ('keepdup _ lob', 'dup _ lob '); Segment_name bytes ---------------------------------------- Dedup_lob 262144 Keepdup_lob 19267584 2 rows selected. SQL>

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.