Saturday, October 27, 2012

Oracle - Move schema from one tablespace to another


Starting from 11G, Oracle offers a much simpler way to migrate schema for tablespaces with Data Pump utility. Previously you would have to export the schema and then drop the user re-import the schema and then rebuild all the indexes.

With Data Pump the process of switching tablespace is much simpler.

STEP 1: Export the schema using datapump
expdp system/system_password SCHEMAS=MY_SCHEMA DIRECTORY=DATA_PUMP_DIR DUMPFILE=MY_SCHEMA.dmp LOGFILE=expdp.log

Review the log to ensure the export is done properly.

STEP 2: Drop the user from database
 DROP USER MY_SCHEMA CASCADE;

STEP 3: Import the schema with REMAP_TABLESPACE
impdp system/my_schema SCHEMAS=MY_SCHEMA REMAP_TABLESPACE=SYSTEM:MY_SCHEMA_TBSPACE DIRECTORY=DATA_PUMP_DIR DUMPFILE=MY_SCHEMA.dmp LOGFILE=impdp.log


STEP 4 : Verify Tablespace Change & Validity of Objects

Check for default tablespace by running:
select username, default_tablespace from dba_users;

Also check if all objects are valid, if not compile them
SQL> select owner, status from dba_objects where upper(owner)='MY_SCHEMA';

Check the indexes:
select index_name, status from all_indexes where status ='UNUSABLE';

For more information, check out the white paper on Data Pump:

Quick Start Guide:
http://www.oracle.com/technetwork/issue-archive/2009/09-jul/datapump11g2009-quickstart-128718.pdf

Documentation:
http://docs.oracle.com/cd/B12037_01/server.101/b10825/dp_import.htm

1 comment:

  1. This guide needs to mention how to capture GRANTS before dropping the schema/user.

    ReplyDelete