Oracle® Database SecureFiles and Large Objects Developer's Guide 11g Release 1 (11.1) Part Number B28393-01 |
|
|
View PDF |
This chapter describes how to use SecureFiles, which were introduced to supplement the original LOBs implementation that is identified by the SQL parameter BASICFILE
. This chapter contains these topics:
This section summarizes LOB storage characteristics used to design tables with LOB column types. Beginning with Release 11.1, a new LOB storage paradigm is 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 above settings:
See Also:
"db_securefile"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
.
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 ] | 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:
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.
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.
For SECUREFILE
LOBs CHUNK
is an advisory size and is provided for backward compatibility purposes.
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:
See Also:
"RETENTION Parameter for BASICFILE LOBS"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.
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 nonpartitioned 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.
Note:
For LOB segments, with theNOLOGGING
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.Under 11g compatibility, these parameters are ignored when SECUREFILE
LOBs are being created.
Under 11g compatibility, these parameters are ignored when SECUREFILE
LOBs are being created.
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.
Turns on or turns off LOB encryption and optionally selects which of the encryption algorithms is to be used.
The option DEDUPLICATE
allows 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
.
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
allows 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 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) );
SecureFile compression does not entail table or index compression and vice-versa.
A server-wide default SecureFile compression algorithm is used.
MEDIUM
and HIGH
options provide varying degrees of compression. The higher the degree of compression, the higher the latency incurred. The HIGH
setting incurs more work, but will compress the data better. The default is MEDIUM
.
Compression can be specified at a partition level. The lob_storage_clause
allows specification for partitioned tables on a per-partition basis.
SecureFile compression is performed on the server-side and allows 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 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 );
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 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 cleartext.
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 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' CACHE NOLOGGING ) 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 );
You can modify LOB storage with an ALTER
TABLE
statement or with online redefinition by using the DBMS_REDEFINITION
package. 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.
See Also:
Oracle Database PL/SQL Packages and Types Reference, the DBMS_REDEFINITION
package.
Oracle Database SQL Language Reference ALTER
TABLE statement.
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) (LOB_deduplicate_clause [LOB_deduplicate_clause ] ) <LOB_deduplicate_clause> ::= { storage_clause | PCTVERSION integer | RETENTION [ { MAX | MIN integer | AUTO | NONE } ] | FREEPOOLS integer | REBUILD FREEPOOLS | LOB_deduplicate_clause | LOB_compression_clause | LOB_encryption_clause | { CACHE | { { NOCACHE | CACHE READS } [ logging_clause ] } } | allocate_extent_clause | shrink_clause | deallocate_unused_clause } [ storage_clause | PCTVERSION integer | RETENTION | FREEPOOLS integer | REBUILD FREEPOOLS | modify_LOB_sharing_clause | LOB_compression_clause | LOB_encryption_clause | { CACHE | { NOCACHE | CACHE READS } [ logging_clause ] } | shrink_clause | deallocate_unused_clause ]...
Where these keywords are defined for this statement as:
The option DEDUPLICATE
allows 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
.
Enables or disables LOB compression. All LOBs in the LOB segment are altered with the new setting.
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.
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.
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 );
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.
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
.
LOB compression is applicable only to SecureFiles.
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
is used to enable and disable LOB encryption for SECUREFILE
LOBs. This syntax also allows 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 cleartext 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 unencrypted.
LOB encryption is applicable only to SecureFiles.
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';
The new additions to these packages are outlined.
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.
The settings can be obtained using this function. An integer corresponding to a pre-defined constant based on the option type is returned.
These additions to the packages are for SECUREFILE
LOBs.
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.
The db_securefile
parameter is set in the file init.ora
:
Parameter Name: db_securefile
Parameter Type: text
Allowable Values: {
ALWAYS
| FORCE | PERMITTED
|
NEVER | IGNORE
}
Default Value: PERMITTED
Description: This parameter allows the database administrator to either allow SECUREFILE
LOBs to be created (PERMITTED
), disallow SECUREFILE
LOBs from being created going forward (NEVER
), force all LOBs created going forward to be SECUREFILE
LOBs (FORCE
), 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 FORCE
is specified, all LOBs created in the system will be created as SECUREFILE
LOBs. If the LOB is being created in an MSSM
tablespace, an error will be thrown. 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';
All features described in this document will be enabled with compatibility set to 11.0.0.0.0 or higher. There will be no downgrade capability after 11.0.0.0.0 is set.
The BASICFILE
LOB format is still supported under 11.0.0.0.0 compatibility.
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.
Note that Streams does not support SecureFiles in the current release.
The method of migrating LOBs columns is presented in this section.
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 is the only recommended method for migration of BasicFile LOBs to SecureFiles. It can be done at the table or partition level.
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/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) STORE AS SECUREFILE (NOCACHE NOLOGGING); 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;
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;