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
Leave a Reply