EXP & IMP COMMAND

Copy paste dari tetangga sebelah

Format:  EXP KEYWORD=value or KEYWORD=(value1,value2,…,valueN)

Example: EXP SCOTT/TIGER GRANTS=Y TABLES=(EMP,DEPT,MGR)

or TABLES=(T1:P1,T1:P2), if T1 is partitioned table

Keyword                               Description (Default)

————————————————————–

USERID                                 username/password

BUFFER                                size of data buffer

FILE                                       output files (EXPDAT.DMP)

COMPRESS                          import into one extent (Y)

GRANTS                               export grants (Y)

INDEXES                             export indexes (Y)

DIRECT                                direct path (N)

LOG                                       log file of screen output

ROWS                                   export data rows (Y)

CONSISTENT                     cross-table consistency(N)

FULL                                    export entire file (N)

OWNER                               list of owner usernames

TABLES                                 list of table names

RECORDLENGTH              length of IO record

INCTYPE                              incremental export type

RECORD                              track incr. export (Y)

TRIGGERS                            export triggers (Y)

STATISTICS                         analyze objects (ESTIMATE)

PARFILE                               parameter filename

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

TABLESPACES                    list of tablespaces to export

TRANSPORT_TABLESPACE           export transportable tablespace metadata (N)

TEMPLATE                           template name which invokes iAS mode export

The Export and Import tools support four modes of operation

FULL               :Exports all the objects in all schemas
OWNER             :Exports objects only belonging to the given OWNER
TABLES           :Exports Individual Tables
TABLESPACE  :Export all objects located in a given TABLESPACE.

Using Import Utility

Objects exported by export utility can only be imported by Import utility. Import utility can  run in Interactive mode or command line mode.

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

Example: IMP SCOTT/TIGER

Or, you can control how Import runs by entering the IMP command followed

by 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=N

or TABLES=(T1:P1,T1:P2), if T1 is partitioned table

USERID must be the first parameter on the command line.

Keyword Description (Default)
USERID username/password
BUFFER size of data buffer
FILE input files (EXPDAT.DMP)
SHOW just list file contents (N)
IGNORE ignore create errors (N)
GRANTS import grants (Y)
INDEXES import indexes (Y)
ROWS import data rows (Y)
LOG log file of screen output
FULL import entire file (N)
FROMUSER list of owner usernames
TOUSER list of usernames
TABLES list of table names
RECORDLENGTH length of IO record
INCTYPE incremental import type
COMMIT commit array insert (N)
PARFILE parameter filename
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_INSTANITATION import streams instantiation metadata (N)

Example Importing Individual Tables

To import individual tables from a full database export dump file give the following command

$imp scott/tiger FILE=myfullexp.dmp FROMUSER=scott TABLES=(emp,dept)

This command will import only emp, dept tables into Scott user and you will get a output similar  to as shown below

Export file created by EXPORT:V10.00.00 via conventional path

import done in WE8DEC character set and AL16UTF16 NCHAR character set

. importing SCOTT’s objects into SCOTT

. . importing table                         “DEPT”          4 rows imported

. . importing table                          “EMP”         14 rows imported

Import terminated successfully without warnings.

~ by Ali Imran on September 29, 2011.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: