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 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
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
Using shell to parallelize import of basic file :
I see in below article people has shared shell script used for basic file to parallelize import
https://www.linkedin.com/pulse/optimising-lob-export-import-performance-via-oracle-datapump-arya/
#!/bin/bash
chunk=10
for ((i=0;i<=9;i++));
do
expdp USERNAME/Password@DB_NAME TABLES=LOB_TEST QUERY=LOB_TEST:\"where mod\(dbms_rowid.rowid_block_number\(rowid\)\, ${chunk}\) = ${i}\" directory=DMP dumpfile=lob_test_${i}.dmp logfile= log_test_${i}.log &
echo $i
done
Or
#!/bin/bash
chunk=10
for ((i=0;i<=9;i++));
do
impdp USERNAME/Password@DB_NAME directory=DMP REMAP_TABLE=LOB_TEST:LOB_TEST remap_schema=source:target dumpfile= lob_test_${i}.dmp logfile=TABLE_imp_log_test_${i}.log DATA_OPTIONS=DISABLE_APPEND_HINT CONTENT=DATA_ONLY &
done
With new versions IMPDP does issues ENABLE_PARALLEL_DML; so use aboce for basic file where PDML was disabled by default.
Reference :
DataPump Export/Import Of LOBs Are Not Executed in Parallel (Doc ID 1467662.1)
