Oracle Securefile features 1th/4 page _oracle

The Securefile feature is a fully redesigned implementation of the large object (LOB) storage format in Oracle 11g, the original LOB storage format now known as Basixfile, which is still the default storage method, but the Securfile keyword opens a new storage method, It allows encryption, use of compression to save space and data de-duplication.

　　Initialization parameters

The Securefile feature is available when the initialization parameter compatible set me 11.0.0.0.0 or higher.

The Db_securefile initialization parameter controls the default behavior of the database to the LOB storage format, and the allowable values are:

ALWAYS-all LOB objects in the ASSM tablespace are created in a securefile lob format, and all LOB objects in a non-ASSM tablespace are created in Basicfile LOB format (unless explicitly indicated to be created in securefile format). In the absence of a specified option, the Basicfile storage format option is ignored and the Securefile default storage format option is used.

FORCE -all LOB objects are created in Securefile LOB format, an error occurs if a lob is created in a non-ASSM tablespace, basicfile storage format options are ignored without specifying an option, Securefile default storage format option is used.

Permitted-default setting, which allows securefile LOB storage format when the Securefile keyword is used, and the default storage method is Basicfile.

NEVER-Creating Securefile LOB objects is not allowed.

IGNORE-Prevents the creation of securefile lobs and ignores all errors when using Securefile storage options.

This parameter is dynamic, so it can be set using the Alter SYSTEM command.

sql> ALTER SYSTEM SET db_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

　Basis

The Securefile lob is created by adding the Securefile keyword after the LOB save clause, and the following code shows the creation of two tables, the first using the original storage format and the second using the Securefile storage format.

CREATE TABLE bf_tab ( ID number, Clob_data CLOB ) LOB (Clob_data) STORE as Basicfile; INSERT into Bf_tab VALUES (1, ' My CLOB data'); 　　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 Duplicate elimination

The securefile deduplicate option allows you to eliminate duplicate data within a single LOB on the table or partition level, as you would expect, this technique and the prevention override result in increased overhead, and the keep_duplicate option explicitly prevents duplication. The following examples compare the spatial use of common securefile and De-duplication securefile.

CREATETABLEKeep_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 L_clob Clob: = Rpad (' x ', 10000, ' X '); BEGIN For I in 1. 1000LOOP 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, ' deduplicate_tab '); COLUMN segment_nameFORMATA30 SELECT segment_name, bytes From User_segments WHERE segment_name in (' Keepdup_lob ', ' Dedup_lob '); Segment_name BYTES ------------------------------ ---------- Dedup_lob 262144 Keepdup_lob 19267584 2 rows selected. Sql>

Note that the de-duplication segment is much smaller and the space saver relies on the degree of repetition within the LOB segment, which can be reset using the ALTER TABLE command.

Current 1/4 page 1234 Next read the full text