Monday, August 01, 2005

Import Export

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

Comments:

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


I'd be even more cautious to use exp/imp and backup in the same sentence (with the exception of: "Don't
use exp as your backup" of course) ;-) . Having said that, exp can play a vital part in a backup strategy, but
I wouldn't go as far and call the output a backup.

However, for your initial problem of migrating data between databases, it quite likely was the best solution.
But for the fun of it, you might want to investigate rman and clone database (a technique that employes a
'real' backup).

Cheers,
Holger
 
Laurent Schneider wrote something on this:

http://laurentschneider.blogspot.com/2005/09/migrate-database-with-imp-exp.html
 
I use oracle IMP EXP to move data between Production and Development.
 
This comment has been removed by a blog administrator.
 
Post a Comment

<< Home

This page is powered by Blogger. Isn't yours?