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');

Leave a Reply

%d bloggers like this: