Oracle Data Pump

Creating the Directory

Include EXP_FULL_DATABASE rights to allow for export of all oracle data. Include IMP_FULL_DATABASE rights to allow for import of all oracle data. This is not required for user owned data.

SQL> CREATE DIRECTORY back_user AS ‘/home/oracle/user/backup’;

SQL> GRANT READ, WRITE ON DIRECTORY backup to user;

SQL> GRANT EXP_FULL_DATABASE TO user;

SQL> GRANT IMP_FULL_DATABASE TO user;

Creating the backup

This only backs up a specific user.

LINUX> expdp user/password DIRECTORY=back_user DUMPFILE=back1.dmp

Include full=yes to get everything.

LINUX> expdp user/password DIRECTORY=back_user DUMPFILE=back1.dmp FULL=yes

Restoring the backup

Change the table_exists_action to replace, skip or append acordingly.

LINUX> impdp user/password table=tableList DIRECTORY=back_user DUMPFILE=back1.dmp table_exists_action=replace

Omit the table parameter for all tables.

LINUX> impdp user/password DIRECTORY=back_user DUMPFILE=back1.dmp table_exists_action=replace