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 schema in the Oracle database using impdp in this 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.

Let’s HR is the schema. then below command can be used

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

or if you are taking a full backup

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

This will generate dump files in the directory specified. The number of dump files is the number of parallelisms given. The %U is the dump file makes each of these files uniquely identified

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

3. Now if the schema does not exist in the target database. Here in the dumpfile parameter, we need to give all the dump file names separated by comma

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

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

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

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

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

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

CREATE OR REPLACE DIRECTORY "IMP_DIR" as '/u01/dumpfileloc';
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 the dump

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

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

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

6. If the Source Tablespace where user objects are created is not there in the target database, it will give an error on user creation, then we can map that tablespace to another oracle tablespace using the 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/dumpfileloc';
impdp   system/<pass> DIRECTORY=IMP_DIR  dumpfile=<dump files>  REMAP_TABLESPACE=USERS1:USERS2 parallel=3

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

CREATE OR REPLACE DIRECTORY "IMP_DIR" as '/u01/dumpfileloc';
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 to in the impdp command

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

Instead of giving this command. You can also specify all these parameters in a par file also. For example

vi  imp_HR.par
userid=system/<pass>
DIRECTORY=IMP_DIR  
dumpfile=<dump files>  
REMAP_TABLESPACE=USERS1:USERS2 
parallel=3

Then give the impdp command as

impdp parfile=imp_HR.par

I hope this post on import schema in oracle will help you

Related Articles

How to migrate the schema from one database to another database using exp/imp
Oracle 12c pluggable database commands
https://docs.oracle.com/en/database/oracle/oracle-database/19/sutil/datapump-import-utility.html