10g Datapump – An Introduction

In 10g you can continue to use the earlier exp and imp versions to export/import data.

However if you export data using exp, then the import has to be with imp utility.

You cannot export using the old exp utility and import using impdp

The 2 new utilites in 10g are EXPDP and IMPDP.

EXPORT USING DATAPUMP (EXPDP).

Important pre-requisites:

Two directories need to be created in the database with the path pointing to an existing physical path on the Database Server: Ex HQLINUX5.

By default dumps and logs created in $ORACLE_HOME/rdbms/log area.

To avoid using the disk on which the ORACLE_HOME resides…

1. CREATE DIRECTORIES on server: example /u01/ORACLE/bozo/datapump and /u02/ORACLE/bozo/pumplogs

2. CREATE DIRECTORIES IN DATABASE.

Sql> create directory dump_dir as ‘/u02/ORACLE/bozo/datapump’; ………All dumps are sent to this area.

Sql> create directory log_dir as ‘/u02/ORACLE/bozo/pumplogs’; ………All logs are sent to this area.

Above directories must exist on unix machine for above command to work

SQL> grant read,write on directory dump_dir to arjun; —user exporting needs write priv and user importing needs read priv.

Grant succeeded.

SQL> grant read,write on directory log_dir to scott;

Grant succeeded.

ESTIMATE SIZE OF EXPORT

expdp arjun/arjun logfile=log_dir:full1.log estimate_only=y ……no parameter dump_dir allowed when using estimate_only=Y

Does not export…only estimates size of export dump.

SCHEMA EXPORT

expdp arjun/arjun schemas=arjun dumpfile=dump_dir:schema1.dmp logfile=log_dir:full1.log parallel=2 filesize=2G

Dump_dir and log_dir are the directories created above.

FULL DATABASE EXPORT

expdp system/temp full=y dumpfile=dump_dir:full.dmp logfile=log_dir:full3.log parallel=2 filesize=2G

IMPORTING USING DATAPUMP

To import into another schema, example – from arjun to scott.

impdp arjun/arjun remap_schema=arjun:scott dumpfile=dump_dir:schema.dmp logfile=log_dir:full4.log

Import into same schema name…i.e…export taken from arjun and import into arjun.

impdp system/temp schemas=arjun dumpfile=dump_dir:schema.dmp logfile=log_dir:scott1.log
The dumpfile should point to the dump_dir:*.dmp file.

FULL DATABASE IMPORT:

impdp system/temp dumpfile=dump_dir:full.dmp logfile=log_dir:full10.log