Oracle

How to move a Schema to another Schema

The fastest way to move a schema to another one is using the oracle datapump tool. Previously I was using exp/imp but datapump is the most efficient way. This way you can remap tablespace of the objects.

Create a directory :

CREATE DIRECTORY ESER_EXP_DIR AS '/BACKUP_DB/ESER';

Grant required privs to the user :

GRANT IMP_FULL_DATABASE TO BCK_USER;

GRANT EXP_FULL_DATABASE TO BCK_USER;
GRANT READ, WRITE ON DIRECTORY ESER_EXP_DIR TO BCK_USER;

Export the schema you want to move

EXPDP BCK_USER/PASSWORD@DBTEST SCHEMAS=DB_TST DIRECTORY=ESER_EXP_DIR DUMPFILE=DB_TST_DP.DMP LOGFILE=DB_TST_DP.LOG

import the schema DB_TST on to the DB_PREPRD and change the objects tablespaces in the same command line.

IMPDP BCKUSER/PASSWORD@DBTEST SCHEMAS=DB_TST DIRECTORY=ESER_EXP_DIR DUMPFILE=DB_TST_DP.DMP LOGFILE=DB_TST_DP_IMP.LOG REMAP_SCHEMA=DB_TST:DB_PREPRD REMAP_TABLESPACE=DB_TST_DATA:DB_PREPRD_DATA REMAP_TABLESPACE=DB_TST_INDEX:DB_PREPRD_INDEX

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s