Oracle SecureFile LOBs in 11G – Oracle Database 11G release 2 (11.2)

This article describes new feature of 11g SecureFile LOB.

In new version 11g Oracle decided to create new type large objects (LOB) called Secure Files. It offers many new benefits versus old version of LOB called in 11g release BasicFile:

  • performance
  • intelligent compression
  • transparent decryption
  • easier management and development

Issues in previous version

  • LOB instantiation was expected to be several megabytes
  • designed to be “write once read many times”
  • small number of updates
  • OLTP workload was not anticipated
  • undo space managed by two parameters PCTVERSION and RETENTION
  • CHUNK size is static parameter and anticipates LOB sizes are typically uniform
  • High concurrency writes in Oracle RAC was not anticipated
  • Low concurrency for DML

New architecture features

  • chunks bigger than oracle data blocks up to 64MB
  • oracle tries to keep data in adjacent locations on disk to minimizes internal fragmentation
  • by using variable chunk sizes, SecureFiles avoids versioning of large unnecessary data
  • higher read and write performance
  • better optimized network layer
  • better optimized usage of UNDO and REDO data
  • better optimized memory usage
  • can be created only in ASSM tablespaces

Storage Options

For SecureFiles following storage options can be defined:

  • MAXSIZE – defines maximum LOB size
  • RETENTION – defines retention policy
    • AUTO – selects MIN, MAX or NONE automatically – DEFAULT
    • MIN – keep old versions at least MIN seconds
    • MAX – keep old versions until MAXSIZE is reached
    • NONE – reuse old versions as much as possible

Following storage options are not used for SecureFiles:

  • FREELIST, FREELIST GROUPS, FREEPOOLS, CHUNK, PCTVERSION

DB_SECUREFILE

NOTE SecureFiles can be created only in ASSM tablespaces in other case exception is raised

New initialization parameter has appeared in 11G DB_SECUREFILE that allows determine usage of SecureFiles:

  • ALWAYS – try to create always SecureFiles LOB in ASSM tablespaces or BasicFiles in NON ASSM tablespaces
  • FORCE – forces to create always SecureFiles LOB
  • PERMITTED – allows to create SecureFiles – default option
  • NEVER – disallows to create SecureFiles, All specific SecureFile storage options and features (for example, compress, encrypt, deduplicate) will throw an exception
  • IGNORE – disallows to create SecureFiles and ignores any errors that would caused by forcing BasicFiles with SecureFiles options

Examples

Two tablespaces are created ASSM and no ASSM for test purposes:

  • users_assm – auto segment space management tablespace. SecureFiles LOB can be created here.
  • users_noassm – manual segment space management tablespace. SecureFiles LOB can’t be created here

BasicFiles LOB can be created in each of the tablespaces

CREATE TABLESPACE users_assm
DATAFILE 'D:\APP\ORACLE\ORADATA\ORA11G\USERS_ASSM01.DBF' SIZE 100m 
SEGMENT SPACE MANAGEMENT AUTO;

CREATE TABLESPACE users_noassm
DATAFILE 'D:\APP\ORACLE\ORADATA\ORA11G\USERS_NOASSM01.DBF' SIZE 100m 
SEGMENT SPACE MANAGEMENT MANUAL;

DB_SECUREFILE=’ALWAYS’

ALWAYS – try to create always as default SecureFiles LOB in ASSM tablespaces or BasicFiles in NON ASSM tablespaces

Let’s change parameter in session to check ALWAYS option

ALTER SESSION SET db_securefile = 'ALWAYS';

run test

CREATE TABLE tbl_def_assm(id1 CLOB)
LOB(id1) STORE AS (TABLESPACE users_assm);

CREATE TABLE tbl_def_noassm(id1 CLOB)
LOB(id1) STORE AS (TABLESPACE users_noassm);

CREATE TABLE tbl_eb_assm(id1 CLOB)
LOB(id1) STORE AS BASICFILE (TABLESPACE users_assm);

CREATE TABLE tbl_eb_noassm(id1 CLOB)
LOB(id1) STORE AS BASICFILE (TABLESPACE users_noassm);

CREATE TABLE tbl_es_assm(id1 CLOB)
LOB(id1) STORE AS SECUREFILE (TABLESPACE users_assm);

CREATE TABLE tbl_es_noassm(id1 CLOB)
LOB(id1) STORE AS SECUREFILE (TABLESPACE users_noassm);

