Thread: Import export

How to export and import database for a particular user or schema ?

The Oracle export (EXP) and import (IMP) utilities are used to perform logical database backup and recovery. They are also used to move Oracle data from one machine, database or schema to another.

First goto command prompt.

Check whether bin path is set correctly or not. Otherwise set the bin path.
Ex:- path = d:\ora_home\bin

Then to export data use the following syntax.

exp scott/tiger file=emp.dmp log=emp.log tables=emp indexes=no

Here scott/tiger are username and password from which data is to be exported.

File is the dump file name. If you want to export entire database tables option is not required. If you want to export a particular table then give tables= tablename.


To import the data use the following syntax

imp scott/tiger file=emp.dmp fromuser=scott touser=scott tables=dept full =n

To import entire dump set full = y. To import a particular table, specify the table name then set full =n.

To get help regarding IMP and EXP go to BIN path of orahome then type
EXP HELP = YES

To import the full database
-----------------------------
imp SYSTEM/password FULL=y FIlE=dba.dmp

To import just the dept and emp tables from the scott schema
-----------------------------
imp SYSTEM/password FIlE=dba.dmp FROMUSER=scott TABLES=(dept,emp)

To import tables and change the owner
-----------------------------
imp SYSTEM/password FROMUSER=blake TOUSER=scott FILE=blake.dmp TABLES=(unit,manager)

To import just the scott schema
-----------------------------
imp / FIlE=scott.dmp



To export the entire database to a single file dba.dmp in the current directory.
-----------------------------
- Login to server

exp SYSTEM/password FULL=y FILE=dba.dmp LOG=dba.log CONSISTENT=y
or
exp SYSTEM/password PARFILE=params.dat

where params.dat contains the following information

FILE=dba.dmp
GRANTS=y
FULL=y
ROWS=y
LOG=dba.log

To dump a single schema to disk (we use the scott example schema here)
----------------------------
- Login to server which has an Oracle client
exp / FIlE=scott.dmp OWNER=scott


To export specific tables to disk.
----------------------------
- Login to server which has an Oracle client
exp SYSTEM/password FIlE=expdat.dmp TABLES=(scott.emp,hr.countries)
-the above command uses two users : scott and hr

exp / FILE=scott.dmp TABLES=(emp,dept)
the above is only for one user