Oracle Database 12c features with some enhancements for LOB objects and particularly for the SecureFiles. This is not a revolution, but we can see that the range of possibilities with LOB objects increases with the different releases of Oracle…

SecureFiles is the default for LOB storage

With Oracle 11g, when creating a table containing LOB data, LOB are stored as BASIC FILE per default and the default value for the init parameter DB_SECUREFILE is set to PERMITTED (the creation of SecureFiles is allowed but not automatic). To create SecureFiles on 11g, two solutions are available:

  • It is possible to set the DB_SECUREFILE parameter to ALWAYS, in order to force the system to create LOB files as SecureFiles. In that case, all BasicFile lob storage options are ignored. Because SecureFiles are only supported with ASSM tablespaces, an error is raised if the user try to create a LOB on a non ASSM tablespace. BasicFile becomes purely unavailable.
  • Use the STORE AS SECUREFILE clause when creating the table, as follows:
SQL> CREATE TABLE t1 (a CLOB)
LOB(a) STORE AS SECUREFILE;

 

With Oracle 12c, LOBs are now stored as SecureFiles per default, and the init parameter DB_SECUREFILE has changed:

  • The new PREFERRED value is now the default, if the COMPATIBLE init parameter is set to 12.0.0.0 or higher. PREFERRED makes LOBs to be stored as SecureFiles per default, unless the BASICFILE clause is explicitely used when creating the table, or the tablespace is not ASSM.
  • The ALWAYS value still forces the storage as SecureFiles, however, LOBs are stored as BasicFile if the tablespace is not ASSM, instead of raising an error. BasicFile is not banned.

I checked this new feature on an Oracle 12c database:

SQL> show parameter db_securefile;
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_securefile                        string      PREFERRED

The parameter has the PREFERRED value. Now, what happens if I create a table without specifying the SECUREFILE clause?

SQL> create table t1 (a CLOB);
SQL> select table_name, securefile from user_lobs; 
TABLE_NAME                     SECUREFILE
------------------------------ ---------------
T1                             YES

The LOB has been created as SecureFile.

Note that even if Securefiles is now the default, it does not prevent the customer from buying the license for both Oracle Advanced Compression and Oracle Advance Security options in order to benefit of deduplication, encryption, and compression for SecureFiles.

Oracle Datapump supports SecureFiles as Default

Oracle 12c stores LOBs as SecureFiles per default. In the case where a table or a database using BasicFiles was exported using EXPDP, if you want to import the dump file into a 12c database, Data Pump will try to recreate LOBs exactly as they were stored in the old database. It was not possible to recreate LOBs directly as SecureFiles.

I think you have guessed, Oracle 12c offers a new clause for the impdp tool, in order to convert LOBs stored as BasicFiles to SecureFiles on the fly: TRANSFORM=LOB_STORAGE:SECUREFILE.
To demonstrate this new feature, I firstly created a table containing LOBs stored as Basicfile on an 11g Database.

