Oracle SecureFiles
Version 20c

General Information
Library Note Morgan's Library Page Header
ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling complimentary technical Workshops on Database Security for the first 30 Oracle Database customers located anywhere in North America, EMEA, LATAM, or APAC that send an email to asra_us@oracle.com. Request a Workshop for your organization today.
Purpose SecureFiles LOB storage is one of two storage types used with Oracle Database 12c the other type is BasicFiles LOB storage. The SECUREFILE LOB parameter enables advanced features, including compression and deduplication (part of the Advanced Compression Option), and encryption (part of the Advanced Security Option). Starting with Oracle Database 12c, SecureFiles is the default storage mechanism for LOBs. LOB compression in SecureFiles provides a size reduction of 2x to 3x.
Dependencies
DBMS_FEATURE_SECUREFILES_SYS DBA_TABLESPACES TS$
DBMS_FEATURE_SECUREFILES_USR GV$PARAMETER V$PARAMETER
DBA_LOBS GV$SECUREFILE_TIMER V$SECUREFILE_TIMER
Create STORE AS SECUREFILE 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>
<DISABLE | ENABLE> STORAGE IN ROW
CHUNK <integer>
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.
 
Preparations and Parameters
Manage securefile initialization parameter

Parameter Options:
  • FORCE
  • PERMITTED (default)
 conn uwclass/uwclass@pdbdev

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 = 'PREFERRED' COMMENT='Altered 15-MAR-2020 for demo' SCOPE=MEMORY;

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

ALTER SYSTEM SET db_securefile = 'PERMITTED' COMMENT='Altered 15-MAR-2020 end of demo' SCOPE=BOTH;
Create Auto Management ASSM Tablespace conn sys@pdbdev 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 Tablespace Quota conn sys@pdbdev 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
Create table with LOB column in securefile tablespace conn uwclass/uwclass@pdbdev

CREATE TABLE secure_file_tab (
rid  NUMBER(5),
bcol BLOB)
LOB (bcol) STORE AS SECUREFILE bcol_lob (
TABLESPACE uwdata
DISABLE  STORAGE IN ROW
CHUNK 8192
RETENTION MIN 3600
KEEP_DUPLICATES
NOCOMPRESS
DECRYPT
CACHE READS);
Create tables using SecureFile Storage conn uwclass/uwclass@pdbdev

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 conn uwclass/uwclass@pdbdev

set linesize 121
col owner format a10
col directory_path format a70

SELECT *
FROM all_directories;

/*
conn sys@pdbdev as sysdba

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

GRANT read ON DIRECTORY ctemp TO uwclass;

conn uwclass/uwclass@pdbdev
*/

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 conn uwclass/uwclass@pdbdev

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;
/
 
Compression
Advanced Compression Demo CREATE TABLE comp_high (
rid  NUMBER(5),
bcol BLOB)
LOB (bcol)
STORE AS SECUREFILE bcol (COMPRESS HIGH) TABLESPACE securefiletbs;

desc user_lobs

SELECT table_name, segment_name, chunk, compression, format, securefile
FROM user_lobs;
 
Deduplication
Deduplication Demo conn uwclass/uwclass@pdbdev

CREATE TABLE uw_blobs(
rid  NUMBER,
bcol BLOB)
LOB (bcol) STORE AS SECUREFILE (TABLESPACE uwdata DEDUPLICATE);

desc user_lobs

SELECT table_name, segment_name, chunk, deduplication, in_row, format, securefile
FROM user_lobs;

Related Topics
DBMS_LOB
DBMS_SECUREFILE_LOG_ADMIN
Large Objects (LOBs)
Partitioned Tables & Indexes
Tables
Transparent Data Encryption
Wallets
What's New In 21c
What's New In 23c

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2023 Daniel A. Morgan All Rights Reserved
  DBSecWorx