Posted in Import&Export

How to clone a schema using different name at destination

Recently I had to make a clone of a schema and to move it from production to development using a different name at destination.

First grab all the information you need:

select username,default_tablespace from dba_users where username like '&USERNAME';

# Now get the tablespace name from the previous statement and get the datafiles below:

select file_name from dba_data_files where tablespace_name='&TBS_NAME';

# at destination create the tablespaces (ENCRYPTED if needed) (different name)

create tablespace NEWUSER_DATA datafile '/oradata/DB_NAME/newuser_data01.dbf'  size 900M autoextend on next 100M maxsize unlimited;
create tablespace NEWUSER_INDEX datafile '/oradata/DB_NAME/newuser_index01.dbf'  size 100M autoextend on next 100M maxsize unlimited;

# create the user at destination

create user NEWUSER identified by 'password' default tablespace NEWUSER_DATA;
 grant unlimited tablespace to NEWUSER;
 grant connect,resource to NEWUSER;
# Export the schema:
 set lin 300
 col owner format a30
 col DIRECTORY_NAME format a50
 col DIRECTORY_PATH format a50
 select owner,DIRECTORY_NAME,DIRECTORY_PATH from dba_directories;
 expdp  \'/ as sysdba\' directory=DATA_PUMP_DIR dumpfile=NEWUSER.20151028.dmpdp logfile=NEWUSER.20151028.logdp schemas=PROD_USER

# Import at destination

impdp \'/ as sysdba\'  remap_schema=PROD_USER:NEWUSER remap_tablespace=PROD_USER_DATA:NEWUSER_DATA,PROD_USER_INDEX:NEWUSER_INDEX directory=DATA_PUMP_DIR dumpfile=NEWUSER.20151028.dmpdp logfile=NEWUSER.20151028.dmpdp_imp.logdp
# Compile it:
 exec dbms_utility.compile_schema('NEWUSER');


I am an Oracle DBA with experience in Maximum Availability technology such as RAC, Data Guard, Grid Control and ASM. I worked with version 9i through 12c on a number of operating systems. Next to that I have an extensive experience with PL/SQL, Oracle eBusiness Suite and shell scripting. Specialties: Oracle RAC & ASM (Configuration/Monitoring/Tuning) Oracle Data Guard (Configuration/Monitoring/Tuning) EM12c (Installation / troubleshooting) SQL & PLSQL *nix Shell scripting

Leave a Reply

Please log in using one of these methods to post your comment: Logo

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

Google+ photo

You are commenting using your Google+ 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 )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.