Oracle ~ exp

Source DB: TSTDUMP1 (Contents the data which needs to be exported).
Destination DB: TSTDUMP2 (Fresh or Populated DB)

STEP 01-> Create a folder in the source DB server.
* Windows : “C:\ORACLE\DUMP\”
* Linux: “/home/oracle/dump”


STEP 02-> Get the dump
exp userid=system/******@TSTDUMP1 file=testdump1.dmp log=testdump1.log full=y statistics=none

**According to the requirement, the whole, partial DB can be exported.
full=y ~ This enables to export the complete Source DB.
owner=testuser ~ This enables to export only the schema “testuser”
tables=testuser.students,testuser.names ~ This enables to export tables “students” & “names” of “testuser” schema


STEP 03-> check the tablespaces in both to check whether they are similar
select tablespace_name from dba_tablespaces;
if not similar, need to make the necessary table spaces in Destination DB

* Windows : create tablespace importtest_ts datafile ‘C:\ORACLE\TSTDUMP1\importtest_ts01.dbf’ size 100m autoextend on;
* Linux : create tablespace importtest_ts datafile ‘/home/oracle/TSTDUMP1/importtest_ts01.dbf’ size 100m autoextend on;


STEP 04-> check the users at Destination DB and drop them

sqlplus sys/******@TSTDUMP2 as sysdba
select username from dba_users;


STEP 05-> check the users at Source DB and create in Destination DB

create user importtest identified by password default tablespace importtest_ts quota unlimited on importtest_ts temporary tablespace temp;


STEP 06-> Then grant the users with the required priviledges
grant create session, resource, create view, create synonym, create public synonym to importtest;


STEP 07-> Then simply import the dump into the Destination DB

* Windows :  C:\ORACLE\DUMP>imp userid=system/******@TSTDUMP2 file=testdump1 log=imptestdump1.log fromuser=(importtest_ts) touser=(importtest_ts) ignore=y commit=y resumable=y statistics=none
* Linux:  C:\ORACLE\DUMP>imp userid=system/******@TSTDUMP2 file=testdump1 log=imptestdump1.log fromuser=importtest_ts touser=importtest_ts ignore=y commit=y resumable=y statistics=none


STEP 08 -> Applying any special grants to the imported users in the Destination DB


STEP 09-> Compile the Destination DB and compare for invalid objects.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s