• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar
Techgoeasy

Techgoeasy

Learn Oracle, PHP, HTML,CSS,Perl,UNIX shell scripts

  • Home
  • Oracle
    • Oracle database
    • Oracle Ebusiness Suite
    • Oracle weblogic
    • Oracle Performance Tuning
    • Oracle Hyperion
    • Oracle Cloud Tutorials
  • SQL
  • interview questions
  • Linux
  • PHP and HTML
  • Downloads
  • General
Home » Oracle » Oracle Database » how to import schema in oracle using impdp

how to import schema in oracle using impdp

February 21, 2021 by techgoeasy Leave a Comment


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


Filed Under: Oracle, Oracle Database

Reader Interactions

Leave a Reply Cancel reply

Primary Sidebar

Top Posts & Pages

  • Oracle Indexes and types of indexes in oracle with example
  • Top 30 Most Useful Concurrent Manager Queries
  • Top 46 Oracle ASM Interview Questions
  • Oracle dba interview questions and answers
  • 40 question you must know about R12.2



Subscribe to our mailing list

Enter your email address to subscribe to this blog and receive notifications of new posts by email

Recent Posts

  • How to resolve Network Error: Software Caused Connection Abort
  • How to convert private key to ppk
  • how to import schema in oracle using impdp
  • how to check PSU patch version in oracle
  • How to migrate the schema from one database to another database using exp/imp

Copyright © 2021 : TechGoEasy

  • Hire me
  • Privacy Policy
  • Contact Us
  • New? Start Here
  • About Us