SQL> connect msc/***
SQL> create table t1 (a BLOB);

Then I created a directory to put the dump file.

SQL> create directory DPUMP_DIR as '/home/oracle';

The path /home/oracle also contains several files. I inserted myfile.txt in the table t1 with the following PL/SQL code:

DECLARE
  src_lob  BFILE := BFILENAME('DPUMP_DIR', 'myfile.txt');
  dest_lob BLOB;
BEGIN
  INSERT INTO t1 VALUES(EMPTY_BLOB())
     RETURNING a INTO dest_lob;  DBMS_LOB.OPEN(src_lob, DBMS_LOB.LOB_READONLY);
  DBMS_LOB.LoadFromFile( DEST_LOB => dest_lob,
                         SRC_LOB  => src_lob,
                         AMOUNT   => DBMS_LOB.GETLENGTH(src_lob) );
  DBMS_LOB.CLOSE(src_lob);  COMMIT;
END;
/

We can see that the LOB has not been stored as SecureFile:

SQL> select table_name, securefile from user_lobs;
TABLE_NAME                     SEC
------------------------------ ---
T1                             NO

Now, I perform the export of the table with the msc user:

$ expdp msc/*** dumpfile=EXPORT_T1.dmp logfile=EXPORT_T1.log directory=DPUMP_DIR tables=T1;

I will try to import this table to a 12c Database by two ways.

1) Without the TRANSFORM clause

$ impdp msc/*** dumpfile=EXPORT_T1.dmp logfile=IMPORT_T1.log directory=DPUMP_DIR table_exists_action=REPLACE

Let’s see the SECUREFILE column in the USER_LOBS view:

SQL> select table_name, securefile from user_lobs;
TABLE_NAME                     SECUREFILE
------------------------------ ---------------
T1                             NO

As expected, Data Pump has recreated the table as it was stored in the 11g Database.

2) With the TRANSFORM clause

$ impdp msc/*** dumpfile=EXPORT_T1.dmp logfile=IMPORT_T1.log directory=DPUMP_DIR table_exists_action=REPLACE transform=LOB_STORAGE:SECUREFILE

This time, we can see that the LOB has been imported as SecureFile:

SQL> select table_name, securefile from user_lobs;
TABLE_NAME                     SECUREFILE
------------------------------ ---------------
T1                             YES

 

Parallel DML supported for LOB stored as SecureFiles on a non-partitioned table

With Oracle 11g, parallel DML is already supported with LOB columns, no matter if it is stored as BasicFile or SecureFile. But it is only supported for partitioned tables.

Oracle 12c offers SecureFiles LOB support enhancements, since it is now possible to perform statements in parallel with LOB columns stored as SecureFiles on a non partitioned table. For LOB stored as BasicFile, the statement still runs in Serial mode, even if the parallel clause is specified.

To demonstrate this new feature, I have created a table T1 on two 11g and 12c databases.

SQL> create table t1 (a BLOB)lob (a) store as SECUREFILE;
 
SQL> select table_name, securefile from user_lobs;
TABLE_NAME                     SEC
------------------------------ ---
T1                             YES

 

DECLARE
  src_lob BFILE := BFILENAME('DPUMP_DIR', 'myfile.txt');
  dest_lob BLOB;
BEGIN
  INSERT INTO t1 VALUES(EMPTY_BLOB()) RETURNING a INTO dest_lob;
  DBMS_LOB.OPEN(src_lob, DBMS_LOB.LOB_READONLY);
  DBMS_LOB.LoadFromFile( DEST_LOB => dest_lob, SRC_LOB => src_lob, AMOUNT => DBMS_LOB.GETLENGTH(src_lob) );
  DBMS_LOB.CLOSE(src_lob);
  COMMIT;
END;
/

11g
Now let’s see what happens on the 11g database if I try to perform an INSERT AS SELECT statement in parallel. I used the dbms_xplan package to see the execution plan of the statement.

SQL> set lines 200
SQL> alter session force parallel dml;
SQL> explain plan for insert into t1 select * from t1;
Explained.
 
SQL> select * from table(dbms_xplan.display);

plan_11g

Here we can see that the SELECT statement has been performed in parallel (it corresponds to the TABLE ACCESS FULL operation with ID 5), however the insertion of data was performed in serial, since the LOAD TABLE CONVENTIONAL operation (id 1) is above the PX COORDINATOR.

12c
I perform exactly same operations on the 12c database.

SQL> set lines 200
SQL> alter session force parallel dml;
SQL> explain plan for 2 insert into t1 select * from t1;
Explained.
 
SQL> select * from table(dbms_xplan.display);

plan_12c

This time, we can see that the LOAD AS SELECT operation has been performed in parallel mode, since it is located below the PX COORDINATOR operation on this execution plan.
A word about Oracle documentation: in the Oracle Database SecureFiles and Large Objects Developer’s Guide, we can read the following:

Oracle supports parallel execution of most of the following DML operations when performed on partitioned tables with SecureFiles LOBs or BasicFiles LOBs, and non-partitioned tables with SecureFiles LOBs only

  • INSERT
  • INSERT AS SELECT
  • CREATE TABLE AS SELECT
  • DELETE
  • UPDATE
  • MERGE (conditional UPDATE and INSERT)

[…]

Most of the following DML” does not necessary mean that all of these DML are supported. I performed some tests and I could only run INSERT AS SELECT, DELETE, UPDATE and CREATE TABLE AS SELECT (which is a DDL in fact) statements in parallel with SECUREFILE on a range partitioned table. On a non-partitioned table, only INSERT AS SELECT worked in parallel…