The DESCRIBE command on an Oracle table is not sufficient to determine whether a LOB column is stored as a SECUREFILE or a regular old BASICFILE. Instead you must query USER_LOBS (or DBA_LOBS etc), or alternatively leverage the PL/SQL dbms_lob.issecurefile function.
% sqlplus
SQL*Plus: Release 11.2.0.1.0 Production on Tue Mar 19 18:04:53 2013
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Enter user-name: / as sysdba
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> show parameter compatible;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
compatible string 11.2.0.0.0
SQL> show parameter db_securefile;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_securefile string PERMITTED
The DB_SECUREFILE parameter specifies whether or not to treat LOB files as SecureFiles by default.
NEVER - LOBs that are specified as SecureFiles are created as BasicFile LOBs. Any SecureFile-specific storage options specified will result in an exception.
PERMITTED - LOBs are allowed to be created as SecureFiles, but will be created as BasicFile by default.
ALWAYS - All LOBs created in the system are created as SecureFile LOBs.
IGNORE - The SECUREFILE keyword and all SecureFile options are ignored.
If the COMPATIBLE parameter is not set to 11.1 or higher, then LOBs are not treated as SecureFiles.
SQL> create user matt identified by welcome1;
User created.
SQL> grant create session to matt;
Grant succeeded.
SQL> grant create table to matt;
Grant succeeded.
SQL> grant unlimited tablespace to matt;
Grant succeeded.
SQL> conn matt/welcome1
Connected.
SQL>
CREATE TABLE test(
lob1 BLOB
,lob2 BLOB
,lob3 BLOB
,lob4 BLOB
)
LOB(lob2) STORE AS BASICFILE
,LOB(lob3) STORE AS SECUREFILE
,LOB(lob4) STORE AS SECUREFILE (
ENABLE STORAGE IN ROW
NOCACHE LOGGING
COMPRESS MEDIUM
DEDUPLICATE
)
/
Table created.
Logging options:
LOGGING - LOB changes generate full entries in redo logs
NOLOGGING - LOB changes are not logged in the redo logs and cannot be replayed in the event of failure.
Caching options
CACHE - LOB data is placed in the buffer cache.
CACHE READS - LOB data is only placed in the buffer cache only during read operations but not during write operations.
NOCACHE - LOB data is not placed in the buffer cache, or brought in to the buffer cache and placed at the least recently used end of the LRU list.
SecureFile LOBs also support FILESYSTEM_LIKE_LOGGING logging option which is similar to metadata journaling of file systems
SQL> desc test;
Name Null? Type
----------------------------------------- -------- ----------------------------
LOB1 BLOB
LOB2 BLOB
LOB3 BLOB
LOB4 BLOB
SQL> desc user_lobs;
Name Null? Type
----------------------------------------- -------- ----------------------------
TABLE_NAME VARCHAR2(30)
COLUMN_NAME VARCHAR2(4000)
SEGMENT_NAME VARCHAR2(30)
TABLESPACE_NAME VARCHAR2(30)
INDEX_NAME VARCHAR2(30)
CHUNK NUMBER
PCTVERSION NUMBER
RETENTION NUMBER
FREEPOOLS NUMBER
CACHE VARCHAR2(10)
LOGGING VARCHAR2(7)
ENCRYPT VARCHAR2(4)
COMPRESSION VARCHAR2(6)
DEDUPLICATION VARCHAR2(15)
IN_ROW VARCHAR2(3)
FORMAT VARCHAR2(15)
PARTITIONED VARCHAR2(3)
SECUREFILE VARCHAR2(3)
SEGMENT_CREATED VARCHAR2(3)
set linesize 100
col Column format a6
col isSecureFile format a12
col Compressed format a10
col DeDuplicated format a12
col Encrypted format a9
col StoredInRow format a11
col Logging format a7
col Cached format a10
SELECT
column_name as "Column"
,securefile as "isSecureFile"
,compression as "Compressed"
,deduplication as "DeDuplicated"
,encrypt as "Encrypted"
,in_row as "StoredInRow"
,logging as "Logging"
,cache as "Cached"
FROM user_lobs
WHERE table_name = 'TEST'
Column isSecureFile Compressed DeDuplicated Encrypted StoredInRow Logging Cached
------ ------------ ---------- ------------ --------- ----------- ------- ----------
LOB1 NO NONE NONE NONE YES YES NO
LOB2 NO NONE NONE NONE YES YES NO
LOB3 YES NO NO NO YES YES NO
LOB4 YES MEDIUM LOB NO YES YES NO
SQL>
insert into test values(empty_blob(), empty_blob(), empty_blob(), empty_blob())
set serveroutput on
DECLARE
l1 BLOB; l2 BLOB; l3 BLOB; l4 BLOB;
BEGIN
SELECT lob1, lob2, lob3, lob4
INTO l1, l2, l3, l4
FROM test
WHERE rownum = 1;
IF dbms_lob.issecurefile(l1) THEN
dbms_output.put_line('Stored in a securefile');
ELSE
dbms_output.put_line('Not stored in a securefile');
END IF;
IF dbms_lob.issecurefile(l2) THEN
dbms_output.put_line('Stored in a securefile');
ELSE
dbms_output.put_line('Not stored in a securefile');
END IF;
IF dbms_lob.issecurefile(l3) THEN
dbms_output.put_line('Stored in a securefile');
ELSE
dbms_output.put_line('Not stored in a securefile');
END IF;
IF dbms_lob.issecurefile(l4) THEN
dbms_output.put_line('Stored in a securefile');
ELSE
dbms_output.put_line('Not stored in a securefile');
END IF;
END;
/
Not stored in a securefile
Not stored in a securefile
Stored in a securefile
Stored in a securefile
PL/SQL procedure successfully completed.
No comments:
Post a Comment