Last Updated: February 25, 2016
· goliardico

Moving schema from an Oracle database to another

Sometimes could happend that you need to move a schema between two databases and you don't have physical access to them, like in this example:

You would move "user1" schema from "db1" to "db2" but you are logged only in "host1" (with oracle utilities, at least).

I'm talking of two Oracle 11gR2 with ASM, so the old exp/imp utilities are unusable (do you know DEFERRED_SEGMENT_CREATION?).

Ok, you need:

  • create database link
  • create directories
  • create user

Make them talking

First of all, we need to create link between "db1" and "db2" and create a storage area for dumpfile and log.
Log to db2 with a user capable to create db links and directories:

sqlplus admin/admin@db2
create user user1 identified by user1;
grant connect, resource, create view to user1;
grant create database link to user1;

create directory dp as '+ASMVOL'; -- choose the right one
grant read, write on directory dp to user1;

create directory dp_log as '/tmp'; -- works ever (in Linux/Unix)
grant read, write on directory dp_log to user1;

"dp" directory is for dumpfile, "dp_log" is for ... well, log file.

Now we'll connect with "user1":

sqlplus user1/user1@db2
create database link db1_link connect to user1 identified by user1 using 'db1host:1521/db1';


We'll use Data Pump utility expdp and impdp over "db2" (Oracle automatically connect to "db1" using database link.)

Run expdp with network_link parameter:

expdp user1/user1@db2 directory=dp dumpfile=db1_today.dmp logfile=dp_log:db1_today.explog network_link=db1_link

Ok, db1_today.dmp is inside "dp" directory: +ASMVOL\db1_today.dmp


Finaly, import dumpfile inside "user1":

impdp user1/user1@db2 directory=dp dumpfile=db1_today.dmp logfile=dp_log:db1_today.implog

The End.