Oracle datapump uses direct path load

Does Oracle datapump uses direct path load?

 

Yes. This is one of feature that makes impdp or expdp more faster than conventional export and import. To use direct path loading through oracle datapump, one has follow certain condition. Alternatively it can can be used by external table method by which we unload the data on flat file on file system of database server and after user can use those flat file as simple data source in its SELECT statement.

 

EXPDP will use DIRECT_PATH mode if:



The structure of a table allows a Direct Path unload, i.e.: 

     - The table does not have fine-grained access control enabled for SELECT. 

     - The table is not a queue table. 

     - The table does not contain one or more columns of type BFILE or opaque, or an object type containing opaque columns. 

     - The table does not contain encrypted columns. 

     - The table does not contain a column of an evolved type that needs upgrading. 

     - If the table has a column of datatype LONG or LONG RAW, then this column is the last column. 



The parameters QUERY, SAMPLE, or REMAP_DATA parameter were not used for the specified table in the Export Data Pump job. 



The table or partition is relatively small (up to 250 Mb), or the table or partition is larger, but the job cannot run in parallel because the parameter PARALLEL was not specified (or was set to 1).

 

IMPDP will use DIRECT_PATH if:



The structure of a table allows a Direct Path load, i.e.: 

     - A global index does not exist on a multipartition table during a single-partition load. This includes object tables that are partitioned. 

     - A domain index does not exist for a LOB column. 

     - The table is not in a cluster. 

     - The table does not have BFILE columns or columns of opaque types. 

     - The table does not have VARRAY columns with an embedded opaque type. 

     - The table does not have encrypted columns. 

     - Supplemental logging is not enabled and the table does not have a LOB column. 

     - The table into which data is being imported is a pre-existing table and: 

        – There is not an active trigger, and: 

        – The table is partitioned and has an index, and: 

        – Fine-grained access control for INSERT mode is not enabled, and: 

        – A constraint other than table check does not exist, and: 

        – A unique index does not exist. 



The parameters QUERY, REMAP_DATA parameter were not used for the specified table in the Import Data Pump job. 



The table or partition is relatively small (up to 250 Mb), or the table or partition is larger, but the job cannot run in parallel because the parameter PARALLEL was not specified (or was set to 1).

 

How to enforce a specific load/unload method ?

 

In very specific situations, the undocumented parameter ACCESS_METHOD can be used to enforce a specific method to unload or load the data. Example:

 

%expdp system/manager ... ACCESS_METHOD=DIRECT_PATH  

%expdp system/manager ... ACCESS_METHOD=EXTERNAL_TABLE  



or: 



%impdp system/manager ... ACCESS_METHOD=DIRECT_PATH  

%impdp system/manager ... ACCESS_METHOD=EXTERNAL_TABLE 

 

Important Need-To-Know's when the parameter ACCESS_METHOD is specified for a job:

 

 

• The parameter ACCESS_METHOD is an undocumented parameter and should only be used when requested by Oracle Support.
  

  
   

• If the parameter is not specified, then Data Pump will automatically choose the best method to load or unload the data.
  

  
   

• If import Data Pump cannot choose due to conflicting restrictions, an error will be reported:
  
  ORA-31696: unable to export/import TABLE_DATA:"SCOTT"."EMP" using client specified AUTOMATIC method
  

  
   

• The parameter can only be specified when the Data Pump job is initially started (i.e. the parameter cannot be specified when the job is restarted).
  

  
   

• If the parameter is specified, the method of loading or unloading the data is enforced on all tables that need to be loaded or unloaded with the job.
  

  
   

• Enforcing a specific method may result in a slower performance of the overall Data Pump job, or errors such as:
  

  
   

 

... 

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA 

ORA-31696: unable to export/import TABLE_DATA:"SCOTT"."MY_TAB" using client specified DIRECT_PATH method 

...

 

 

• To determine which access method is used, a Worker trace file can be created, e.g.:
  

  
   

 

%expdp system/manager DIRECTORY=my_dir \ 

DUMPFILE=expdp_s.dmp LOGFILE=expdp_s.log \ 

TABLES=scott.my_tab TRACE=400300

 

The Worker trace file shows the method with which the data was loaded (or unloaded for Import Data Pump):

 

... 

KUPW:14:57:14.289: 1: object: TABLE_DATA:"SCOTT"."MY_TAB" 

KUPW:14:57:14.289: 1: TABLE_DATA:"SCOTT"."MY_TAB" external table, parallel: 1 

...

 

EXPDP will use EXTERNAL_TABLE mode if:



Data cannot be unloaded in Direct Path mode, because of the structure of the table, i.e.: 

     - Fine-grained access control for SELECT is enabled for the table. 

     - The table is a queue table. 

     - The table contains one or more columns of type BFILE or opaque, or an object type containing opaque columns. 

     - The table contains encrypted columns. 

     - The table contains a column of an evolved type that needs upgrading. 

     - The table contains a column of type LONG or LONG RAW that is not last. 



Data could also have been unloaded in "Direct Path" mode, but the parameters QUERY, SAMPLE, or REMAP_DATA were used for the specified table in the Export Data Pump job. 



Data could also have been unloaded in "Direct Path" mode, but the table or partition is relatively large (> 250 Mb) and parallel SQL can be used to speed up the unload even more.

 

IMPDP will use EXTERNAL_TABLE if:



Data cannot be loaded in Direct Path mode, because at least one of the following conditions exists: 

     - A global index on multipartition tables exists during a single-partition load. This includes object tables that are partitioned. 

     - A domain index exists for a LOB column. 

     - A table is in a cluster. 

     - A table has BFILE columns or columns of opaque types. 

     - A table has VARRAY columns with an embedded opaque type. 

     - The table has encrypted columns. 

     - Supplemental logging is enabled and the table has at least one LOB column. 

     - The table into which data is being imported is a pre-existing table and at least one of the following conditions exists: 

        – There is an active trigger 

        – The table is partitioned and does not have any indexes 

        – Fine-grained access control for INSERT mode is enabled for the table. 

        – An enabled constraint exists (other than table check constraints) 

        – A unique index exists 



Data could also have been loaded in "Direct Path" mode, but the parameters QUERY, or REMAP_DATA were used for the specified table in the Import Data Pump job. 



Data could also have been loaded in "Direct Path" mode, but the table or partition is relatively large (> 250 Mb) and parallel SQL can be used to speed up the load even more.