SQL Error: ORA-43853: SECUREFILE lobs cannot be used 
in non-ASSM tablespace "USERS_NOASSM"

check results

SELECT table_name, column_name, tablespace_name, securefile
  FROM user_lobs
WHERE table_name like 'TBL%ASSM'
ORDER BY table_name;

TABLE_NAME      COLUMN_NAME  TABLESPACE_NAME  SECUREFILE
--------------- ------------ ---------------- ------------
TBL_DEF_ASSM    ID1          USERS_ASSM       YES
TBL_DEF_NOASSM  ID1          USERS_NOASSM     NO
TBL_EB_ASSM     ID1          USERS_ASSM       YES
TBL_EB_NOASSM   ID1          USERS_NOASSM     NO
TBL_ES_ASSM     ID1          USERS_ASSM       YES

clean

DROP TABLE tbl_def_assm;
DROP TABLE tbl_def_noassm;
DROP TABLE tbl_eb_assm;
DROP TABLE tbl_eb_noassm;
DROP TABLE tbl_es_assm;

DB_SECUREFILE=’FORCE’

FORCE – forces to create always SecureFiles LOB

Let’s change parameter in session to check FORCE option

ALTER SESSION SET db_securefile = 'FORCE';

run test

CREATE TABLE tbl_def_assm(id1 CLOB)
LOB(id1) STORE AS (TABLESPACE users_assm);

CREATE TABLE tbl_def_noassm(id1 CLOB)
LOB(id1) STORE AS (TABLESPACE users_noassm);

SQL Error: ORA-43853: SECUREFILE lobs cannot be used 
in non-ASSM tablespace "USERS_NOASSM"

CREATE TABLE tbl_eb_assm(id1 CLOB)
LOB(id1) STORE AS BASICFILE (TABLESPACE users_assm);

CREATE TABLE tbl_eb_noassm(id1 CLOB)
LOB(id1) STORE AS BASICFILE (TABLESPACE users_noassm);

SQL Error: ORA-43853: SECUREFILE lobs cannot be used 
in non-ASSM tablespace "USERS_NOASSM"

CREATE TABLE tbl_es_assm(id1 CLOB)
LOB(id1) STORE AS SECUREFILE (TABLESPACE users_assm);

CREATE TABLE tbl_es_noassm(id1 CLOB)
LOB(id1) STORE AS SECUREFILE (TABLESPACE users_noassm);

SQL Error: ORA-43853: SECUREFILE lobs cannot be used 
in non-ASSM tablespace "USERS_NOASSM"

check results

SELECT table_name, column_name, tablespace_name, securefile
  FROM user_lobs
WHERE table_name like 'TBL%ASSM'  
ORDER BY table_name;

SELECT table_name, column_name, tablespace_name, securefile
  FROM user_lobs
WHERE table_name like 'TBL%ASSM'
ORDER BY table_name;

TABLE_NAME      COLUMN_NAME  TABLESPACE_NAME  SECUREFILE
--------------- ------------ ---------------- ------------
TBL_DEF_ASSM    ID1          USERS_ASSM       YES
TBL_EB_ASSM     ID1          USERS_ASSM       YES
TBL_ES_ASSM     ID1          USERS_ASSM       YES

clean

DROP TABLE tbl_def_assm;
DROP TABLE tbl_eb_assm;
DROP TABLE tbl_es_assm;

DB_SECUREFILE=’PERMITTED’

PERMITTED – allows to create SecureFiles – default option

Let’s change parameter in session to check PERMITTED option

ALTER SESSION SET db_securefile = 'PERMITTED';

run test

CREATE TABLE tbl_def_assm(id1 CLOB)
LOB(id1) STORE AS (TABLESPACE users_assm);

CREATE TABLE tbl_def_noassm(id1 CLOB)
LOB(id1) STORE AS (TABLESPACE users_noassm);

CREATE TABLE tbl_eb_assm(id1 CLOB)
LOB(id1) STORE AS BASICFILE (TABLESPACE users_assm);

CREATE TABLE tbl_eb_noassm(id1 CLOB)
LOB(id1) STORE AS BASICFILE (TABLESPACE users_noassm);

CREATE TABLE tbl_es_assm(id1 CLOB)
LOB(id1) STORE AS SECUREFILE (TABLESPACE users_assm);

