Oracle Technologies Blog

By ASKM

COPY Schema into Same Database with impdp

Posted by Srikrishna Murthy Annam on June 7, 2012

In this article I am going to explain the procedure to copy a schema to a different schema in the same database without exporting the source schema. This procedure works fine if you want to copy the schema to a different database.

1. Create a directory pointing to any physical directory on the server
2. Create a loopback database link. It means, you have to create a database link pointing to the same database.
   NOTE : If your requirement is to copy schema to a different schema, create a database link pointing to target database.
3. Use the impdp to copy schema to another schema.

Assume that the database name is DB-A. I am trying to copy a schema SCOTT to a different schema SCOTT_NEW in the same database.

SQL> create a directory TEST_DIR as ‘/xxxx/exp-dir/’;

SQL> create a database link SCOTT_DB_LINK connect to SYSTEM identified by  xxxxxxx using ‘DB-A’;

If you are trying to copy schema to a different database , say DB-B , create database link as follows.

SQL> create a database link SCOTT_DB_LINK connect to system identified by xxxxx using ‘DB-B’;

(NOTE : We should be able to resolve the alias DB-A to database A and DB-B to database B.)

Verify the database link

SQL> select * from dual@SCOTT_DB_LINK;

Use the following syntax to copy schema,

impdp system/xxxxxx schemas=SCOTT directory=TEST_DIR network_link=SCOTT_DB_LINK  remap_schema=SCOTT:SCOTT_NEW logfile=TEST_DIR:SCOTT_NEW.log

Verify the new schema with the source schema with following commands.

SQL> select object_type,count(1) from dba_objects where owner=’SCOTT’ and status=’INVALID’ group by object_type;
SQL> select object_type,count(1) from dba_objects where owner=’SCOTT_NEW’ and status=’INVALID’ group by object_type;
SQL> select object_name,object_type from dba_objects where owner=’SCOTT’ and status=’INVALID’;
SQL> select object_name,object_type from dba_objects where owner=’SCOTT_NEW’ and status=’INVALID’;

Observations:

We are eliminating the creation of dump file, by using database link with the parameter network_link

Hope it helps

SRI

Advertisements

2 Responses to “COPY Schema into Same Database with impdp”

  1. lao said

    good

  2. Jay said

    The database link needs to be defined as public

    SQL> create public database link ….

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s

 
%d bloggers like this: