facebook twitter youtube facebook facebook facebook

E-Mail : info@askmlabs.com

Phone : +1.215.353.8306

Latest Post

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.
  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. 
  1. 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 
 
Support :
Copyright © 2013. askMLabs - All Rights Reserved
Proudly powered by Blogger