Oracle Data Pump (expdp and impdp)

At this Article I will present Datapump (Export/ Import) tools.

Datapump help us to migrate Oracle data for copying to a new database after an upgrade or creating a backup.

Lets start step by step example

First of all the user have some privileges to use datapump tools.

Now create a backup location

SQL> create or replace directory dp_vey_dir as '/u01/backups/datapump';

Give privilege

SQL> grant read, write on directory dp_vey_dir to veysi;

Now use the tools

Full Export

For full export the user must have

EXP_FULL_DATABASE privileges

$ expdp user/password DIRECTORY=backup DUMPFILE=full_backup.dmp FULL=y LOGFILE=full_export.log

For example

$ expdp veysi/veysi DIRECTORY=dp_vey_dir DUMPFILE=Full_vey1.dmp FULL=y LOGFILE=full_vey1.log

Schema Export

$ expdp user /password DIRECTORY=backup DUMPFILE=schema_backup.dmp SCHEMAS=schema1,schema2

For example:

$  expdp veysi/veysi schemas=veysi DIRECTORY=dp_vey_dir DUMPFILE=Vey1.dmp LOGFILE=expdpvey1.log

Table Export

  

For example

$  expdp veysi/veysi tables=tbl_v_students DIRECTORY=dp_vey_dir DUMPFILE=Vey.dmp LOGFILE=expdpvey.log

Full Import

After Exporting we need to import. For that the user need IMP_FULL_DATABASE privileges.

$ impdp user/password DIRECTORY=backup DUMPFILE=full_backup.dmp FULL=y LOGFILE=full_import.log

For example

$ impdp veysi/veysi DIRECTORY=dp_vey_dir DUMPFILE=Full_vey1.dmp FULL=y LOGFILE=full_vey1.log

Schema Import

$  impdp veysi/veysi schemas=veysi DIRECTORY=dp_vey_dir DUMPFILE=Vey1.dmp LOGFILE=impdpvey1.log

Table Import

$  impdp veysi/veysi tables=v_tbl_student DIRECTORY=dp_vey_dir DUMPFILE=Vey.dmp LOGFILE=impdpvey.log