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.
Verify the database link
We are eliminating the creation of dump file, by using database link with the parameter network_link
Hope it helps
SRI
- 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.
- 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/’;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 xxxxxxx using ‘DB-A’;
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.logVerify 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;Observations:
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';
We are eliminating the creation of dump file, by using database link with the parameter network_link
Hope it helps
SRI