CREATE TABLE tbl_es_noassm(id1 CLOB)
LOB(id1) STORE AS SECUREFILE (TABLESPACE users_noassm);

SQL Error: ORA-43853: SECUREFILE lobs cannot be used 
in non-ASSM tablespace "USERS_NOASSM"

check results

SELECT table_name, column_name, tablespace_name, securefile
  FROM user_lobs
WHERE table_name like 'TBL%ASSM'
ORDER BY table_name;

TABLE_NAME      COLUMN_NAME  TABLESPACE_NAME  SECUREFILE
--------------- ------------ ---------------- ------------
TBL_DEF_ASSM    ID1          USERS_ASSM       NO
TBL_DEF_NOASSM  ID1          USERS_NOASSM     NO
TBL_EB_ASSM     ID1          USERS_ASSM       NO
TBL_EB_NOASSM   ID1          USERS_NOASSM     NO
TBL_ES_ASSM     ID1          USERS_ASSM       YES

clean

DROP TABLE tbl_def_assm;
DROP TABLE tbl_def_noassm;
DROP TABLE tbl_eb_assm;
DROP TABLE tbl_eb_noassm;
DROP TABLE tbl_es_assm;

DB_SECUREFILE=’NEVER’

NEVER – disallows to create SecureFiles, All specific SecureFile storage options and features (for example, compress, encrypt, deduplicate) will throw an exception

Let’s change parameter in session to check NEVER option

ALTER SESSION SET db_securefile = 'NEVER';

run test

CREATE TABLE tbl_def_assm(id1 CLOB)
LOB(id1) STORE AS (TABLESPACE users_assm);

CREATE TABLE tbl_def_noassm(id1 CLOB)
LOB(id1) STORE AS (TABLESPACE users_noassm);

CREATE TABLE tbl_eb_assm(id1 CLOB)
LOB(id1) STORE AS BASICFILE (TABLESPACE users_assm);

CREATE TABLE tbl_eb_noassm(id1 CLOB)
LOB(id1) STORE AS BASICFILE (TABLESPACE users_noassm);

CREATE TABLE tbl_es_assm(id1 CLOB)
LOB(id1) STORE AS SECUREFILE (COMPRESS TABLESPACE users_assm);

SQL Error: ORA-43856: Unsupported LOB type 
for SECUREFILE LOB operation
CREATE TABLE tbl_es_noassm(id1 CLOB)
LOB(id1) STORE AS SECUREFILE (COMPRESS TABLESPACE users_noassm);

SQL Error: ORA-43856: Unsupported LOB type 
for SECUREFILE LOB operation

CREATE TABLE tbl_es_assm(id1 CLOB)
LOB(id1) STORE AS SECUREFILE (TABLESPACE users_assm);

CREATE TABLE tbl_es_noassm(id1 CLOB)
LOB(id1) STORE AS SECUREFILE (TABLESPACE users_noassm);

check results

SELECT table_name, column_name, tablespace_name, securefile
  FROM user_lobs
WHERE table_name like 'TBL%ASSM'
ORDER BY table_name;

TABLE_NAME      COLUMN_NAME  TABLESPACE_NAME  SECUREFILE
--------------- ------------ ---------------- ------------
TBL_DEF_ASSM    ID1          USERS_ASSM       NO
TBL_DEF_NOASSM  ID1          USERS_NOASSM     NO
TBL_EB_ASSM     ID1          USERS_ASSM       NO
TBL_EB_NOASSM   ID1          USERS_NOASSM     NO
TBL_ES_ASSM     ID1          USERS_ASSM       NO
TBL_ES_ASSM     ID1          USERS_ASSM       NO

clean

DROP TABLE tbl_def_assm;
DROP TABLE tbl_def_noassm;
DROP TABLE tbl_eb_assm;
DROP TABLE tbl_eb_noassm;
DROP TABLE tbl_es_assm;
DROP TABLE tbl_es_noassm;

DB_SECUREFILE=’IGNORE’

IGNORE – disallows to create SecureFiles and ignores any errors that would caused by forcing BasicFiles with SecureFiles options

Let’s change parameter in session to check IGNORE option

ALTER SESSION SET db_securefile = 'IGNORE';

run test

CREATE TABLE tbl_def_assm(id1 CLOB)
LOB(id1) STORE AS (TABLESPACE users_assm);

