Oracle SecureFiles

Oracle SecureFiles Version 11.2.0.3

General Information

SecureFiles are part of Oracle's Advanced Compression license option.

Data Dictionary Objects

DBMS_FEATURE_SECUREFILES_SYS	DBA_TABLESPACES	TS$
DBMS_FEATURE_SECUREFILES_USR	GV$PARAMETER	V$PARAMETER
DBA_LOBS	GV$SECUREFILE_TIMER	V$SECUREFILE_TIMER

Syntax

/* To avoid all of the complexities of every possible type of table containing a LOB and all of the various clauses the following examples are intentionally limited in scope to demonstrate the most basic capabilities. Read the online docs to gain a full understanding of SecureFile possibilities. */

CREATE TABLE <[schema_name.]<table_name> (
<column_name> <data_type>,
<column_name> <data_type>,
...
<column_name> <data_type>)
LOB (<lob_column_name>)
STORE AS SECUREFILE <lob_column_name> (
TABLESPACE <SecureFile_tablespace_name>
RETENTION MIN <integer>
[<DEDUPLICATE | KEEP_DUPLICATES>] [<COMPRESS <HIGH | MEDIUM | LOW> | NOCOMPRESS>] <[DECRYPT ENCRYPT>] [<CACHE READS | NOCACHE>])
TABLESPACE <tablespace_name>;

Wallet

To run the demos on this page a wallet must exist and must be open: See link at page bottom

SecureFile Demo

Manage securefile initialization parameter

Parameter Options:

FORCE
PERMITTED (default)

conn uwclass/uwclass

set linesize 121
col name format a30
col value format a30

SELECT name, value
FROM gv$parameter
WHERE name LIKE '%secure%';

ALTER SYSTEM SET db_securefile = 'FORCE' SCOPE=MEMORY;

SELECT name, value
FROM gv$parameter
WHERE name LIKE '%secure%';

ALTER SYSTEM SET db_securefile = 'PERMITTED' SCOPE=BOTH;

Create Auto Management ASSM Tablespace

conn / as sysdba

desc dba_tablespaces

SELECT tablespace_name, segment_space_management
FROM dba_tablespaces;

desc dba_data_files

SELECT file_name
FROM dba_data_files;

CREATE TABLESPACE securefiletbs
DATAFILE 'c:\temp\securefile01.dbf' SIZE 25M
EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO;

desc dba_tablespaces

SELECT tablespace_name, extent_management, allocation_type, segment_space_management
FROM dba_tablespaces
ORDER BY 1;

Allocate Quota

conn / as sysdba

ALTER USER uwclass QUOTA 23M ON securefiletbs;

SELECT username, max_bytes, max_blocks
FROM dba_ts_quotas
WHERE tablespace_name = 'SECUREFILETBS';

Create table with LOB column in securefile tablespace

conn uwclass/uwclass

CREATE TABLE secure_file_tab (
rid NUMBER(5),
bcol BLOB)
LOB (bcol_lob) STORE AS SECUREFILE bcol (
TABLESPACE securefiletbs
RETENTION MIN 3600
KEEP_DUPLICATES NOCOMPRESS DECRYPT CACHE READS);

Create tables using SecureFile Storage

conn uwclass/uwclass

CREATE TABLE reg_tab (
rid NUMBER(5),
bcol BLOB)
LOB (bcol)
STORE AS REGFILE (
TABLESPACE uwdata)
TABLESPACE uwdata;

CREATE TABLE sec_tab_kd (
rid NUMBER(5),
bcol BLOB)
LOB (bcol)
STORE AS SECUREFILE bcol (
TABLESPACE securefiletbs
RETENTION MIN 3600
KEEP_DUPLICATES NOCOMPRESS DECRYPT CACHE READS)
TABLESPACE uwdata;

CREATE TABLE sec_tab_dd (
rid NUMBER(5),
bcol BLOB)
LOB (bcol)
STORE AS SECUREFILE bcol2 (
TABLESPACE securefiletbs
RETENTION MIN 3600
COMPRESS ENCRYPT CACHE READS)
TABLESPACE uwdata;

SELECT table_name, tablespace_name
FROM user_tables
ORDER BY 1;

desc user_lobs

col table_name format a10
col column_name format a10

SELECT table_name, column_name, chunk, retention, cache, encrypt, compression, deduplication, in_row, format, securefile
FROM user_lobs;

Load BLOBs

set linesize 121
col owner format a10
col directory_path format a70

SELECT *
FROM all_directories;

/*
conn / as sysdba

CREATE OR REPLACE DIRECTORY ctemp AS 'c:\temp';

GRANT read ON DIRECTORY ctemp TO uwclass;

conn uwclass/uwclass
*/

