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=yor
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
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
If you do not supply any parameters then you enter an interactive session as illustrated below. (Responses to prompts are shown in blue. from the prompt type imp and press enter
$>imp
Import: Release 9.2.0.6.0 - Production on Thu Mar 29 15:07:43 2007
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Username: SYSTEM
Password: password
Connected to: Oracle9i Enterprise Edition Release 9.2.0.6.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0 - Production
Import file: expdat.dmp > /mention/path/of/dumpFile/includingFileName.dmp
Enter insert buffer size (minimum is 8192) 30720> (press enter to accept default)
Export file created by EXPORT:V09.02.00 via conventional path
import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses AL32UTF8 character set (possible charset conversion)
List contents of import file only (yes/no): no > press enter
Ignore create error due to object existence (yes/no): no > press enter
Import grants (yes/no): yes > press enter
Import table data (yes/no): yes > press enter
Import entire export file (yes/no): no > press enter or type no
Username: give the userName for which you want the data to be imported
Enter table(T) or partition(T:P) names. Null list means all tables for user
Enter table(T) or partition(T:P) name or . if done: press enter
. importing TST_001_V2's objects into TST_001_V2
source: http://wiki.oracle.com/page/Oracle+export+and+import+