CREATE TABLE tbl_def_noassm(id1 CLOB)
LOB(id1) STORE AS (TABLESPACE users_noassm);

CREATE TABLE tbl_eb_assm(id1 CLOB)
LOB(id1) STORE AS BASICFILE (TABLESPACE users_assm);

CREATE TABLE tbl_eb_noassm(id1 CLOB)
LOB(id1) STORE AS BASICFILE (TABLESPACE users_noassm);

CREATE TABLE tbl_es_assm(id1 CLOB)
LOB(id1) STORE AS SECUREFILE (COMPRESS TABLESPACE users_assm);

CREATE TABLE tbl_es_noassm(id1 CLOB)
LOB(id1) STORE AS SECUREFILE (COMPRESS TABLESPACE users_noassm);

check results

SELECT table_name, column_name, tablespace_name, securefile
  FROM user_lobs
 WHERE table_name like 'TBL%ASSM'
 ORDER BY table_name;

TABLE_NAME      COLUMN_NAME  TABLESPACE_NAME  SECUREFILE
--------------- ------------ ---------------- ------------
TBL_DEF_ASSM    ID1          USERS_ASSM       NO
TBL_DEF_NOASSM  ID1          USERS_NOASSM     NO
TBL_EB_ASSM     ID1          USERS_ASSM       NO
TBL_EB_NOASSM   ID1          USERS_NOASSM     NO
TBL_ES_ASSM     ID1          USERS_ASSM       NO
TBL_ES_ASSM     ID1          USERS_ASSM       NO

clean

DROP TABLE tbl_def_assm;
DROP TABLE tbl_def_noassm;
DROP TABLE tbl_eb_assm;
DROP TABLE tbl_eb_noassm;
DROP TABLE tbl_es_assm;
DROP TABLE tbl_es_noassm;

Deduplication

New storage parameter DEDUPLIACATE can be specified for SecureFiles. It enables detection of repeated data in LOB columns for a table/partition and save it only once. Parameter KEEP_DEDUPLICATE prevents deduplication.

ALTER SESSION SET db_securefile = 'ALWAYS';

CREATE TABLE tbl_dd_assm
(
  id1 CLOB,
  id2 CLOB,
  id3 CLOB
)
LOB(id1) STORE AS SECUREFILE 
    id1_l (DEDUPLICATE TABLESPACE users_assm)
LOB(id2) STORE AS SECUREFILE 
    id2_l (KEEP_DUPLICATES TABLESPACE users_assm)
LOB(id3) STORE AS SECUREFILE 
    id3_l (TABLESPACE users_assm);

check

SELECT 
    table_name, column_name, segment_name, 
    securefile, deduplication
  FROM user_lobs
WHERE table_name = 'TBL_DD_ASSM'  
ORDER BY table_name;

TABLE_NAME   COLUMN_NAME  SEGMENT_NAME  SECUREFILE  DEDUPLICATION
------------ ------------ ------------- ----------- --------------
TBL_DD_ASSM  ID1          ID1_DEDU      YES         LOB
TBL_DD_ASSM  ID2          ID2_KEDE      YES         NO
TBL_DD_ASSM  ID3          ID3_DEF       YES         NO

load data

BEGIN
  FOR i IN 1..1000
  LOOP
    INSERT INTO tbl_dd_assm 
    (
      id1,
      id2,
      id3
    )
    VALUES
    (
      rpad('*',10000, '*'),
      rpad('*',10000, '*'),
      rpad('*',10000, '*')
    );
  END LOOP;
  
  COMMIT;
END;
/

As expected segment ID1_DEDU with DEDUPLICATE is very small

SELECT segment_name, segment_subtype, bytes 
  FROM user_segments 
 WHERE segment_name IN ( 'ID1_DEDU', 'ID2_KEDE', 'ID3_DEF' );

SEGMENT_NAME  SEGMENT_SUBTYPE  BYTES
------------- ---------------- --------
ID1_DEDU      SECUREFILE        1245184
ID2_KEDE      SECUREFILE       10682368
ID3_DEF       SECUREFILE       10682368

clean

DROP TABLE tbl_dd_assm;

Compression

Three types of compression are available for SecureFiles:

  • LOW – recommended for fast writes, reads
  • MEDIUM – default for low latency systems
  • HIGH – CPU intensive, high latency systems

Compression requires Advanced Compression Option

Example

