PSOUG Home Code Snippets Oracle Lookup Oracle Reference Oracle Error Codes Oracle Functions PSOUG Forum


 
 Search the Reference Library pages:  

Oracle SecureFiles
Version 11.1
 


General
Related Data Dictionary Objects
ts$ dba_tablespaces dba_lobs gv$parameter

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: emp\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 Wallet directory in operating system		 -- Note: This step is identical with the one performed with TRANSPARENT -- DATA ENCRYPTION. if a wallet already exists skip this step.

host

-- mkdir $ORACLE_BASE\admin\<SID>\wallet
mkdir $ORACLE_BASE\admin\orabase\wallet

exit

Alter SQLNET.ORA file		 -- Note: This step is identical with the one performed with TRANSPARENT -- DATA ENCRYPTION. if a wallet already exists skip this step.

SQLNET.AUTHENTICATION_SERVICES= (NTS)

NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

ENCRYPTION_WALLET_LOCATION = (SOURCE = (METHOD=FILE) (METHOD_DATA = (DIRECTORY = c:\oracle\product\admin\orabase\wallet)))

-- Note: if you do not use this wallet location you will likely
-- receive ORA-28368: cannot auto-create wallet when setting the key

Set Encryption Key		 conn uwclass/uwclass

ALTER SYSTEM SET ENCRYPTION KEY AUTHENTICATED BY "N0way!";
At startup ... or if the Wallet is not open ALTER SYSTEM SET ENCRYPTION WALLET OPEN IDENTIFIED BY "N0way!";

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);
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: emp';

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 SecureFiles Demo		 CREATE TABLE comp_high (
rid  NUMBER(5),
bcol BLOB)
LOB (bcol)
STORE AS SECUREFILE bcol (COMPRESS HIGH)
TABLESPACE securefiletbs;
 
Related Topics
DBMS_LOB
Large Objects (LOBs)
 
   Home |    Search |    Code Library |    Sponsors |    Privacy |    Terms of Use |    Contact Us    © 2003 - 2024 psoug.org
-----