Oracle, RMAN, GoldenGate, Active Duplication, OEM, DataPump...

Friday, June 4, 2010

Data Pump. Moving tables to new schema, new tablespace.

I want to move a series of tables from one schema to another. At the same time I want to move them to a new tablespace. Data Pump is great for doing this.

The following data pump parameters allow for this. You can remap more then one tablespace at a time.

First export the tables.

Export parfile example (expdp_joe_tables.par):
USERID='/ as sysdba'
TABLES=(JOE.TABLE1, JOE.TABLE2, JOE.TABLE3)
DUMPFILE=joe_tables
LOGFILE=expdp_joe_tables

-- Run the expdp command
#expdp parfile=expdp_joe_tables.par

Import parfile example (impdp_joe_tables.par):
USERID='/ as sysdba'
TABLES=(JOE.TABLE1, JOE.TABLE2, JOE.TABLE3)
REMAP_SCHEMA=JOE:SAM
REMAP_TABLESPACE=USERJOE1:USERSAM1,USERJOE2:USERSAM2
DUMPFILE=joe_tables
LOGFILE=impdp_joe_tables

Import the tables to a new schema and tablespace.

-- Run the impdp command
#impdp parfile=impdp_joe_tables.par

No comments:

Post a Comment