COPY Schema into Same Database with impdp

Written By askMLabs on Thursday, June 7, 2012 | 4:10 PM

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.

Create a directory pointing to any physical directory on the server
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.

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

Share this article :

askMLabs