Datapump in Oracle 11g

Datapump import/export are used for data migration. You can export TABLES, SCHEMA, TABLESPACE or DATABASE and restore it on another server.

Quickfacts:

1. Datapump produces a dumpfile with extention .dmp or .imp
2. Privilege 'exp_full_database' is required to export full database
3. Privilege 'imp_full_database' is required to impport full database
4. In datapump, you can user a parfile to store your commands

Getting the help page

expdp help=y

Keyword                                  Description
------------------------------------------------------------------------------
DIRECTORY                          Directory object to be used for dumpfiles and logfiles.
DUMPFILE                             List of destination dump files (expdat.dmp),
                                                 e.g. DUMPFILE=scott1.dmp, scott2.dmp, dmpdir:scott3.dmp.
EXCLUDE                              Exclude specific object types, e.g. EXCLUDE=TABLE:EMP.
FULL                                       Export entire database (N).
HELP                                       Display Help messages (N).
INCLUDE                                Include specific object types, e.g. INCLUDE=TABLE_DATA.
JOB_NAME                            Name of export job to create.
LOGFILE                                 Log file name (export.log).
NETWORK_LINK                  Name of remote database link to the source system.
NOLOGFILE                           Do not write logfile (N).
PARALLEL                             Change the number of active workers for current job.
PARFILE                                 Specify parameter file.
SCHEMAS                              List of schemas to export (login schema).
TABLES                                  Identifies a list of tables to export - one schema only.
TABLESPACES                     Identifies a list of tablespaces to export.
VERSION                              Version of objects to export where valid keywords are:
                                                (COMPATIBLE), LATEST, or any valid database version.
EXPORT

Example 1: Joe is a user on a database, Joe needs to export the whole database.
Solution:
c:\> sqlplus sys/**** as sysdba
     grant exp_full_database to joe;
     commit;
     exit;
c:\> expdp joe/**** full=y dumpfile=joe_database.dmp logfile=joe.log compression=n

^full database is exported
^Dumpfile will be created in the default location, which is 'c:\app\user\admin\orcl\dpdump'

Example 2: System needs to export Joe's schema with compression
Solution:
c:\> expdp system/**** full=n schemas=joe dumpfile=joe_schema.dmp logfile=joe1.log compression=y

^Joe's schema is exported
^Dumpfile will be created in the default location, which is 'c:\app\user\admin\orcl\dpdump'
^Dumpfile will be compressed

Example 3: Joe needs to export 2 tables (Student, Lecturer) to a location on disc ('c:\oracle')
Solution:
c:\> sqlplus joe/****
     create directory oracle as 'c:\oracle';
     commit;
     exit;
c:\> expdp joe/**** full=n tables=Student,Lecturer directory=oracle dumpfile=joe_tables.dmp logfile=joe2.log

^Joe's table are exported
^Dumpfile will be created in 'c:\oracle'
^There should be no space between the tables...e.g tables=(student1,student2,student3)

IMPORT

Example 4: Joe needs to import 2 tables (Student, Lecturer) from a directory called 'oracle'
Solution:
c:\> impdp joe/**** directory=oracle dumpfile=joe_tables.dmp tables=Student,Lecturer logfile=joe3.log

^Joe's table will be imported

Example 5: Joe needs to import 2 tables (Student, Lecturer) and change the tables name to 'student1' and 'student2'
Solution:
c:\> impdp joe/**** directory=oracle dumpfile=joe_tables.dmp logfile=joe3.log remap_table=joe.Student:student1,joe.Lecturer:student2

^Joe's table will be imported and renamed
^Tables must be separated by comma, the colon (:) must separate the oldname and new name

Example 6: System needs to import the whole database from a dumpfile on a flashdrive
Solution:
c:\> cd c:\app\user\admin\orcl\dpdump
Copy dumpfile to the folder
Launch impdp from this location
c:\app\user\admin\orcl\dpdump> impdp system/**** full=y dumpfile=database.dmp logfile=db.log

Example 7: System needs to import joe's schema from dumpfile (of full database) on a flashdrive
Solution:
c:\> cd c:\app\user\admin\orcl\dpdump
Copy dumpfile to the folder
Launch impdp from this location
c:\app\user\admin\orcl\dpdump> impdp system/**** full=n schemas=joe dumpfile=database.dmp logfile=db1.log

^Joe's schema will be imported


Example 8: System needs to import joe's schema and change name to DANIEL and also change the tablespace to daniel's tablespace
from dumpfile (of full database) on a flashdrive
Solution:
c:\> cd c:\app\user\admin\orcl\dpdump
Copy dumpfile to the folder
Launch impdp from this location
c:\app\user\admin\orcl\dpdump> impdp system/**** full=n dumpfile=database.dmp logfile=db2.log remap_schema=joe:daniel remap_tablespace=users:daniel_tbs

^Joe's schema will be imported and changed to Daniel
^Tablespace will be changed from users to Daniel_tbs
^you MUST physically create this tablespace and grant access to Daniel.

Example 9: System needs to import joe's schema using a parameter file
Solution:

create a parfile called 'joe.txt'

USERNAME=system/****
FULL=N
SCHEMA=(joe,daniel,class)
DIRECTORY=oracle
DUMPFILE=database.dmp
LOGFILE=db4.log

Save file and exit

c:\> impdp parfile=joe.txt

Parfile makes it more convenient to adjust datapump commands. It also help when you need to constantly run same commands.

Example 10: System needs to import joe's schema from dumpfile and replace all tables
Solution:
c:\> impdp system/**** full=n schemas=joe dumpfile=database.dmp logfile=db6.log table_exists_action=replace

^Joe's schema will be imported
^Joe's tables will be replaced if they already exist on the database prior to import