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