OracleBlog

I recently installed a new Oracle instance on my laptop. I wanted to migrate my small development database (complete with structure and data) from my main workstation to my laptop.

It was very easy using the import (IMP) and export (EXP) tools. They are described in Chapter 1 (Export) and Chapter 2 (Import) of the Oracle Utilities Guide, available here:

http://www.oracle.com/technology/documentation/index.html

Everything you should need to know is in there. For good measure, I read Chapter 8 of Tom Kyte's "Expert One-on-One Oracle" on Import and Export.

Word of caution: Think twice before using IMP/EXP as your back-up strategy on large, complex databases.

Both utilities are located in $ORACLE_HOME/bin directory. You need to run the $ORACLE_HOME/rdbms/admin/catexp.sql file. You can see the commands you need by using the HELP=Y option at your command prompt

C:\> EXP HELP=Y

So for me, the process was as easy as this:

1. Export the data

C:\> EXP USERID=scott/tiger OWNER=scott FILE=scott.dmp

2. Copy the DMP file to the target machine

3. Import the data

C:\> IMP USERID=scott/tiger FILE=scott.dmp FULL=Y

Done! All the tables, triggers, procedures, view and constraints, as well as all the data. While I was at it, I just created a BAT file out of this and added it to the Task Manager to back-up my data regularly. (Unix: SH file and CRON).

As a final note, here is a good FQ, by Frank Naude:
http://www.orafaq.com/faqiexp.htm