Import / Export Basics

on January 11, 2008

Here is some notes on Import / Export;


Look at the imp/exp parameters before starting.

These parameters can be listed by executing the following commands: “exp help=yes” or “imp help=yes".


The following examples demonstrate how the imp/exp utilities can be used:


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

exp scott/tiger file=emp.dmp tables=(emp,dept)


imp scott/tiger file=emp.dmp full=yes

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


exp userid=scott/tiger@orcl parfile=export.txt


… where export.txt contains:

BUFFER=100000

FILE=account.dmp

FULL=n

OWNER=scott

GRANTS=y

COMPRESS=y


NOTE: If you do not like command line utilities, you can import and export data with the “Schema Manager” GUI that ships with Oracle Enterprise Manager (OEM).


Here is the Help result:


You can let Import prompt you for parameters by entering the IMPcommand followed by your username/password:


Example:

IMP SCOTT/TIGER


Or, you can control how Import runs by entering the IMP command followedby various arguments. To specify parameters, you use keywords:


Format:

IMP KEYWORD=value or

KEYWORD=(value1,value2,…,valueN)



Example: IMP SCOTT/TIGER IGNORE=Y TABLES=(EMP,DEPT) FULL=Nor TABLES=(T11,T12), if T1 is partitioned table


USERID must be the first parameter on the command line.


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 statementRESUMABLE_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)
The following keywords only apply to transportable tablespacesTRANSPORT_TABLESPACE import transportable tablespace metadata (N)TABLESPACES tablespaces to be transported into databaseDATAFILES datafiles to be transported into databaseTTS_OWNERS users that own data in the transportable tablespace set

ShareThis