Oracle export and import

Using exp:

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 <user>/<password> 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 <user>/<password> FILE=scott.dmp TABLES=(emp,dept)

the above is only for one user

Using imp:

To import the full database exported in the example above.

imp SYSTEM/password FULL=y FIlE=dba.dmp LOG=import.log

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 exported in the example above

imp <user>/<password> FIlE=scott.dmp