ORACLE export and import via parameter
files |
Export a table
1) Create the expTBL.par
parameter file with the following content:
userid=system
tables=OWNER.TABLENAME
2) Then run the following export command
as the ‘oracle’ user:
exp parfile=expTBL.par
If you want to export a few tables you
can specify a list of tables:
tables=(OWNER.TABLE1,
OWNER.TABLE2, OWNER.TABLE3)
Export a schema
1) Create the expTBL.par
parameter file with the following content:
userid=system
owner=OWNER
file=OWNER.dmp
log=OWNER.log
2) Then run the following export command
as the ‘oracle’ user:
exp parfile=expTBL.par
This will create a log file which you
can examine later for errors and
also will create the OWNER.dmp export dump file.
Help for export: exp
HELP=Y
If you need to have a quick look of the
possible options:
oracle@host> exp
HELP=Y
This will print out a brief summary of
the keywords that you can use.
Keyword Description (Default) Keyword Description (Default)
--------------------------------------------------------------------------
USERID username/password FULL export entire file (N)
BUFFER size of data buffer OWNER list of owner usernames
FILE output files (EXPDAT.DMP) TABLES list of table names
COMPRESS import into one extent (Y) RECORDLENGTH length
of IO record
GRANTS export grants (Y) INCTYPE incremental export type
INDEXES export indexes (Y) RECORD track incr. export (Y)
DIRECT direct path
(N) TRIGGERS export triggers (Y)
LOG log file of
screen output
STATISTICS analyze objects (ESTIMATE)
ROWS export data rows (Y) PARFILE parameter filename
CONSISTENT cross-table consistency(N) CONSTRAINTS
export constraints (Y)
OBJECT_CONSISTENT transaction set to read only during object
export (N)
FEEDBACK display progress every x rows (0)
FILESIZE maximum size of each dump file
FLASHBACK_SCN SCN used to set session snapshot back
to
FLASHBACK_TIME time used to get the SCN closest to the specified
time
QUERY select clause used to export a
subset of a table
RESUMABLE suspend when a space related error
is encountered(N)
RESUMABLE_NAME text string used to identify resumable statement
RESUMABLE_TIMEOUT wait time for RESUMABLE
TTS_FULL_CHECK perform full or partial dependency check
for TTS
VOLSIZE number of bytes to write to each
tape volume
TABLESPACES list of tablespaces
to export
TRANSPORT_TABLESPACE export
transportable tablespace metadata (N)
TEMPLATE template
name which invokes iAS mode
export
Import tables
For imp you can check the options with
issuing imp help=yes at the command prompt.
The example below sets the BUFFER
parameter to high, the INDEXES option to YES,
the source and target schemas, IGNORE to YES (meaning do not interrupt the
import
when an error – for example the schema
already exist – happens) and STATISTICS to
NONE (do not import statistics collected
in the source database).
1) Create the parameter file impTBS.par with the following content:
BUFFER=320000
USERID=SYSTEM
INDEXES=YES
FROMUSER=SOURCE_SCHEMA_NAME
TOUSER= TARGET_SCHEMA_NAME
IGNORE=Y
FILE=DIRNAME/expdat.dmp
LOG=DIRNAME/impTBSdat.log
STATISTICS=NONE
TABLES=\(
TABLE1,
TABLE2,
TABLE3,
TABLEn
\)
The tables can be specified if you do
not need all tables to be imported.
Note that the \ before parenthesis is required on
Linux/Unix systems.
2) run the
import
oracle@host> imp parfile=impTBS.par
OR if you want to run a long import in the background:
nohup oracle@host>
imp parfile=impTBS.par &
Import a schema
This simple example takes care of the
import of the whole schema into the target
database. Note that only a minimal set of
options were specified in this example.
USERID=SYSTEM
FROMUSER=SOURCE_SCHEMA_NAME
TOUSER= TARGET_SCHEMA_NAME
IGNORE=Y
FILE=DIRNAME/expdat.dmp
LOG=DIRNAME/impTBSdat.log
It is important to specify a log file as
you importing a whole schema and the number of objects can be very high and you
can’t really check the errors on the screen output.
Import options
With issuing imp help=yes you can get to
a similar summary of the options supported:
Keyword Description (Default) Keyword Description (Default)
--------------------------------------------------------------------------
USERID
username/password
FULL import entire file
(N)
BUFFER size of data
buffer FROMUSER list of owner usernames
FILE input files
(EXPDAT.DMP) TOUSER list of usernames
SHOW just list file
contents (N) TABLES list of table
names
IGNORE ignore
create errors (N) RECORDLENGTH length
of IO record
GRANTS import grants
(Y) INCTYPE incremental import type
INDEXES import indexes (Y)
COMMIT commit
array insert (N)
ROWS import data rows
(Y) PARFILE parameter filename
LOG log
file of screen output CONSTRAINTS import
constraints (Y)
DESTROY
overwrite tablespace data file (N)
INDEXFILE write
table/index info to specified file
SKIP_UNUSABLE_INDEXES skip maintenance of unusable indexes
(N)
FEEDBACK
display progress every x rows(0)
TOID_NOVALIDATE skip
validation of specified type ids
FILESIZE
maximum size of each dump file
STATISTICS import precomputed
statistics (always)
RESUMABLE
suspend when a space related error is encountered(N)
RESUMABLE_NAME text
string used to identify resumable statement
RESUMABLE_TIMEOUT wait
time for RESUMABLE
COMPILE compile procedures,
packages, and functions (Y)
STREAMS_CONFIGURATION import streams general metadata (Y)
STREAMS_INSTANTIATION import streams instantiation metadata
(N)
DATA_ONLY import
only data (N)
VOLSIZE number
of bytes in file on each volume of a file on tape
The following keywords only apply to transportable tablespaces
TRANSPORT_TABLESPACE import transportable tablespace
metadata (N)
TABLESPACES tablespaces to be transported
into database
DATAFILES datafiles to be transported
into database
TTS_OWNERS users that own data in the transportable tablespace set