how to import schema in oracle using impdp

we often have a situation where we need to move the schema from Prod to DEV or from test to another. We will discuss how to import schemas in Oracle database using impdp in these situation

How to import schema in Oracle

1. First of all we should take the dump of the schema or full database of the source database depending on the requirement.

Lets HR is the schema. then below command can be used

CREATE OR REPLACE DIRECTORY "EXP_DIR" as '/u01/dumpfileoloc';
expdp   system/<pass> DIRECTORY=EXP_DIR  dumpfile=hr%U.dmp   schemas=HR parallel=3

or if you are taking full backup

CREATE OR REPLACE DIRECTORY "EXP_DIR" as '/u01/dumpfileoloc';
expdp system/<pass> DIRECTORY=EXP_DIR dumpfile=fullbackup%U.dmp  parallel=3

2. Now we need to move these dump file to the target database server .This can be done using scp or sftp

3. Now if the schema does not exists in the target database

CREATE OR REPLACE DIRECTORY "IMP_DIR" as '/u01/dumpfileoloc';
impdp   system/<pass> DIRECTORY=IMP_DIR  dumpfile=<dump files>  parallel=3

if the import is full backup, then it is required to specify the schema otherwise it will try to import whole database

CREATE OR REPLACE DIRECTORY "IMP_DIR" as '/u01/dumpfileoloc';
impdp   system/<pass> DIRECTORY=IMP_DIR  dumpfile=<dump files>  schemas=HR parallel=3

4. If the schema exists and you want to import with different name like HR2, then

CREATE OR REPLACE DIRECTORY "IMP_DIR" as '/u01/dumpfileoloc';
impdp   system/<pass> DIRECTORY=IMP_DIR  dumpfile=<dump files>  REMAP_SCHEMA=HR:HR2 parallel=3

if the import is full backup, then it is required to specify the schema otherwise it will try to import whole database

CREATE OR REPLACE DIRECTORY "IMP_DIR" as '/u01/dumpfileoloc';
impdp   system/<pass> DIRECTORY=IMP_DIR  dumpfile=<dump files>  schemas=HR REMAP_SCHEMA=HR:HR2 parallel=3

5. If the schema exists and you want to import by replacing the table i.e dropping the existing table and importing from dump

CREATE OR REPLACE DIRECTORY "IMP_DIR" as '/u01/dumpfileoloc';
impdp   system/<pass> DIRECTORY=IMP_DIR  dumpfile=<dump files>  TABLE_EXISTS_ACTION=REPLACE parallel=3

if the import is full backup, then it is required to specify the schema otherwise it will try to import whole database

CREATE OR REPLACE DIRECTORY "IMP_DIR" as '/u01/dumpfileoloc';
impdp   system/<pass> DIRECTORY=IMP_DIR  dumpfile=<dump files>  schemas=HR TABLE_EXISTS_ACTION=REPLACE parallel=3

6. If the Source Tablespace where user object are created is not there in target database ,it will give error on user creation , then we can map that tablespace to another tablespace using REMAP_TABLESPACE parameter

Run the below query on the source side

SQL> select default_tablespace from dba_users where username=HR;
DEFAULT_TABLESPACE
-------
USERS1

Now if you have USER2 tablespace in the target database

CREATE OR REPLACE DIRECTORY "IMP_DIR" as '/u01/dumpfileoloc';
impdp   system/<pass> DIRECTORY=IMP_DIR  dumpfile=<dump files>  REMAP_TABLESPACE=USERS1:USERS2 parallel=3

if the import is full backup, then it is required to specify the schema otherwise it will try to import whole database

CREATE OR REPLACE DIRECTORY "IMP_DIR" as '/u01/dumpfileoloc';
impdp   system/<pass> DIRECTORY=IMP_DIR  dumpfile=<dump files>  schemas=HR REMAP_TABLESPACE=USERS:USERS2 parallel=3

7. This same applies if we have multiple schemas also.

You need to specify the schemas name according in the impdp command

impdp   system/<pass> DIRECTORY=IMP_DIR  dumpfile=<dump files>  schemas=HR,SCOTT,TEST parallel=3

I hope these commands helps you