Oracle Foundation EXP/IMP Command

First, the Export method:

There are four ways to export data using the Exp/imp method:

1. Table mode export: One or more specified tables, including the table definition, table data, table owner authorization, table index, table constraints, and triggers created on the table. You can also export only structures and not export data. You can also export all the tables owned by the user, and you can also specify the partitions of the exported table.

2. User mode export: All objects in the user pattern and the data in the object.

3. Tablespace Export: All objects contained in the specified table space, and the index definition on the object.

4. All database export: Refers to all objects in the database, including tablespaces, users, and all objects in the schema (tables, views, sequences, synonyms, constraints, indexes, stored procedures and triggers, etc.), data, and permissions.

Second, export the data with the EXP command.

Grammar:

Exp Userid=username/password direct=y full=y rows=y file=d:/backup/back.dmp log=d:/backup/back.log

Description

Userid=username/password: Represents the user name and password.

Direct=y: Whether to export by direct path

Full=y: Indicates all database exports

Rows=y: Represents the export along with the data in the base table

File: Export files

LOG: Exported Journal file

The parameter can be one or more, and if no required parameters are given at the command line, the Export utility prompts the user to enter it individually.

　　

Because of too many exp parameters, you can obtain the EXP syntax information by entering exp help=y.  

By entering the EXP command and username/password, you can then command the username/password:

Routine: EXP Scott/tiger

Alternatively, you can control "export" by different parameters by entering an EXP command with various parameters. To specify parameters, you can use keywords:

Format: EXP keyword=value or keyword= (value1,value2,..., Valuen)
Routines: EXP scott/tiger grants=y tables= (emp,dept,mgr) or tables= (T1:P1,T1:P2), if T1 is a partitioned table

The USERID must be the first parameter in the command line.

Keyword description (default) keyword description (default)
--------------------------------------------------------------------------
USERID username/password Full export entire file (N)
Buffer Data buffer size owner owner user Name list
File output files (expdat. DMP) TABLES table Name list
COMPRESS Import to a zone (Y) RecordLength IO record length
GRANTS Export Permission (Y) Inctype Incremental Export type
INDEXES Export Index (y) RECORD tracking incremental export (Y)
Direct path (N) TRIGGERS export Trigger (Y)
Log screen output STATISTICS Analysis Object (ESTIMATE)
Rows Export data row (Y) parfile parameter file name
Consistent cross-table conformance CONSTRAINTS export constraints (Y)

FEEDBACK Show progress per x line (0)
FILESIZE maximum size per dump file
FLASHBACK_SCN the SCN used to callback session snapshots
Flashback_time the time to obtain the SCN closest to the specified time
Select clause that the QUERY uses to export a subset of the table
Resumable hangs when encountering a space-related error (N)
Resumable_name text string used to identify a recoverable statement
Resumable_timeout resumable Waiting time
Tts_full_check performing a full or partial correlation check on TTS
tablespaces List of table spaces to export
Transport_tablespace exporting the Transportable tablespace metadata (N)
Template call IAS schema exported templates name

Example:

　　1) fully export the database

Exp System\[email protected] file= ' d:\backup\full.dmp ' full=y

　　2) Export user

　　exp system/[email protected] file= ' D:\backup\scott.dmp ' owner= (SCOTT)

　　3) Export Table

　　exp scott/[email protected] file= ' D:\backup\table.dmp ' tables= (emp,dept)

　　4) Export Table space

　　exp scott/[email protected] file= ' d:\backup\table.dmp ' Tablespaces= (users)

If you want to compress the DMP file, you can do so by adding compress=y to the above command.

Iii. importing data using the IMP command　　

You can get the syntax information for IMP by entering IMP help=y at the command line:
=============================================================================
C:/Documents and Settings/auduser>imp help=y

Commands that can be followed by a username/password by entering the IMP command and your user name/password:

Routine: IMP Scott/tiger

Alternatively, you can control "import" according to different parameters by entering the IMP command and various parameters. To specify parameters, you can use keywords:

Format: IMP keyword=value or keyword= (value1,value2,..., Vlauen)
Routine: IMP scott/tiger ignore=y tables= (emp,dept) full=n
or tables= (T1:P1,T1:P2), if T1 is a partitioned table

The USERID must be the first parameter in the command line.

Keyword description (default) keyword description (default)
--------------------------------------------------------------------------
USERID username/password Full import entire file (N)
Buffer data buffer size fromuser owner user Name list
File input files (expdat. DMP) touser List of user names
SHOW only list File contents (N) TABLES table Name list
IGNORE Ignore creation error (N) length of RecordLength IO record
GRANTS Import Permission (Y) Inctype incremental import type
INDEXES Import Index (Y) Commit commit array insert (N)
Rows Import data row (Y) parfile parameter file name
Log screen output CONSTRAINTS import limit (Y)
DESTROY Overlay tablespace data file (N)
Indexfile Writing table/index information to the specified file
Skip_unusable_indexes Skip maintenance of indexes that are not available (N)
FEEDBACK Show progress per x line (0)
Toid_novalidate skipping validation of a specified type ID
FILESIZE maximum size per dump file
STATISTICS always import precomputed statistics
Resumable hangs when encountering a space-related error (N)
Resumable_name text string used to identify a recoverable statement
Resumable_timeout resumable Waiting time
COMPILE compilation process, packages and functions (Y)

The following keywords are only available for transportable tablespaces
Transport_tablespace import of transportable tablespace metadata (N)
Tablespaces the tablespace that will be transferred to the database
Datafiles data files that will be transferred to the database
Tts_owners has users who can transmit data in a table-space set

Example:

　　1) Import All

Imp scott/[email protected] file=full.dmp full=y

　　2) Import Table

Imp scott/[email protected] file=table.dmp tables= (emp,dept)

　　3) Import Users

　　imp scott/[email protected] file=d:\backup\scott.dmp FROMUSER=SCOtt Touser=scott

Oracle Foundation EXP/IMP Command