CREATE TABLE tbl_nocomp
(
  id1  NUMBER,
  id2  CLOB
) 
LOB(id2) STORE AS SECUREFILE 
tbl_nocomp_id2 (NOCOMPRESS TABLESPACE users_assm);

CREATE TABLE tbl_comp_low 
(
  id1  NUMBER,
  id2  CLOB
) 
LOB(id2) STORE AS SECUREFILE 
tbl_comp_low_id2 (COMPRESS LOW TABLESPACE users_assm);

CREATE TABLE tbl_comp_med 
(
  id1  NUMBER,
  id2  CLOB
) 
LOB(id2) STORE AS SECUREFILE
tbl_comp_med_id2 (COMPRESS MEDIUM TABLESPACE users_assm);

CREATE TABLE tbl_comp_high 
(
  id1  NUMBER,
  id2  CLOB
) 
LOB(id2) STORE AS SECUREFILE
tbl_comp_high_id2 (COMPRESS HIGH TABLESPACE users_assm);

load test data

INSERT /*+ APPEND */ INTO tbl_nocomp
SELECT LEVEL, rpad('X', 10000, 'X') 
  FROM dual
CONNECT BY LEVEL < 1001;

COMMIT;

INSERT /*+ APPEND */ INTO tbl_comp_low
SELECT LEVEL, rpad('X', 10000, 'X') 
  FROM dual
CONNECT BY LEVEL < 1001;

COMMIT;

INSERT /*+ APPEND */ INTO tbl_comp_med
SELECT LEVEL, rpad('X', 10000, 'X') 
  FROM dual
CONNECT BY LEVEL < 1001;

COMMIT;

INSERT /*+ APPEND */ INTO tbl_comp_high
SELECT LEVEL, rpad('X', 10000, 'X') 
  FROM dual
CONNECT BY LEVEL < 1001;

COMMIT;

collects stats

exec dbms_stats.gather_table_stats(USER, 'TBL_NOCOMP');
exec dbms_stats.gather_table_stats(USER, 'TBL_COMP_HIGH');
exec dbms_stats.gather_table_stats(USER, 'TBL_COMP_MED');
exec dbms_stats.gather_table_stats(USER, 'TBL_COMP_LOW');

check results. I didn’t load complex data so there is no difference in space consumption for LOW, MEDIUM, HIGH

SELECT 
    table_name, column_name, securefile, compression
  FROM user_lobs
 WHERE table_name 
    IN ( 'TBL_NOCOMP', 'TBL_COMP_HIGH', 
         'TBL_COMP_MED', 'TBL_COMP_LOW' )
 ORDER BY table_name;

TABLE_NAME     COLUMN_NAME  SECUREFILE  COMPRESSION
-------------- ------------ ----------- ------------
TBL_COMP_HIGH  ID2          YES         HIGH
TBL_COMP_LOW   ID2          YES         LOW
TBL_COMP_MED   ID2          YES         MEDIUM
TBL_NOCOMP     ID2          YES         NO
SELECT segment_name, segment_type, segment_subtype, bytes
  FROM   user_segments
 WHERE  segment_name 
    IN ( 'TBL_NOCOMP_ID2', 'TBL_COMP_HIGH_ID2', 
         'TBL_COMP_MED_ID2', 'TBL_COMP_LOW_ID2' );

SEGMENT_NAME       SEGMENT_TYPE  SEGMENT_SUBTYPE  BYTES
------------------ ------------- ---------------- ------
TBL_COMP_HIGH_ID2  LOBSEGMENT    SECUREFILE         131072
TBL_COMP_LOW_ID2   LOBSEGMENT    SECUREFILE         131072
TBL_COMP_MED_ID2   LOBSEGMENT    SECUREFILE         131072
TBL_NOCOMP_ID2     LOBSEGMENT    SECUREFILE       10682368

clean

DROP TABLE tbl_comp_high;
DROP TABLE tbl_comp_med;
DROP TABLE tbl_comp_low;
DROP TABLE tbl_nocomp;

Encryption

SecureFiles encryption uses Transparent Data Enryption (TDE) feature. Following encryption algorithms can be used:

  • 3DES168 – triple data encryption standard with 168-bit key size
  • AES128 – advanced encryption standard with 128-bit key size
  • AES192 – advanced encryption standard with 192-bit key size – DEFAULT
  • AES256 – advanced encryption standard with 256-bit key size

