181 DBMS_SECUREFILES
The DBMS_SECUREFILES package provides functions to operate
on and manage SecureFiles LOB.
This chapter contains the following topics:
181.1 DBMS_SECUREFILES Security Model
This package is defined with AUTHID CURRENT USER, so it
executes with the privileges of the current user.
Any DBMS_SECUREFILES subprogram called from an anonymous PL/SQL
block is executed using the privileges of the current user.
See Also:
For more information on AUTHID and privileges, see Oracle AI Database PL/SQL
Language Reference.
181.2 Summary of DBMS_SECUREFILES Functions
This table lists the DBMS_SECUREFILES functions and briefly
describes them.
Table 181-1 DBMS_SECUREFILES Package Functions
| Subprogram | Description |
|---|---|
| GET_LOB_COMPR_RATIO Function | Use this function to estimate the potential storage savings by enabling compression on SecureFile LOBs. |
| GET_LOB_DEDUPLICATION_RATIO Function | Use this function to estimate the potential storage savings by enabling deduplication on SecureFile LOBs. |
181.2.1 GET_LOB_COMPR_RATIO Function
Use this function to estimate the potential storage savings by enabling compression on SecureFile LOBs. Provide values for various parameters to selectively analyze the different compression types.
The compression ratio is estimated for the specified number of rows in a LOB column. For example, let's consider that the compression ratio is 2.33. It indicates that after enabling compression, around half of the storage space is saved for the sampled rows in the LOB column.
Disclaimer: The compression ratio is an approximate value, which is calculated based on the sampled rows in a LOB column. The actual space saved when compression feature is enabled for LOBs may be different.
Syntax
Syntax to get the compression ratio for LOBs.
DBMS_SECUREFILES.GET_LOB_COMPR_RATIO ( tablespacename IN VARCHAR2, tabowner IN VARCHAR2, tabname IN VARCHAR2, lobcolumnname IN VARCHAR2, partname IN VARCHAR2, comptype IN NUMBER, subset_numrows IN NUMBER, lobcnt OUT PLS_INTEGER, comp_ratio OUT NUMBER, block_compr_ratio OUT NUMBER, total_time OUT NUMBER, blkcnt_cmp OUT PLS_INTEGER, blkcnt_uncmp OUT PLS_INTEGER, ) return NUMBER;
Parameters
Table 181-2 GET_LOB_COMPR_RATIO Function Parameters
| Parameter | Description |
|---|---|
|
|
Name of the temporary scratch tablespace that can be used for analysis. |
|
|
Schema of the table to be analyzed. |
|
|
Name of the table to be analyzed. |
|
|
Name of the LOB column. |
|
|
In case of partitioned tables, name of the related partition or sub-partition. |
|
|
Compression ratio is the ratio of the original data size (in bytes) to the compressed data size (in bytes). |
|
|
Number of rows sampled to estimate the compression ratio. |
block_compr_ratio |
Block level compression ratio is
blkcnt_uncmp divided by blkcnt_cmp. It provides
the ratio of the number of blocks occupied by the uncompressed data to the number of
blocks occupied by the compressed data.
|
total_time |
Provides an estimate of the time taken, in hours, to compress the LOB data in the table. |
|
|
Specify the compression type for the analysis. The permitted values are,
|
|
|
Number of blocks used by compressed sample of the table. |
|
|
Number of blocks used by uncompressed sample of the table. |
|
|
Number of LOBs actually sampled to estimate the compression ratio. |
Example
DECLARE
cmp_ratio NUMBER;
block_comp_ratio NUMBER;
time_taken NUMBER;
blkcnt_uncmp INTEGER;
blkcnt_cmp INTEGER;
lobcnt INTEGER;
BEGIN
cmp_ratio := DBMS_SECUREFILES.GET_LOB_COMPR_RATIO(
tablespacename => 'SYSAUX',
tabowner => 'T',
tabname => 'T',
lobcolumnname => 'DATA',
partname => '',
comp_ratio => cmp_ratio,
subset_numrows => 1000,
block_compr_ratio => block_comp_ratio,
total_time => time_taken,
comptype => 2,
blkcnt_uncmp => blkcnt_uncmp,
blkcnt_cmp => blkcnt_cmp,
lobcnt => lobcnt
);
DBMS_OUTPUT.put_line('Estimated compression ratio of sample: ' || cmp_ratio);
DBMS_OUTPUT.put_line('Estimated compression ratio at block level: ' || block_comp_ratio);
DBMS_OUTPUT.put_line('Number of blocks used by the uncompressed sample of the table: ' || blkcnt_uncmp);
DBMS_OUTPUT.put_line('Number of blocks used by the compressed sample of the table: ' || blkcnt_cmp);
DBMS_OUTPUT.put_line('Number of LOBS actually sampled: ' || lobcnt);
DBMS_OUTPUT.put_line('Time taken: ' || time_taken);
END;
/Sample Output
Estimated compression ratio of sample: 1.36889059958356
Estimated compression ratio at block level: 1
Number of blocks used by the uncompressed sample of the table: 3102
Number of blocks used by the compressed sample of the table: 3102
Number of LOBS actually sampled: 1000
Time taken: .00015315493742147832
PL/SQL procedure successfully completed.Usage Notes
The function creates different tables in the scratch tablespace and runs analysis on these objects. It does not modify anything in the user-specified tables. All interim objects are removed before the function completes.
181.2.2 GET_LOB_DEDUPLICATION_RATIO Function
Advanced LOB deduplication enables Oracle Database to automatically detect
duplicate LOB data, within a LOB column or partition, and conserves space by storing only
one copy of the data. Use the GET_LOB_DEDUPLICATION_RATIO function to
estimate the potential storage savings by enabling deduplication on SecureFile
LOBs.
The GET_LOB_DEDUPLICATION_RATIO function returns the deduplication ratio, which allows you to make an informed decision about enabling advanced LOB deduplication or about deduplicating the resultant SecureFiles LOB, before migrating BasicFiles LOB to SecureFiles LOB. For information about advanced LOB deduplication, see ALTER TABLE with Advanced LOB Deduplication in Oracle Database SecureFiles and Large Objects Developer's Guide.
The deduplication ratio is estimated for the specified number of rows in a LOB column. For example, let's consider that the deduplication ratio is 2.33. It indicates that after enabling deduplication, around half of the storage space is saved for the sampled rows in the LOB column.
Disclaimer: The deduplication ratio is an approximate value, which is calculated based on the sampled rows in the LOB column. The actual space saved may be different when deduplication is enabled for the entire table.
Required Role
The SECUREFILE_MONITOR role is required to use the
GET_LOB_DEDUPLICATION_RATIO function.
Syntax
DBMS_SECUREFILES.GET_LOB_DEDUPLICATION_RATIO (
tablespacename IN VARCHAR2,
tabowner IN VARCHAR2,
tabname IN VARCHAR2,
lobcolumnname IN VARCHAR2,
partname IN VARCHAR2,
dedup_ratio OUT NUMBER,
subset_numrows IN NUMBER DEFAULT DEDUP_RATIO_LOB_MAXROWS
)Parameters
Table 181-3 GET_LOB_DEDUPLICATION_RATIO Function Parameters
| Parameter | Description |
|---|---|
|
|
Name of the tablespace |
|
|
Owner of the table |
|
|
Name of the table |
|
|
Name of the LOB column for which the deduplication ratio is calculated |
|
|
In case of partitioned tables, enter the related partition name |
|
|
Number of rows sampled to estimate the deduplication ratio. By default, the deduplication ratio is calculated for all the rows. |
Return Values
Returns the deduplication ratio, dedup_ratio, which indicates
storage space saved for SecureFile LOB column after enabling deduplication.
Example
The following sample code calculates and returns the deduplication ratio for C, a LOB column, in ACME_TABLE, which is owned by JOHN.
DECLARE
dedup_ratio number;
l_table_name varchar2(128) = "ACME_TABLE";
l_column_name varchar2(3000) = "C";
l_tablespace_name varchar2(128) := "TBS";
l_owner varchar2(128) := "JOHN";
BEGIN
dedup_ratio := dbms_lob.GET_LOB_DEDUPLICATION_RATIO(
l_tablespace_name,
l_owner,
l_table_name,
l_column_name,
'',
dedup_ratio,
-1);
dbms_output.put_line('Deduplication ratio: ' || dedup_ratio);
END;Usage Notes
This function can process a maximum of 100000 LOBs or 1% of the total number of rows in the table, whichever is lesser.