Traditional Import and Export Utilities

Export and Import Modes

There are 4 modes:
1. Full
2. Tablespace
3. User
4. Table

Export Session in Full Database Mode

Parameter File Method

exp SYSTEM/manager PARFILE=params.dat

params.dat:

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

Command-Line Method

exp SYSTEM/manager FULL=y FILE=dba.dmp GRANTS=y ROWS=y


Export Session in User Mode

Parameter File Method

exp scott/tiger PARFILE=params.dat

params.dat:

FILE=scott.dmp
OWNER=scott
GRANTS=y
ROWS=y
COMPRESS=y

Command-Line Method

exp scott/tiger FILE=scott.dmp OWNER=scott GRANTS=y ROWS=y COMPRESS=y 


Export Sessions in Table Mode

exp SYSTEM/manager TABLES=a, scott.b, c, mary.d


DBA Exporting Tables for Two Users

Parameter File Method

exp SYSTEM/manager PARFILE=params.dat

params.dat:

FILE=expdat.dmp
TABLES=(scott.emp,u1.t2)
GRANTS=y
INDEXES=y

Command-Line Method

exp SYSTEM/manager FILE=expdat.dmp TABLES=scott.emp,u1.t2 GRANTS=y INDEXES=y


User Exports Tables That He Owns

Parameter File Method

exp scott/tiger PARFILE=params.dat

params.dat:

FILE=scott.dmp
TABLES=(dept,salgrade)
ROWS=y
COMPRESS=y

Command-Line Method

exp scott/tiger FILE=scott.dmp TABLES=dept,salgrade ROWS=y COMPRESS=y


Using Pattern Matching to Export Various Tables

Parameter File Method

exp SYSTEM/manager PARFILE=params.dat

params.dat:

FILE=misc.dmp
TABLES=(scott.%P%,u1.%,scott.%S%)

Command-Line Method

exp SYSTEM/manager FILE=misc.dmp TABLES=scott.%P%,u1.%,scott.%S%



Import Sessions

Import of Selected Tables for a Specific User

Parameter File Method

imp SYSTEM/manager PARFILE=params.dat

params.dat:

FILE=dba.dmp
SHOW=n
IGNORE=n
GRANTS=y
FROMUSER=scott
TABLES=(dept,emp)

Command-Line Method

imp SYSTEM/manager FILE=dba.dmp FROMUSER=scott TABLES=(dept,emp)


Import of Tables Exported by Another User

Parameter File Method

imp SYSTEM/manager PARFILE=params.dat

params.dat:

FILE=blake.dmp
SHOW=n
IGNORE=n
GRANTS=y
ROWS=y
FROMUSER=blake
TOUSER=scott
TABLES=(unit,manager)

Command-Line Method

imp SYSTEM/manager FROMUSER=blake TOUSER=scott FILE=blake.dmp - TABLES=(unit,manager)


Import of Tables from One User to Another

Parameter File Method
imp SYSTEM/manager PARFILE=params.dat

params.dat:

FILE=scott.dmp
FROMUSER=scott
TOUSER=blake
TABLES=(*)

Command-Line Method
imp SYSTEM/manager FILE=scott.dmp FROMUSER=scott TOUSER=blake TABLES=(*)


Import Using Pattern Matching to Import Various Tables

Parameter File Method
imp SYSTEM/manager PARFILE=params.dat

params.dat:

FILE=scott.dmp
IGNORE=n
GRANTS=y
ROWS=y
FROMUSER=scott
TABLES=(%d%,b%s)

Command-Line Method
imp SYSTEM/manager FROMUSER=scott FILE=scott.dmp TABLES=(%d%,b%s)


Command-Line Entries

exp username/password PARAMETER=value
exp username/password PARAMETER=(value1, value2,...,valuen)

Parameter Files

exp PARFILE=filename
exp username/password PARFILE=filename

exp username/password PARFILE=params.dat INDEXES=n


Interactive Mode

exp username/password

exp scott/tiger FILE = dat1.dmp, dat2.dmp, dat3.dmp FILESIZE=2048

The following is an example of specifying an SCN. When the export is performed, the data will be consistent as of SCN 3482971.

exp system/manager FILE=exp.dmp FLASHBACK_SCN=3482971

exp system/manager FILE=exp.dmp FLASHBACK_TIME="TIMESTAMP '2002-05-01 11:00:00'"

exp system/manager FILE=exp.dmp FLASHBACK_TIME="TO_TIMESTAMP('12-02-2001 14:35:00', 'DD-MM-YYYY HH24:MI:SS')"

exp system/manager FILE=exp.dmp FLASHBACK_TIME="'2002-05-01 11:00:00'"

exp SYSTEM/manager LOG=export.log


Query

exp scott/tiger TABLES=emp QUERY=\"WHERE job=\'SALESMAN\' and sal \<1600\"

It will create a query at runtime like below:
SELECT * FROM emp WHERE job='SALESMAN' and sal <1600;