10g DATAPUMP

23)The Data Pump has the following advantages over the traditional expand imp tools:

Data access methods are decided automatically. For circumstances where direct path cannot be used, the external method is used.
Can perform export in parallel. It can also write to multiple files on different disks. (Specify parameters PARALLEL=2 and the two directory names with file specification DUMPFILE=DDIR1:/file1.dmp,DDIR2:/file2.dmp.)

Has the ability to attach and detach from a job gives the DBA opportunity to monitor job progress remotely and make adjustments to the job as needed.

Has the ability to restart a failed job from where it failed.

Has more options to filter metadata objects. The INCLUDE and EXCLUDE options of the expdp and impdp utilities—which are described in the following section—make it possible to extract metadata with several possible combinations.

Has the option to filter data rows during import.

The ESTIMATE_ONLY option can be used to estimate disk space requirements before actually performing the job.

Data can be exported from a remote database and imported to a remote database using a database link.

Its job status can be queried from the database directly or by using the Enterprise Manager.

Jobs can be allocated resources dynamically based on the workload.

Explicit database version can be specified, so only supported object types are exported.

Its operations can be performed from one database to another without writing to a dump file, using the network method.

During import, you can change target file names, schema, and tablespaces.


Data pump performance can be improved by using the PARALLEL parameter. This should be used in conjunction with the "%U" wildcard in the DUMPFILE parameter to allow multiple dumpfiles to be created or read:

What happens when we don't use wildcard character in conjunction with PARALLEL.

No let us look at how to use ORACLE_DATAPUMP driver to LOAD and UNLOAD data into external table.

LOAD
====
drop table employees;

CREATE TABLE employees (
ename VARCHAR2 (10),
title VARCHAR2 (10),
salary NUMBER (8))
ORGANIZATION EXTERNAL (
TYPE ORACLE_LOADER <<<<<<==========*************
DEFAULT DIRECTORY WORK_DIR
ACCESS PARAMETERS
(RECORDS DELIMITED BY NEWLINE FIELDS (
ename CHAR(10),
title CHAR(10),
salary CHAR(8)))
LOCATION ('employee.dat'));


UNLOAD
======
Oracle have incorporated support for data pump technology into external tables. The ORACLE_DATAPUMP access driver can be used to unload data to data pump export files and subsequently reload it. The unload of data occurs when the external table is created using the "AS" clause:

CREATE TABLE emp_xt
ORGANIZATION EXTERNAL
(
TYPE ORACLE_DATAPUMP <<<<<<==========*************
DEFAULT DIRECTORY test_dir
LOCATION ('emp_xt.dmp')
)
AS SELECT * FROM emp;