Search this blog ...

Wednesday, March 20, 2013

How to determine if an Oracle LOB is stored as a SECUREFILE or BASICFILE

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