Wednesday, December 07, 2011

Cloning an Oracle Schema

An Oracle schema can be "cloned" by using the expdp and impdp utilities.

1) Create a directory on the file system and let Oracle know about it.
a) [bash assumed]: mkdir /home/oracle/data_dmp
b) [in sqlplus]: create directory data_dmp as '/home/oracle/data_dmp';
grant read,write on directory DATA_DMP to myschema;

2) Export schema to file:
expdp myschema/**** dumpfile=myschema.dmp logfile=myschema.exp schemas=myschema directory=data_dmp

3) Create a user for your new schema. I am cloning this schema simply to run a test of some scripts that will modify the schema. I like to call my temporary schemas "delme" so I remember to delete them.

[I created user in SQL Developer and granted all because I'm lazy and want it done fast.]

Crossed out because Step 4 below will actually create the user for you!

4) Import!
impdp myschema/**** directory=data_dmp dumpfile=myschema.dmp logfile=myschema.imp remap_schema='MYSCHEMA':'DELME'