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.

APEXposed 2009 - A request for input

I will be speaking at APEXposed 2009 in Atlanta, GA on 10 & 11 November. Some of you have certainly seen the How to Hack an APEX Application presentation. I will be giving a revised version of that. It is difficult to find interesting things because the APEX developers keep adding features to make it harder for developers to get into trouble, but I'll have a few items of interest, plus the old standby's.

My second talk is APEX and the Oracle Database.
The power of APEX is partly the immense scope of capabilities present in the Oracle database. In this presentation I am going to show how to use many of these capabilities within APEX. Below are a few topics I have in mind.

• Oracle Text (Intermedia)
• Spacial
• SQL Analytics
• File Compression
• owa routines
• utl_inaddr
• External Tables
• Virtual Private Database
  

I'd love to get additional ideas--if you have any favorites, please let me know!