Using Oracle SecureFiles LOBs
Using Oracle SecureFiles LOBs
From：http://docs.oracle.com/cd/E11882_01/appdev.112/e18294/adlob_smart.htm#ADLOB45227
近日將OCP11g的題庫拿出來研究了下，題中涉及的大量與10g差異性的特性，本人從官方網站上一一謄抄下來，與11g OCP相關的部分已做了不同顏色處理，希望對正在考11gOCP或者升級考試的同仁有用。
This chapter describes how to use SecureFiles LOBs, which were introduced to extend the original BasicFiles LOB implementation.
This chapter contains these topics:
l Using CREATE TABLE with SecureFiles LOBs
l Using ALTER TABLE with SecureFiles LOBs
l Initialization Parameter db_securefile for SecureFiles LOBs
l Migrating Columns from BasicFiles LOBs to SecureFiles LOBs
l PL/SQL Packages for SecureFiles LOBs and DBFS
About SecureFiles LOBs
Beginning with Oracle Database 11g Release 1, Oracle introduced SecureFiles, a new LOB storage architecture. SecureFiles LOBs are created when the storage keyword SECUREFILE appears in the CREATE TABLE statement. The original LOB storage architecture, BasicFiles LOBs, is the default storage. It is in effect if the keyword SECUREFILE is not used, or if the keyword BASICFILE is used in the CREATE TABLE statement.
The following sections describe LOB storage characteristics used when designing, creating, and modifying tables with LOB column types. The database administrator may use the db_securefile initialization parameter in the init.ora file to modify initial settings.
Oracle recommends that you enable compression, deduplication, or encryption through the CREATE TABLE statement. If you enable these features through the ALTER TABLE statement, all SecureFiles LOB data in the table is read, modified, and written; this causes the database to lock the table during a potentially(潛在的) lengthy operation.
This section contains the following topics:
l About Compression
SecureFiles Intelligent Compression, available with the Oracle Advanced Compression Option, seamlessly analyzes SecureFiles LOB data and compresses to save disk space.
Note that you must have a license for the Oracle Advanced Compression Option before implementing SecureFiles Intelligent Compression. See for more information.
About Deduplication
SecureFiles Intelligent Deduplication, available with the Oracle Advanced Compression Option, enables Oracle Database to automatically detect duplicate LOB data within a LOB column or partition, and conserve space by storing only one copy of the data.
Note
that you must have a license for the Oracle Advanced Compression Option before implementing SecureFiles Intelligent Deduplication.
See：
for more information.
Note
also that Oracle Streams does not support SecureFiles LOBs that are deduplicated.
About Encryption
SecureFiles Intelligent Encryption, available with the Oracle Advanced Security Option, introduces a new encryption facility for LOBs. The data is encrypted using Transparent Data Encryption (TDE), which allows the data to be stored securely, and still allows for random read and write access.
Note
that you must have a license for the Oracle Advanced Security Option before implementing SecureFiles Intelligent Encryption. See for more information.
Using CREATE TABLE with SecureFiles LOBs
A SecureFiles LOB can only be created in a tablespace managed with Automatic Segment Space Management (ASSM). The following parameter descriptions apply to the LOB storage paradigm using the SECUREFILE parameter. See , CREATE TABLE statement, for further details.
Oracle recommends that you enable compression, deduplication, or encryption through the CREATE TABLE statement. If you enable these features through the ALTER TABLE statement, all SecureFiles LOB data in the table is read, modified, and written; this causes the database to lock the table during a potentially lengthy operation.
The SHRINK option is not supported for SecureFiles LOBs.
SecureFiles LOBs introduce new storage parameters. These are introduced in the BNF of CREATE TABLE, in Example 4-1. Full description of the parameters is in"Parameters of CREATE TABLE for SecureFiles LOB".
Example 4-1 BNF for CREATE TABLE
Keywords are in bold.
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 ] ;
{ 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 data_type [ SORT ]
[ DEFAULT expr ]
[ ENCRYPT encryption_spec ]
[ ( inline_constraint [ inline_constraint ] ... )
| inline_ref_constraint
]
{ Oracle_built_in_datatypes
| ANSI_supported_datatypes
| user_defined_types
| Oracle_supplied_types
}
{ character_datatypes
| number_datatypes
| long_and_raw_datatypes
| datetime_datatypes
| large_object_datatypes
| rowid_datatypes
}
{ BLOB | CLOB | NCLOB| BFILE }
[ column_properties ]
[ table_partitioning_clauses ]
[ CACHE | NOCACHE ]
[ parallel_clause ]
[ ROWDEPENDENCIES | NOROWDEPENDENCIES ]
[ enable_disable_clause ]
[ enable_disable_clause ]...
[ row_movement_clause ]
[ AS subquery ]
{ 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
}
]...
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
{ (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)
}
}
{ TABLESPACE tablespace
| { LOB_parameters [ storage_clause ]
}
| storage_clause
}
[ TABLESPACE tablespace
| { LOB_parameters [ storage_clause ]
}
]...
[ { 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 | NOLOGGING | FILESYSTEM_LIKE_LOGGING }
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 }
]...
)
{ DEDUPLICATE
| KEEP_DUPLICATES
}
{ COMPRESS [ HIGH | MEDIUM | LOW ]
| NOCOMPRESS }
{ ENCRYPT [ USING 'encrypt_algorithm' ]
[ IDENTIFIED BY password ]
| DECRYPT
}
XMLTYPE [ COLUMN ] column
[ XMLType_storage ]
[ XMLSchema_spec ]
STORE AS
{ OBJECT RELATIONAL
| [ SECUREFILE | BASICFILE ] { CLOB | BINARY XML }
[ { LOB_segname [ (LOB_parameters) ]
| LOB_parameters
}
]
VARRAY varray_item
{ [ substitutable_column_clause ]
STORE AS [ SECUREFILE | BASICFILE ] LOB
{ [ LOB_segname ] (LOB_parameters)
| LOB_segname
}
| substitutable_column_clause
}
Parameters of CREATE TABLE for SecureFiles LOB
Table 4-1 summarizes the parameters of the CREATE TABLE statement.
Table 4-1 Parameters of CREATE TABLE Statement
|
Parameter |
Description |
|
Parameter that specifies the original architecture for LOBs. It creates BasicFiles LOBs, which do not support compression, deduplication or encryption features. |
|
|
Parameter that specifies SecureFiles LOBs, an architecture that improves performance and also supports compression, deduplication, and encryption features. |
|
|
Data size used by Oracle Database when accessing or modifying the LOB. This is used for BasicFiles LOBs; for SecureFiles LOBs it is an advisory size provided for backward compatibility. |
|
|
Configures the LOB column to store old versions of LOB data in a specified manner. |
|
|
Upper limit of storage space that may be used by a LOB. |
|
|
Specifies the number of FREELIST groups for BasicFiles LOBs, if the database is in automatic undo mode. Not used for SecureFiles LOBs. |
|
|
Logging options. |
|
|
Specifies the number of process freelists or freelist groups, respectively, allocated to the segment;NULL for partitioned tables. Not used for SecureFiles LOBs. |
|
|
Specifies the percentage of all used BasicFiles LOB data space that may be occupied by old versions of the LOB data pages. Not used for SecureFiles LOBs. |
|
|
Turns on or turns off SecureFiles Intelligent Compression. |
|
|
Turns on or turns off SecureFiles Intelligent Deduplication. |
|
|
Turns on or turns off SecureFiles Intelligent Encryption. |
l BASICFILE
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.
l SECUREFILE
To use the SecureFiles LOB storage architecture and functionality, explicitly specify the storage parameter SECUREFILE. A SecureFiles LOB can only be created in a tablespace managed with Automatic Segment Space Management (ASSM).
For BasicFiles LOBs, specifying any of the SecureFiles LOB options results in an error.
l CHUNK
CHUNK is one or more Oracle blocks. For SecureFiles LOBs, CHUNK is an advisory size and is provided for backward compatibility purposes. For BasicFiles LOBs, you may specify the chunk size when creating a table that stores LOBs; it corresponds to the data size used by Oracle Database when accessing or modifying the LOB value.
l RETENTION
In Oracle Database Release 11g, this parameter specifies the retention policy. A value of MAX tells the system to keep old versions of LOB data blocks until the space used by the segment has reached the size specified in the MAXSIZE parameter. If MAXSIZE is not specified, MAX behaves like AUTO.
A value of MIN tells the system to keep old versions of LOB data blocks for the specified number of 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 BasicFiles LOBs, see "RETENTION Parameter for BasicFiles LOBs".
l MAXSIZE
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 blocks are taken from the pool of old versions of LOB data blocks regardless of time requirements and as needed.
l FREEPOOLS
Specifies the number of FREELIST groups for BasicFiles LOBs, if the database is in automatic undo mode. Under 11g compatibility, this parameter is ignored when SecureFiles LOBs are created.
l LOGGING, NOLOGGING, or FILESYSTEM_LIKE_LOGGING
Specify LOGGING if you want the creation of the LOB, and subsequent(後來的、併發的) inserts into the LOB, 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 non-partitioned 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 the system only logs the metadata. This option is invalid for BasicFiles LOBs. This setting is similar to metadata journaling of file systems, which reduces mean time to recovery from failures. The LOGGING setting for SecureFiles LOBs is similar to the data journaling of file systems. Both the LOGGING and FILESYSTEM_LIKE_LOGGING settings provide a complete transactional file system with SecureFiles LOBs.
For SecureFiles LOBs, the NOLOGGING setting is converted internally to FILESYSTEM_LIKE_LOGGING.
FILESYSTEM_LIKE_LOGGING ensures that data is completely recoverable after a server failure.
See "LOGGING / NOLOGGING Parameter for BasicFiles LOBs" and "Ensuring Read Consistency".
Caution:
For LOB segments, if using NOLOGGING and FILESYSTEM_LIKE_LOGGING settings, it is possible for data to be changed on the disk during a backup operation. This results 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.
l FREELISTS or FREELIST GROUPS
This parameter specifies the number of process freelists or freelist groups, respectively, allocated to the segment; NULL for partitioned tables. Under 11g compatibility, these parameters are ignored when SecureFiles LOBs are created.
l PCTVERSION
This parameter specifies the percentage of all used BasicFiles LOB data space that can be occupied by old versions of BasicFiles LOB data pages. Under 11g compatibility, this parameter is ignored when SecureFiles LOBs are created.
l COMPRESS or NOCOMPRESS
The COMPRESS option turns on SecureFiles Intelligent Compression, and NOCOMPRESS turns it off. Note that setting table or index compression does not effect SecureFiles Intelligent Compression.
Note that you must have a license for the Oracle Advanced Compression Option before implementing SecureFiles Intelligent Compression. See for more information.
l DEDUPLICATE or KEEP_DUPLICATES
The DEDUPLICATE option enables SecureFiles Intelligent Deduplication; it specifies that SecureFiles LOB data that is identical in two or more rows in a LOB column, partition or subpartition must share the same data blocks. The database combines SecureFiles LOBs with identical content into a single copy, reducing storage and simplifying storage management. The opposite of this option is KEEP_DUPLICATES.
Note that you must have a license for the Oracle Advanced Compression Option before implementing SecureFiles Intelligent Deduplication. See for more information.
l ENCRYPT or DECRYPT
The ENCRYPT option turns on SecureFiles Intelligent Encryption, and encrypts all SecureFiles LOB data using Oracle Transparent Data Encryption (TDE). The DECRYPT options turns off SecureFiles Intelligent Encryption.
Note that you must have a license for the Oracle Advanced Security Option before implementing SecureFiles Intelligent Encryption. See for more information.
? CREATE TABLE Compression
This section discusses SecureFiles Intelligent Compression iwhen used in the CREATE TABLE statement. This section contains the following topics:
l Usage Notes for CREATE TABLE Compression
l Examples of CREATE TABLE Compression
l Usage Notes for CREATE TABLE Compression
SecureFiles Intelligent Compression does not enable table or index compression. Similarly, table and index compression does not enable SecureFiles Intelligent Compression.
The 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 compresses the data better. The default is MEDIUM.
The LOW compression option introduces an extremely lightweight compression algorithm that removes the majority of the CPU cost that is typical with file compression. Compressed SecureFiles LOBs at the LOW level now provide a very efficient choice for SecureFiles LOB storage. SecureFiles LOBs compressed at LOW generally consume less CPU time than BasicFiles LOBs, consume less storage than BasicFiles LOBs, and typically help the application run faster because of a reduction in disk I/O.
Compression can be specified at the partition level. The lob_storage_clause enables specification of compression for partitioned tables on a per-partition basis.
SecureFiles LOB compression is performed on the server and enables random reads and writes to LOB data. Compression utilities on the client, like utl_compress, cannot provide random access.
The method DBMS_LOB.SETOPTIONS() can be used to enable and disable compression on individual SecureFiles LOBs. See "SETOPTIONS()"
LOB compression is applicable only to SecureFiles LOBs.
Examples of CREATE TABLE Compression
The following examples demonstrate how to issue CREATE TABLE statements for specific compression scenarios.
Example 4-2 Creating a SecureFiles LOB column with LOW compression
CREATE TABLE t1 (a CLOB)
LOB(a) STORE AS SECUREFILE(
COMPRESS LOW
CACHE
NOLOGGING
);
Example 4-3 Creating a SecureFiles LOB column with MEDIUM (default) compression
CREATE TABLE t1 ( a CLOB)
LOB(a) STORE AS SECUREFILE (
COMPRESS
CACHE
NOLOGGING
);
Example 4-4 Creating a SecureFiles LOB column with HIGH compression
CREATE TABLE t1 ( a CLOB)
LOB(a) STORE AS SECUREFILE (
COMPRESS HIGH
CACHE
);
Example 4-5 Creating a SecureFiles LOB column with disabled compression
CREATE TABLE t1 ( a CLOB)
LOB(a) STORE AS SECUREFILE (
NOCOMPRESS
CACHE
);
Example 4-6 Creating a SecureFiles LOB column with compression on one partition
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)
);
l CREATE TABLE Deduplication
This section discusses SecureFiles LOB deduplication when used in the CREATE TABLE statement. This section contains the following topics:
Usage Notes for CREATE TABLE Deduplication
Examples of CREATE TABLE Deduplication
Usage Notes for CREATE TABLE 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 LOBs.
DBMS_LOB.SETOPTIONS can be used to enable or disable deduplication on individual LOBs.
Examples of CREATE TABLE Deduplication
The following examples demonstrate how to issue CREATE TABLE statements for specific deduplication scenarios.
Example 4-7 Creating a SecureFiles LOB column with deduplication
CREATE TABLE t1 ( a CLOB)
LOB(a) STORE AS SECUREFILE (
DEDUPLICATE
CACHE
);
Example 4-8 Creating a SecureFiles LOB column with disabled deduplication
CREATE TABLE t1 ( a CLOB)
LOB(a) STORE AS SECUREFILE (
KEEP_DUPLICATES
CACHE
);
Example 4-9 Creating a SecureFiles LOB column with deduplication on one partition
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)
);
Example 4-10 Creating a SecureFiles LOB column with deduplication disabled on one partition
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)
);
l CREATE TABLE Encryption
This section discusses SecureFiles LOB encryption when used in the CREATE TABLE statement. This section contains the following topics:
Usage Notes for CREATE TABLE Encryption
Examples of CREATE TABLE Encryption
Usage Notes for CREATE TABLE Encryption
The current Transparent Data Encryption (TDE) syntax is used for enabling encryption on LOB data types. The LOB must be created with the SECUREFILE parameter.
Encryption is performed at the block level.
encrypt_algorithm indicates the name of the encryption algorithm. Valid algorithms are: AES192 (default), 3DES168, AES128, and 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 are encrypted.
DECRYPT keeps the LOBs in clear text.
LOBs can be encrypted only on a per-column basis, similar to TDE. All partitions within a LOB column are encrypted.
Key management controls the ability to encrypt or decrypt.
LOB encryption is allowed only with SecureFiles LOBs.
TDE 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 information about creating and using Oracle wallet with TDE.
Examples of CREATE TABLE Encryption
The following examples demonstrate how to issue CREATE TABLE statements for specific encryption scenarios.
Example 4-11 Creating a SecureFiles LOB column with a specific encryption algorithm
CREATE TABLE t1 ( a CLOB ENCRYPT USING 'AES128')
LOB(a) STORE AS SECUREFILE (
CACHE
);
Example 4-12 Creating a SecureFiles LOB column with encryption for 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)
);
Example 4-13 Creating a SecureFiles LOB column with encryption based on a password key
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
);
Example 4-14 Creating a SecureFiles LOB column with disabled encryption
CREATE TABLE t1 ( a CLOB )
LOB(a) STORE AS SECUREFILE (
CACHE DECRYPT
);
Using ALTER TABLE with SecureFiles LOBs
You can modify LOB storage with an ALTER TABLE statement or with online redefinition by using the DBMS_REDEFINITION package. See Oracle Database PL/SQL Packages and Types Reference.
Oracle recommends that you enable compression, deduplication, or encryption at table creation time. Enabling these features using ALTER TABLE causes the read, modify, or write commands to alter the entire SecureFiles LOB column, holding a table lock during this potentially long operation.
Note that the SHRINK option is not supported for SecureFiles LOBs.
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 parameters.
The BNF of ALTER TABLE, in Example 4-1, introduces new parameters. Full description of the parameters is in "Parameters of CREATE TABLE for SecureFiles LOB".
See "Migrating Columns from BasicFiles LOBs to SecureFiles LOBs", and the for details on the ALTER TABLEstatement.
Example 4-15 BNF for ALTER TABLE
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 }
]...
] ;
{ { 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 (LOB_item) ( 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
} ...
Parameters of CREATE TABLE for SecureFiles LOB
Table 4-2 summarizes the parameters of the CREATE TABLE statement.
Table 4-2 Parameters of ALTER TABLE Statement
|
Parameter |
Description |
|
Configures the LOB column to store old versions of LOB data in a specified manner. |
|
|
Turns on or turns off SecureFiles LOB compression. |
|
|
Turns on or turns off SecureFiles LOB deduplication. |
|
|
Turns on or turns off SecureFiles LOB encryption. |
RETENTION
Altering RETENTION only affects space created after the ALTER TABLE statement runs.
COMPRESS or NOCOMPRESS
Enables or disables LOB compression. All LOBs in the LOB segment are altered with the new setting.
DEDUPLICATE or KEEP_DUPLICATES
The option DEDUPLICATE enables you to specify that LOB data which is identical in two or more rows in a LOB column should 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 isKEEP_DUPLICATES.
ENCRYPT or DECRYPT
Enables or disables 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 Compression
This section discusses SecureFiles LOB compression when used in the ALTER TABLE statement. This section contains the following topics:
Usage Notes for ALTER TABLE Compression
Examples of ALTER TABLE Compression
Usage Notes for ALTER TABLE Compression
This syntax alters the compression mode of the LOB column.
DBMS_LOB.SETOPTIONS can be used to enable or disable compression on individual LOBs.
Compression may be specified either at the table level or the partition level.
The LOW,MEDIUM, and HIGH options provide varying degrees of compression. The higher the compression, the higher the latency incurred. The HIGHsetting incurs more work, but compresses the data better. The default is MEDIUM. Decompression is simple and very fast. See "CREATE TABLE Compression".
LOB compression applies only to SecureFiles LOBs.
Examples of ALTER TABLE Compression
The following examples demonstrate how to issue ALTER TABLE statements for specific compression scenarios.
Example 4-16 Altering a SecureFiles LOB column to enable LOW compression
ALTER TABLE t1 MODIFY
LOB(a) (
COMPRESS LOW
);
Example 4-17 Altering a SecureFiles LOB column to disable compression
ALTER TABLE t1 MODIFY
LOB(a) (
NOCOMPRESS
);
Example 4-18 Altering a SecureFiles LOB column to enable HIGH compression
ALTER TABLE t1 MODIFY
LOB(a) (
COMPRESS HIGH
);
Example 4-19 Altering a SecureFiles LOB column to enable compression on one partition
ALTER TABLE t1 MODIFY PARTITION p1
LOB(a) (
COMPRESS HIGH
);
ALTER TABLE Deduplication
This section discusses the SecureFiles LOB deduplication in reference to the ALTER TABLE statement. This section contains the following topics:
Usage Notes for ALTER TABLE Deduplication
Examples of ALTER TABLE Deduplication
Usage Notes for ALTER TABLE Deduplication
ALTER TABLE syntax can enable or disable LOB-level deduplication.
This syntax alters the deduplication mode of the LOB column.
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 LOBs.
Examples of ALTER TABLE Deduplication
The following examples demonstrate how to issue ALTER TABLE statements for specific deduplication scenarios.
Example 4-20 Altering a SecureFiles LOB column to disable deduplication
ALTER TABLE t1 MODIFY
LOB(a) (
KEEP_DUPLICATES
);
Example 4-21 Altering a SecureFiles LOB column to enable deduplication
ALTER TABLE t1 MODIFY
LOB(a) (
DEDUPLICATE
);
Example 4-22 Altering a SecureFiles LOB column to enable deduplication on one partition
ALTER TABLE t1 MODIFY PARTITION p1
LOB(a) (
DEDUPLICATE
);
ALTER TABLE Encryption
This section discusses SecureFiles LOB encryption when used in the ALTER TABLE statement. This section contains the following topics:
Usage Notes for ALTER TABLE Encryption
Examples of ALTER TABLE Encryption
Usage Notes for ALTER TABLE Encryption
ALTER TABLE is used to enable and disable LOB encryption for SecureFiles LOBs. This syntax also allows the user to re-key LOB columns with a new key or algorithm.
ENCRYPT/DECRYPT options enable or disable encryption on all LOBs in the specified SecureFiles LOB column.
SALT is the default for LOB encryption. NO SALT is not supported.
The DECRYPT option converts encrypted columns to 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 LOBs.
Examples of ALTER TABLE Encryption
The following examples demonstrate how to issue ALTER TABLE statements for specific encryption scenarios.
Example 4-23 Altering a SecureFiles LOB column by encrypting based on a specific algorithm
Enable LOB encryption using 3DES168.
ALTER TABLE t1 MODIFY
( a CLOB ENCRYPT USING '3DES168');
This is the second example of enabling LOB encryption using 3DES168.
ALTER TABLE t1 MODIFY LOB(a)
(ENCRYPT USING '3DES168');
Example 4-24 Altering a SecureFiles LOB column by encrypting based on a password key
Enable encryption on a SecureFiles LOB column and build the encryption key using a password.
ALTER TABLE t1 MODIFY
( a CLOB ENCRYPT IDENTIFIED BY foo);
Example 4-25 Altering a SecureFiles LOB column by re-keying the encryption
To re-encrypt the LOB column with a new key, re-key the table.
ALTER TABLE t1 REKEY USING '3DES168';
Database File System Links
This section introduces Database File System Links. It contains the following topics:
Overview of Database File System Links
Creating Database File System Links
Copying Database File System Links
Copying a Linked LOB Between Tables
Online Redefinition and DBFS Links
Overview of Database File System Links
DBFS Links provide the ability to transparently store SecureFiles LOBs in a location separate from the segment where the LOB would normally be stored, and instead store a link to the LOB in the segment. The link must reference a path that uses DBFS to locate the LOB when accessed. This means that the LOB could be stored on another file system, or on a tape system, or in the cloud, or any other location that can be accessed using DBFS.
When a user or application tries to access a SecureFiles LOB that has been stored outside the segment using a DBFS Link, the behavior can vary depending on the attempted operation and the characteristics of the DBFS store that is holding the LOB:
Read:
If the LOB is not already cached in a local area in the database, then it can be read directly from the DBFS content store that holds it, if the content store allows streaming access based on the setting of the PROPNAME_STREAMABLE parameter. If the content store does not allow streaming access, then the entire LOB will first be read into a local area in the database, where it will be stored for a period of time for future access.
Write:
If the LOB is not already cached in a local area in the database, then it will first be read into the database, modified as needed, and then written back to the DBFS content store defined in the DBFS Link for the LOB in question.
Delete:
When a SecureFiles LOB that is stored through a DBFS Link is deleted, the DBFS Link is deleted from the table, but the LOB itself is NOT deleted from the DBFS content store. Or it is more complex, based on the characteristics/settings, of the DBFS content store in question.
Database File System Links enable the use of SecureFiles LOBs to implement Hierarchical Storage Management (HSM) in conjunction with the DBFS Hierarchical Store (DBFS HS). HSM is a process by which the database moves rarely used or unused data from faster, more expensive, and smaller storage to slower, cheaper, and higher capacity storage.
Figure 4-1 Database File System Link
Description of "Figure 4-1 Database File System Link"
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.
Oracle provides several methods for creating a DBFS Link:
You can move SecureFiles LOB data into a specified DBFS pathname and store the reference to the new location in the LOB. CallDBMS_LOB.MOVE_TO_DBFS_LINK() with LOB and DBFS path name arguments, and the system creates the specified DBFS HSM Store if it does not exist, copies data from the SecureFiles LOB into the specified DBFS HSM Store, removes data from the SecureFiles LOB, and stores the file path name for subsequent access through this LOB.
You can copy or create a reference to an existing file. Call DBMS_LOB.COPY_DBFS_LINK() to copy a link from an existing DBFS Link. If there is any data in the destination SecureFiles LOB, the system removes this data and stores a copy of the reference to the link in the destination SecureFiles LOB.
Call DBMS_LOB.SET_DBFS_LINK(), which assumes that the data for the link is stored in the specified DBFS path name. The system removes data in the specified SecureFiles LOB and stores the link to the DBFS path name.
Creating a DBFS Link impacts the operations that may be performed, and how they may be performed. Some of the DBMS_LOB operations that existed before Oracle Database 11gR2 throw an exception if the LOB is a DBFS Link. The application must explicitly replace the DBFS Link with a LOB by callingDBMS_LOB.COPY_FROM_LINK() before making these calls. When completed, the application can move the updated LOB back to DBFS usingDBMS_LOB.MOVE_TO_DBFS_LINK(), if needed. Other DBMS_LOB operations that existed before Oracle Database 11gR2 work transparently if the DBFS Link is in a file system that supports streaming. Note that these operations fail if streaming is either not supported or disabled.
If the DBFS Link file is modified through DBFS interfaces directly, the change is reflected in subsequent reads of the SecureFiles LOB. If the file is deleted through DBFS interfaces, then an exception occurs on subsequent reads.
For the database, it is also possible that a DBA may not want to store all of the data stored in a SecureFiles LOB HSM during export and import. Oracle has the ability to export and import only the Database File System Links. The links are fully qualified identifiers that provide access to the stored data, when entered into a SecureFiles LOB or registered on a SecureFiles LOB in a different database. This ability to export and import a link is similar 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 occurs on the imported system. The application is responsible for stored data retention. If the application system removes data from the store that still has a reference to it, the database throws an exception when the referencing SecureFiles LOB(s) attempt to access the data. Oracle also supports continuing 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 copies in compliance with 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 SecureFiles LOB. By default, the LOB is not obtained from the DBFS HSM 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 has a local copy in the database and references the LOB data in the DBFS HSM Store.
Copying a Linked LOB Between Tables
CREATE TABLE ... AS SELECT (CTAS) and INSERT TABLE ... AS SELECT (ITAS) copies any DBFS Links that are stored in any SecureFiles LOBs in the source table to the destination table.
Online Redefinition and DBFS Links
Online redefinition copies any DBFS Links that are stored in any SecureFiles LOBs in the table being redefined.
Transparent Read
DBFS Links have the ability to read from a linked SecureFiles LOB even if the data is not cached in the database. This is done by reading the data from the Content Store where the data is currently stored, and streaming that data back to the user application as if it were being read from the SecureFiles LOB segment. This allows seamless access to the DBFS Linked data without the prerequisite first call to DBMS_LOB.COPY_FROM_DBFS_LINK().
Whether or not transparent read is available for a particular SecureFiles LOB is determined by the DBFS_CONTENT store where the data resides. This feature is always enabled for DBFS_SFS stores, and by default for DBFS_HS stores. To disable transparent read for DBFS_HS store, set the PROPNAME_STREAMABLE parameter toFALSE.
See Also:
Initialization Parameter db_securefile for SecureFiles LOBs
The initialization parameter db_securefile is set in the file init.ora.
The parameter db_securefile is of type text. Its allowable values are ALWAYS, PERMITTED [default], NEVER, or IGNORE. The db_securefile parameter is dynamic and its scope is ALTER SYSTEM.
Example 4-26 Setting db_securefile parameter through ALTER SYSTEM
ALTER SYSTEM SET db_securefile = 'ALWAYS';
The db_securefile parameter enables the database administrator to either allow SecureFiles LOBs to be created (PERMITTED), disallow SecureFiles LOBs from being created in the future (NEVER), attempt to create SecureFiles LOBs but fall back to BasicFiles LOBs (ALWAYS), or disallow SecureFiles LOBs and ignore any errors that would otherwise be caused by forcing BasicFiles LOBs with SecureFiles LOBs options (IGNORE).
If NEVER is specified, any LOBs that are specified as SecureFiles LOBs are created as BasicFiles LOBs. All SecureFiles LOB- specific storage options and features such as compress, encrypt, or deduplicate throw an exception. The BasicFiles LOB defaults are used for storage options not specified.
ALWAYS attempts to create all LOBs as SecureFiles LOBs but creates any LOBs not in ASSM tablespaces as BasicFiles LOBs, unless the SECUREFILE parameter is explicitly specified. Any BasicFiles LOB storage options specified are ignored, and the SecureFiles LOB defaults are used for all storage options not specified.
If IGNORE is specified, the SECUREFILE keyword and all SecureFiles LOB options are ignored.
Compatibility and Upgrading
All features described in this document are enabled with compatibility set to 11.2.0.0.0 or higher. There is no downgrade capability after 11.2.0.0.0 is set.
If you want to upgrade BasicFiles LOBs to SecureFiles LOBs, you must use typical methods for upgrading data (CTAS/ITAS, online redefinition, export/import, column to column copy, or using a view and a new column). Most of these solutions require twice the disk space used by the data in the input LOB column. However, partitioning and taking these actions on a partition-by-partition basis lowers the disk space requirements.
Migrating Columns from BasicFiles LOBs to SecureFiles LOBs
The method of migrating LOBs columns is presented in this section.
Preventing Generation of REDO Space when Migrating to SecureFiles LOBs
Generation of redo space can cause performance problems during the process of migrating BasicFiles 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 BasicFiles LOB to SecureFiles LOB are logged only if the storage characteristics of the SecureFiles 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 SecureFiles LOB column(s). You can turn LOGGING on once your migration is complete.
Online Redefinition for BasicFiles LOBs
Online redefinition is the only recommended method for migration of BasicFiles LOBs to SecureFiles LOBs. It can be done at the table or partition level.
Online Redefinition Advantages
No requirement to take the table or partition offline
Can be done in parallel
Online Redefinition Disadvantages
Additional storage equal to the entire table or partition and all LOB segments must be available
Global indexes must be rebuilt
Using Online Redefinition for Migrating Tables with BasicFiles LOBs
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. Example 4-27 demonstrates how to migrate a table using Online Redefinition.
Example 4-27 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
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 requirement 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 BasicFiles LOB column to a SecureFiles LOB column may 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 LOBs.
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-27, "Example of Online Redefinition" in the last section:
ALTER SESSION FORCE PARALLEL DML;
PL/SQL Packages for SecureFiles LOBs and DBFS
This section introduces PL/SQL packages that are used with SecureFiles LOBs.
This section includes the following topics:
DBMS_LOB Package
LOBs inherit the LOB column settings for deduplication, encryption, and compression, which may also be configured on specific LOB instances using the LOB locator APIs. Note that LONG APIs cannot be used to configure LOB settings. The following sections describe additions and modifications made to the PL/SQLDBMS_LOB package to accommodate these features. See Oracle Database PL/SQL Packages and Types Reference, DBMS_LOB package for more details.
The constants listed in Table 4-3, of type CONSTANT PLS_INTEGER, support the DBFS Link interfaces. For more information about constants used in the PL/SQLDBMS_LOB package, see Oracle Database PL/SQL Packages and Types Reference.
Table 4-3 Some DBMS_LOB Constants
|
Constant |
Description |
|
DBFS_LINK_NEVER |
DBFS link state value |
|
DBFS_LINK_YES |
DBFS link state value |
|
DBFS_LINK_NO |
DBFS link state value |
|
DBFS_LINK_CACHE |
Flag used by COPY_DBFS_LINK() and MOVE_DBFS_LINK(). |
|
DBFS_LINK_NOCACHE |
Flag used by COPY_DBFS_LINK() and MOVE_DBFS_LINK(). |
|
DBFS_LINK_PATH_MAX_SIZE |
The maximum length of DBFS pathnames; 1024. |
|
CONTENTTYPE_MAX_SIZE |
The maximum 1-byte ASCII characters for content type; 128. |
Table 4-4 summarizes changes made to the methods of the PL/SQL package DBMS_LOB. Many existing methods have been augmented to support the SecureFiles LOB paradigm, and several new methods have been added.
Note that some of the DBMS_LOB operations that existed before Oracle Database 11gR2 throw an exception if the LOB is a DBFS Link. The application must explicitly replace the DBFS Link with a LOB by calling COPY_FROM_LINK() before making these calls. When completed, the application can move the updated LOB back to DBFS using MOVE_TO_DBFS_LINK(), if needed. Other DBMS_LOB operations that existed before Oracle Database 11gR2 work transparently if the DBFS Link is in a file system that supports streaming. Note that these operations fail if streaming is either not supported or disabled.
Table 4-4 DBMS_LOB Methods
|
Method |
Description |
|
Retrieves the previously set options of a specific LOB |
|
|
Sets new options for a specific LOB |
|
|
Determines if a LOB is a SecureFiles LOB |
|
|
Moves the specified LOB data from the database into DBFS HSM Store |
|
|
Copies the specified LOB data from DBFS HSM Store into the database |
|
|
Copies an existing DBFS link into a new LOB |
|
|
Returns the DBFS path name for a LOB |
|
|
Links a LOB with a DBFS path name |
|
|
Returns the linking state of a LOB |
|
|
Returns a unique file path name for creating a DBFS Link |
|
|
Sets the content type string of the LOB data |
|
|
Retrieves the content type string of the LOB data |
|
|
Appends the contents of the source LOB to the destination LOB |
|
|
Compares two LOBs in full or in parts |
|
|
Converts the character data of a CLOB or NCLOB into the specified character set and writes it in binary format to a destination BLOB |
|
|
Converts the binary data of a BLOB into the specified character set and writes it in character format to a destination CLOB or NCLOB |
|
|
Copies all or part of the source LOB to the destination LOB |
|
|
Erases all or part of a LOB |
|
|
Deletes a specified fragment of the LOB |
|
|
Inserts a fragment of data into the LOB |
|
|
Moves a fragment of a LOB from one location in the LOB to another location |
|
|
Replaces a fragment of a LOB with new data |
|
|
Loads BFILE data into a BLOB |
|
|
Loads BFILE data into a CLOB |
|
|
Loads BFILE data into a LOB |
|
|
Reads data from a LOB |
|
|
Returns a fragment of a LOB |
|
|
Trims the LOB to a specified length |
|
|
Writes data to a LOB |
|
|
Appends data to the end of a LOB |
GETOPTIONS()
This function obtains the compression, deduplication and encryption settings of individual SecureFiles LOBs. An integer corresponding to a pre-defined constant based on the option type is returned.
Note that you cannot turn compression or deduplication on or off for an entire SecureFiles LOB column that has these features disabled.
See the Oracle Database PL/SQL Packages and Types Reference for more details on this function. See the Oracle Call Interface Programmer's Guide for more information on the corresponding OCI LOB function OCILobGetContentType().
SETOPTIONS()
This procedure sets compression, deduplication and encryption features. It enables the features to be set on a per-LOB basis, overriding the default LOB settings. This call incurs a round trip to the server to make the changes persistent.
You cannot turn compression or deduplication on or off for a SecureFiles LOB column that does not have those features enabled. GETOPTIONS() andSETOPTIONS() work on individual SecureFiles LOBs. You can turn off a feature on a particular SecureFiles LOB and turn on a feature that has been turned off bySETOPTIONS(), but you cannot turn on an option that has not been given to the SecureFiles LOB when the table was created.
See the Oracle Database PL/SQL Packages and Types Reference for more details on this function. See the Oracle Call Interface Programmer's Guide for more information on the corresponding OCI LOB function OCILobSetContentType().
ISSECUREFILE()
This function returns TRUE if the LOB locator passed to it is for a SecureFiles LOB. It returns FALSE otherwise.
See the Oracle Database PL/SQL Packages and Types Reference for more details on this function.
MOVE_TO_DBFS_LINK()
MOVE_TO_DBFS_LINK() places the specified LOB data (from the database) into the DBFS HSM Store. If the LOB is 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 is 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 stored causes the LOB to be cached (MOVE_TO_DBFS_LINK_CACHE) or removed (DBFS_LINK_NOCACHE) according to the flags setting.
See Oracle Database PL/SQL Packages and Types Reference for more information.
COPY_FROM_DBFS_LINK()
COPY_FROM_DBFS_LINK() retrieves the specified LOB data from DBFS HSM Store to the database.
If the LOB is successfully retrieved, COPY_FROM_DBFS_LINK silently returns success.
See Oracle Database PL/SQL Packages and Types Reference for more information.
COPY_DBFS_LINK()
COPY_DBFS_LINK() results in a destination LOB referring to the same the DBFS path name as the source LOB without causing an intervening get. An optional flag parameter causes the LOB to be read into the destination.
See Oracle Database PL/SQL Packages and Types Reference for more information.
GET_DBFS_LINK()
GET_DBFS_LINK() returns the DBFS path name for the given LOB.
See Oracle Database PL/SQL Packages and Types Reference for more information.
SET_DBFS_LINK()
SET_DBFS_LINK links the specified LOB with the given DBFS path name.
GET_DBFS_LINK_STATE()
GET_DBFS_LINK_STATE retrieves the current link state for the specified LOB.
Returns the current link state of the specified LOB. If the LOB has never been linked, the state is set to DBFS_LINK_NEVER. If the LOB has been linked, the state is set to DBFS_LINK_YES. If the LOB has been retrieved from DBFS HSM Store, the state is set to DBFS_LINK_NO. If the LOB was linked, but the data was left in the database, cached is set to TRUE. Cached is set to FALSE if the data was removed after the link was created, and NULL if state is DBFS_LINK_NEVER. The pathnameargument is set to the DBFS path name that is used to identify the LOB in the DBFS HSM Store.
See Oracle Database PL/SQL Packages and Types Reference for more information.
DBFS_LINK_GENERATE_PATHNAME()
DBFS_LINK_GENERATE_PATHNAME() returns a unique file path name for creating a DBFS Link.
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 is always the same for the same LOB and version.
SETCONTENTTYPE()
SETCONTENTTYPE() sets the content type string for the data in the LOB.
To clear an existing contenttype associated with a SecureFiles LOB, invoke SETCONTENTTYPE() with contenttype set to an empty string.
See Oracle Database PL/SQL Packages and Types Reference for more information.
GETCONTENTTYPE()
GETCONTENTTYPE() gets the content type string for the data in the LOB if set.
If the SecureFiles LOB does not have a contenttype associated with it, GETCONTENTTYPE() returns NULL.
See Oracle Database PL/SQL Packages and Types Reference for more information.
APPEND()
If APPEND() is called on a SecureFiles LOB that is linked, an exception is thrown.
See Oracle Database PL/SQL Packages and Types Reference for more information.
COMPARE()
If COMPARE() is called on a LOB that is a DBFS Link, the linked LOB is streamed from the DBFS, if possible, otherwise an exception is thrown.
See Oracle Database PL/SQL Packages and Types Reference for more information.
CONVERTTOBLOB()
If CONVERTTOBLOB is called and the source LOB has been linked, an exception is thrown.
See Oracle Database PL/SQL Packages and Types Reference for more information.
CONVERTTOCLOB()
If CONVERTTOCLOB is called and the source LOB has been linked, an exception is thrown.
See Oracle Database PL/SQL Packages and Types Reference for more information.
COPY()
If the source LOB is linked, the data is streamed from the DBFS, if possible, otherwise an exception is thrown. If the destination LOB is linked, an exception is thrown.
See Oracle Database PL/SQL Packages and Types Reference for more information.
ERASE()
If the LOB to be erased is linked, an exception is thrown.
See Oracle Database PL/SQL Packages and Types Reference for more information.
FRAGMENT_DELETE()
If the LOB is linked, an exception is thrown.
See Oracle Database PL/SQL Packages and Types Reference for more information.
FRAGMENT_INSERT()
If the LOB is linked, an exception is thrown.
See Oracle Database PL/SQL Packages and Types Reference for more information.
FRAGMENT_MOVE()
If the LOB is linked, an exception is thrown.
See Oracle Database PL/SQL Packages and Types Reference for more information.
FRAGMENT_REPLACE()
If the LOB is linked, an exception is thrown.
See Oracle Database PL/SQL Packages and Types Reference for more information.
LOADBLOBFROMFILE()
If the BLOB is linked, an exception is thrown.
See Oracle Database PL/SQL Packages and Types Reference for more information.
LOADCLOBFROMFILE()
If the CLOB is linked, an exception is thrown.
See Oracle Database PL/SQL Packages and Types Reference for more information.
LOADFROMFILE()
If the LOB is linked, an exception is thrown.
See Oracle Database PL/SQL Packages and Types Reference for more information.
READ()
If the LOB is linked, the data is streamed from the DBFS, if possible, otherwise an exception is thrown.
See Oracle Database PL/SQL Packages and Types Reference for more information.
SUBSTR()
If the LOB is linked, the data is streamed from the DBFS, if possible, otherwise an exception is thrown.
See Oracle Database PL/SQL Packages and Types Reference for more information.
TRIM()
If the LOB is linked, an exception is thrown.
See Oracle Database PL/SQL Packages and Types Reference for more information.
WRITE()
If the LOB is linked, an exception is thrown.
See Oracle Database PL/SQL Packages and Types Reference for more information.
WRITEAPPEND()
If the LOB is linked, an exception is thrown.
See Oracle Database PL/SQL Packages and Types Reference for more information.
DBMS_SPACE Package
The DBMS_SPACE PL/SQL package enables you to analyze segment growth and space requirements.
SPACE_USAGE()
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. See Oracle Database PL/SQL Packages and Types Reference for more information.
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/28612416/viewspace-1065383/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- SecureFiles LOBs基礎知識之儲存篇
- oracle SecureFiles 替代之前的 LOBOracle
- ORACLE11GR1 中的SecureFilesOracle
- Oracle 11g新特性之SecureFilesOracle
- Oracle 11g 新特性 -- SecureFiles 說明Oracle
- Summary Note Index for BasicFiles and SecureFilesIndex
- DataPump Export/Import Of LOBs Are Not Executed in ParallelExportImportParallel
- Using Regular Expressions in Oracle DatabaseExpressOracleDatabase
- write picture to oracle using javaOracleJava
- Oracle 20c 新特性：Online SecureFiles Defragmentation 線上的 LOB 碎片整理OracleFragment
- using escape character whitin oracleOracle
- Using WebLogic Server With Oracle RAC(轉)WebServerOracle
- oracle JRE issue using runInstallerOracle
- Using Create directory & UTL_FILE in OracleOracle
- For oracle databases, if the top showing the oracle database, then oracle process is using the top cOracleDatabase
- How To Using Flashback Data Archive (Oracle Total Recall)HiveOracle
- Oracle 12c: Recover tables using RMANOracle
- How to check whether the current database in using Oracle optionsDatabaseOracle
- 【SQL】Using Oracle's Parallel Execution FeaturesSQLOracleParallel
- Step by Step Data Replication Using Oracle GoldenGateOracleGo
- TEMPORARY LOBS are not freed up automatically after PL/SQL block executionSQLBloC
- ORA-22868: table with LOBs contains segments in different tablespacesAI
- Oracle 12Cr2 Using CloneDB to clone a databaseOracleDatabase
- zt_Using Oracle's Parallel Execution FeaturesOracleParallel
- Using Oracle Database 11g Release 2 Result Cache in an Oracle RAC EnvironmentOracleDatabase
- How to Set Device Name Using UDEV on Oracle Linux 7.1devOracleLinux
- oracle create a new database using backup controlfile to traceOracleDatabase
- Using index condition Using indexIndex
- Converting Oracle Database from Linux to Windows using RMANOracleDatabaseLinuxWindows
- G009-ORACLE-ASK Using In-Database ArchivingOracleDatabase
- Oracle 12C Transport a Database to a Different Platform Using Backup SetsOracleDatabasePlatform
- Converting Oracle Database from Windows to Linux using RMANOracleDatabaseWindowsLinux
- Using Silent Mode to Install Oracle Enterprise Manager Grid Control 10.2.0.1.1 Using an Existing DaOracle
- Oracle Database 12c Release 2 (12.2) RAC On Oracle Linux 7 Using VMwareOracleDatabaseLinux
- Oracle RAC 10g Installation on Oracle Enterprise Linux Using VMware ServerOracleLinuxServer
- 【Oracle】Installing and Using Standby Statspack in 11gOracle
- MySQL 索引優化 Using where, Using filesortMySql索引優化
- using indexIndex