Guenadi N Jilevski's Oracle BLOG

Data access methods using data pump utility

Data access methods using data pump utility

A Data Pump export and import job can access the table data in either of the two ways:
Direct-Path :
This access uses the Direct Path API
Direct-path exports and imports lead to improved performance, since the direct path internal stream format is the same as the format stored in Oracle dump files. This leads to reduced need for data conversion

Oracle always uses direct-path method to load or unload data except under some conditions:
1) Clustered tables
2) Active triggers in tables
3) A single partition in a table with a global index
4) Referrential integrity constraints
5) Domain indexes on LOB columns
6) Tables with fine-grained access control enabled in the insert mode
7) Tables with BFILE or opaque type columns

It is mandatory to use direct-path access methods under the following conditions :

Table has any LONG data

External-tables :
This feature lets oracle read data from and write data to operating system files that lie outside the database.
Direct-path doesn’t support intra-partition parallelism and it is imperative to use external tables in such cases.
Under following conditions only external tables can be used :
1) Clustered tables
2) Active triggers in tables
3) A single partition in a table with a global index
4) Referrential integrity constraints
5) Domain indexes on LOB columns
6) Tables with fine-grained access control enabled in the insert mode
7) Tables with BFILE or opaque type columns

Note :
The data file format is identical in direct-path access method and external tables. So we can easily export data with one method and import data with other method.

February 19, 2008 Posted by gjilevski | oracle | Leave a comment