| General Information |
| Note |
SecureFiles are part of Oracle's Advanced Compression license option. |
| Wallet |
To run the demos on this page a wallet must exist and must be open: See link at page bottom |
| 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:\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); |
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 SecureFiles Demo |
CREATE TABLE comp_high (
rid NUMBER(5),
bcol BLOB)
LOB (bcol)
STORE AS SECUREFILE bcol (COMPRESS HIGH)
TABLESPACE securefiletbs; |
|