CREATE OR REPLACE PROCEDURE load_blob (filein IN VARCHAR2) IS
src_file BFILE;
dst_file BLOB;
lgh_file BINARY_INTEGER;
BEGIN
src_file := bfilename('CTEMP', filein);

dbms_lob.fileopen(src_file, dbms_lob.file_readonly);
lgh_file := dbms_lob.getlength(src_file);

FOR i IN 1..3 LOOP
    INSERT INTO reg_tab
    (rid, bcol)
    VALUES
    (i, EMPTY_BLOB())
    RETURNING bcol INTO dst_file;

    SELECT bcol
    INTO dst_file
    FROM reg_tab
    WHERE rid = i
    FOR UPDATE;

    dbms_lob.loadfromfile(dst_file, src_file, lgh_file);

    UPDATE reg_tab
    SET bcol = dst_file
    WHERE rid = i;

    INSERT INTO sec_tab_kd
    (rid, bcol)
    VALUES
    (i, EMPTY_BLOB())
    RETURNING bcol INTO dst_file;

    SELECT bcol
    INTO dst_file
    FROM sec_tab_kd
    WHERE rid = i
    FOR UPDATE;

    dbms_lob.loadfromfile(dst_file, src_file, lgh_file);

    UPDATE sec_tab_kd
    SET bcol = dst_file
    WHERE rid = i;

    INSERT INTO sec_tab_dd
    (rid, bcol)
    VALUES
    (i, EMPTY_BLOB())
    RETURNING bcol INTO dst_file;

    SELECT bcol
    INTO dst_file
    FROM sec_tab_dd
    WHERE rid = i
    FOR UPDATE;

    dbms_lob.loadfromfile(dst_file, src_file, lgh_file);

    UPDATE sec_tab_dd
    SET bcol = dst_file
    WHERE rid = i;
END LOOP;
COMMIT;

dbms_lob.fileclose(src_file);
END load_blob;
/

-- with an mpg file
exec load_blob('sphere.mpg');

SELECT COUNT(*)
FROM reg_tab;

SELECT COUNT(*)
FROM sec_tab_kd;

SELECT COUNT(*)
FROM sec_tab_dd;

SELECT dbms_lob.getlength(bcol) FROM reg_tab;

SELECT dbms_lob.getlength(bcol) FROM sec_tab_kd;

SELECT dbms_lob.getlength(bcol) FROM sec_tab_dd;

-- with a doc file
exec load_blob('sphere.html');

SELECT COUNT(*)
FROM reg_tab;

SELECT COUNT(*)
FROM sec_tab_kd;

SELECT COUNT(*)
FROM sec_tab_dd;

SELECT dbms_lob.getlength(bcol) FROM reg_tab;

SELECT dbms_lob.getlength(bcol) FROM sec_tab_kd;

SELECT dbms_lob.getlength(bcol) FROM sec_tab_dd;

Examine Results

col segment_name format a30

SELECT segment_name, segment_type, tablespace_name, blocks
FROM user_segments
WHERE segment_name IN ('REG_TAB', 'SEC_TAB_KD', 'SEC_TAB_DD', 'BCOL', 'BCOL2', 'REGFILE');

set serveroutput on

DECLARE
b    BOOLEAN;
bvar BLOB;
BEGIN
SELECT bcol
INTO bvar
FROM sec_tab2
WHERE rownum = 1;

b := dbms_lob.issecurefile(bvar);

IF b THEN
    dbms_output.put_line('Stored in a securefile');
ELSE
    dbms_output.put_line('Not stored in a securefile');
END IF;
END;
/

DECLARE
bvar BLOB;
BEGIN
SELECT bcol
INTO bvar
FROM sec_tab_kd
WHERE rownum = 1;

dbms_output.put_line(dbms_lob.getoptions(bvar, 1));
dbms_output.put_line(dbms_lob.getoptions(bvar, 3));
dbms_output.put_line(dbms_lob.getoptions(bvar, 4));

SELECT bcol
INTO bvar
FROM sec_tab_dd
WHERE rownum = 1;

dbms_output.put_line(dbms_lob.getoptions(bvar, 1));
dbms_output.put_line(dbms_lob.getoptions(bvar, 3));
dbms_output.put_line(dbms_lob.getoptions(bvar, 4));
END;
/

Another SecureFile Demo

SecureFile Advanced Compression

CREATE TABLE comp_high (
rid NUMBER(5),
bcol BLOB)
LOB (bcol)
STORE AS SECUREFILE bcol (COMPRESS HIGH)
TABLESPACE securefiletbs;