Following keywords are used

  • ENCRYPT
  • DECRYPT

Wallet must be setup to use this feature

TDE wallet management

create directory $ORACLE_BASE\admin\wallet

mkdir $ORACLE_HOME\admin\wallet

logon as user sys and set encryption master key. It will create wallet file “ewallet.p12” in above directory

sqlplus / as sysdba
SQL> ALTER SYSTEM SET ENCRYPTION KEY IDENTIFIED BY "oracle";

Encryption securefile

Now it’s time to test encryption

CREATE TABLE test_tbl_enc_def
(
  id1 CLOB
)
LOB(id1) 
STORE AS SECUREFILE (ENCRYPT);

CREATE TABLE test_tbl_enc
(
  id1 CLOB
)
LOB(id1) 
STORE AS SECUREFILE 
(ENCRYPT USING 'AES128' IDENTIFIED BY 'dbaora.com');


Rekey table

ALTER TABLE test_tbl_enc REKEY USING 'AES192';

Check in views

SELECT table_name, column_name, encrypt, securefile
  FROM user_lobs
 WHERE table_name IN ('TEST_TBL_ENC_DEF', 'TEST_TBL_ENC');

TABLE_NAME        COLUMN_NAME  ENCRYPT  SECUREFILE
----------------- ------------ -------- -----------
TEST_TBL_ENC      ID1          YES      YES
TEST_TBL_ENC_DEF  ID1          YES      YES

Decription

ALTER TABLE test_tbl_enc 
MODIFY LOB(id1) (DECRYPT);

Caching and logging

Caching is just buffering LOB segments in memory. It speeds up operations executed on LOBs.

Following settings are available for caching:

  • CACHE – LOB segments are buffered in buffer cache
  • CACHE READS – LOB segments are buffered in buffer cache for read operations only
  • NOCACHE – DEFAULT option, LOB segments are not buffered

Logging defines how modification in LOB data are copied to redo stream.

Following settings that can be used for SecureFiles:

  • NOLOGGING – changes are not copied to redo logs
  • LOGGING – changes are copied to redo logs
  • FILESYSTEM_LIKE_LOGGING – only metadata is logged, but still recovery is possible for affected LOB
CREATE TABLE test_tbl_cl
(
  id1    CLOB
) 
LOB(id1) STORE AS SECUREFILE
(
  NOCACHE
  LOGGING
);
ALTER TABLE test_tbl_cl
MODIFY LOB(ID1) 
(
  CACHE
  FILESYSTEM_LIKE_LOGGING
);

Migrate BasicFile LOB to the SecureFile LOB

It can be done only manually

  • CREATE TABLE … AS SELECT …
  • copy to new table INSERT INTO … SELECT …
  • Online table redefintion.
  • Export/Import
  • Operations on columns ALTER TABLE … MOVE LOB … STORE AS SECUREFILE
  • Add new column SECUREFILE and copy data from BASICFILE

Extra database parameters

To avoid OS file system buffering especially for CLOB, instance parameter FILESYSTEMIO_OPTIONS should be set

ALTER SESSION SET filesystemio_options=setall;

PLSQL addons

Dummy data

CREATE TABLE test_tbl_plsql
(
  id1    CLOB
) 
LOB(id1) STORE AS SECUREFILE 
( 
  ENCRYPT 
  COMPRESS
  CACHE
  LOGGING
);

INSERT INTO test_tbl_plsql VALUES('Test data');
COMMIT;

DBMS_LOB has got extra functions to get new properties dedicated for securefiles

SET SERVEROUTPUT ON
DECLARE
  l_clob  CLOB;
BEGIN
  SELECT id1 INTO l_clob 
    FROM test_tbl_plsql
   WHERE  rownum = 1;

  DBMS_OUTPUT.put_line('Compress : ' || 
    DBMS_LOB.getoptions(l_clob, DBMS_LOB.opt_compress));
  DBMS_OUTPUT.put_line('Dedup    : ' || 
    DBMS_LOB.getoptions(l_clob, DBMS_LOB.opt_deduplicate));
  DBMS_OUTPUT.put_line('Encrypt  : ' || 
    DBMS_LOB.getoptions(l_clob, DBMS_LOB.opt_encrypt));
END;
/

Compress : 1
Dedup    : 0
Encrypt  : 2

Have a fun 🙂

Tomasz

Leave a Reply

Your email address will not be published. Required fields are marked *