Oracle SecureFile Lob - Speeding Up Lob import from 11g to 12c

This  blog is mainly to give insight of SecureFile .We  all  face issue  for Lob that  though we  use multiple parallelism  for Lob import , it only uses 1 channel for import of Lob data. However if we  convert to SecureFile  while import  it used multiple channels .

In Oracle 12C it’s possible to define LOB storage method during import time independent from export settings. It gives more flexibility during importing data.

If the COMPATIBLE parameter is set to "12.0.0.0" or higher, the default value for the DB_SECUREFILE parameter is "PREFERRED", which means all LOBs will be created as SecureFiles, unless they are explicitly created as BasicFiles or their tablespace uses Manual Segment Space Management.

Prior to 12cR1 Parallel DML (PDML) had a number of restrictions when used against a table containing LOBs (BasicFile or SecureFile). PDML was only supported for the following operations and only if the table containing the LOB was partitioned.

INSERT INTO ... AS SELECT ...
CREATE TABLE ... AS SELECT ...
DELETE
UPDATE
MERGE
Multitable INSERT

From Oracle Database 12c Release 1 (12.1) onward Oracle supports more parallel operations against partitioned tables containing LOBs (SecureFile and BasicFile).

INSERT
INSERT INTO ... AS SELECT ...
CREATE TABLE ... AS SELECT ...
DELETE
UPDATE
MERGE
Multitable INSERT
SQL*Loader
Import/Export

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.


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

SQL> select securefile from user_lobs where table_name='TBL_LOB_TEST';
SEC
---
YES

impdp .. TRANSFORM=LOB_STORAGE:SECUREFILE|BASICFILE|DEFAULT|NO_CHANGE

DEFAULT – no lob storage clause is set for CREATE TABLE

NO_CHANGE – use settings from dump file

BASICFILE – creates LOBs as basicfile

SECUREFILE – creates LOBs as securefile

Can be set in PL/SQL using DBMS_DATAPUMP.METADATA_TRANSFORM

Example

Imports data and sets LOBs as SECUREFILE

impdp hr/hr DIRECTORY=dpdump_dir DUMPFILE=hr.dmp

TRANSFORM=LOB_STORAGE:SECUREFILE

Reference : 

DataPump Export/Import Of LOBs Are Not Executed in Parallel (Doc ID 1467662.1)