10g Datapump – quick 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 exp and import using impdp

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

EXPORT USING DATAPUMP (EXPDP).

Important pre-requisites:

1. 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 DIRECTORY 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 scott;   —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