Cloning an Oracle Schema

When I started this blog I decided I would only blog about things you could not find reasonably easily with a simple google search. This post violates that rule. Cloning a schema is something that I do fairly frequently but not frequently enough that I remember the exact syntax. First I googled it every time, later I created a little text file with the commands. Having already gone to the trouble to write a text file, I might as well just paste it in here. I use datapump. In the example below I want to export the SCOTT schema which has all of its data in the USERS tablespace. I want to import it into a database (either the same or another Oracle database) as the user SCOTT2 in the tablespace USERS2. That means I need to change both the database schema and tablespace. Here are the commands (replace values as appropriate for your env):

export ORACLE_HOME=/opt/oracle/product/oracle10g
export ORACLE_SID=c2dev1
cd $ORACLE_HOME/bin
./expdp system/ dumpfile=scott.dmp schemas=scott

Note: this will create the file scott.dmp in the location $ORACLE_HOME/admin/dpdump. You can create a different directory for it, but for my purposes this is sufficient.

That is all it takes to do the export.
If you plan to import into the same database it can stay in the same location.

To do the import into a different database you will need to copy the scott.dmp file to the right location for the other database.

cp $ORACLE_HOME/admin/c2dev1/dpdump/scott.dmp /mynas/scott.dmp

Then I ssh to the target machine and copy to the new target location

cp /mynas/scott.dmp $ORACLE_HOME/admin/c2dev2/dpdump/scott.dmp

export ORACLE_HOME=/opt/oracle/product/oracle10g
export ORACLE_SID=c2dev2
cd $ORACLE_HOME/bin
./impdp system/ dumpfile=scott.dmp remap_schema=scott:scott2 remap_tablespace=USERS:USERS2

That should do it. This should make it easy for me to do in the future and hopefully help someone else along the way.