Friday 19 February 2016

Oracle - copying tables from one schema to another

There may well be other methods but this works for me.

This example copies some tables ('MY_TABLE1','MY_TABLE2',',MY_TABLE3') from a schema  named 'SRC_SCHEMA' to a schema 'DEST_SCHEMA'

1) Create a parameter file (called PARS.txt) describing the tables you want to copy:
   a) using a LIKE
schemas=SRC_SCHEMA dumpfile=some_tables.dmp INCLUDE=TABLE:"LIKE 'MY_TA_%'"

   b) here is an example listing specific tables
schemas=SRC_SCHEMA dumpfile=some_tables.dmp INCLUDE=TABLE:"IN ('MY_TABLE_1','MY_TABLE_2','MY_TABLE_3')"

2) export these tables using expdp
expdp system/password PARFILE=PARS.txt
3) import these tables from the created dump file using impdp.

NB1: there are other options to the parameter TABLE_EXISTS_ACTION. This one will simply overwrite what is there.

NB2: the example shows the remapping of two TABLESPACES as well
impdp system/password TABLE_EXISTS_ACTION=REPLACE dumpfile=some_tables.dmp REMAP_SCHEMA=SRC_SCHEMA:DEST_SCHEMA  REMAP_TABLESPACE=SRC_TSPACE1:DEST_TSPACE1   REMAP_TABLESPACE=SRC_TSPACE2:DEST_TSPACE2