Data Pomp Import & Export in Oracle database

Today I would like to show how to export and import a data from oracle instance. To accomplish this task I will use the expdp and impdp. So let's start.

The first task:
1. Create a directory where that dmp file should be stored. (User who is going to export has to have granted privileges to writing and reading to this directory).

mkdir /home/exports

2. Create a mapped directory on database side. (User who is going to do the export and import has to have privileges to "CREATE ANY DIRECTORY").

conn sys/pass@ora10g as SYSDBA
alter user scott identified by tiger account unlock
grant CREATE ANY DIRECTORY to scott

create or replace directory dmpDir as '/home/exports/';
grant read, write on directory

Export and Imports commands
1. Tables:

expdp scott/tiger@ora10g tables=Table1,Table2 directory=dmpDir dumpfile=scottDmp.dmp logfile=scottDmpExp.log

impdp scott/tiger@ora10g tables=Table1,Table2 directory=dmpDir dumpfile=scottDmp.dmp logfile=scottDmpImp.log TABLE_EXISTS_ACTION=APPEND

TABLE_EXISTS_ACTION - determines how the import should be executed when the importing object already exists. Arguments: SKIP, APPEND (default), TRUNCATE and REPLACE.

2. Schemas:

expdp scott/tiger@ora10g schemas=SCOTT directory=dmpDir dumpfile=scottDmp.dmp logfile=scottDmpExp.log

impdp scott/tiger@ora10g schemas=SCOTT directory=dmpDir dumpfile=scottDmp.dmp logfile=scottDmpImp.log TABLE_EXISTS_ACTION=APPEND

3. Database:

expdp scott/tiger@ora10g full=Y directory=dmpDir dumpfile=scottDmp.dmp logfile=scottDmpExp.log

impdp scott/tiger@ora10g full=Y directory=dmpDir dumpfile=scottDmp.dmp logfile=scottDmpImp.log TABLE_EXISTS_ACTION=APPEND

If you would like to get more info about parameters, please type impdp help=y or expdp help=y.

good luck