Tuesday, May 31, 2005

IMPORT/EXPORT

If you want to export/import data from/to oracle databases, here's a general procedure.

I used this to setup a clean oracle database on a new machine.
I took it from Paul's notes. Thanks Paul!

Exporting a schema from the SOURCE

From the DOS prompt:

C:\> exp [username]@[database] rows=y file=*

when prompted for a filename give it a valid filename (eg. export.dmp) or accept default

where:

exp - ORACLE utility program to export data in proprietary format
username - the owner of the schema you wnat to export
database - the source ORACLE database name


Importing the schema to the DESTINATION

Assumptions: Oracle server is up and runnning in the destination machine.

From SQL-Plus: Login as SYSTEM/manager

SQL> create [username] identified by [password] temporary tablespace temp default tablespace users;
SQL> grant dba to [username];

From the DOS prompt:

C:\> imp [username]@[database] file=* full=y

when prompted for a filename, give the it the name of the [export.dmp] you supplied while exporting

where:
imp - ORACLE utility program to import data
username - the destination owner of the schema you want to import
database - the destination ORACLE database name