How to import data from Oracle

This article introduces the import and export of Oracle Data imp and exp commands, and describes the corresponding parameters.

Drills to deepen understanding.

At last, the article discusses the problems that may occur when using these two commands (such as insufficient permissions and different oracle versions), and puts forward corresponding solutions;

The content in this article is excerpted from the Internet. Thanks for your experience;

I. Description

The oracle exp/imp command is used to export/import databases;

The exp command is used to export data from a remote database server to a local machine to generate a dmp file;

The imp command is used to import the dmp file of the local database from the local database to a remote Oracle database.

Ii. Syntax

You can enter imp help = y in the command line to obtain the syntax information of imp:

========================================================== ============================================

C: \ Documents ents and Settings \ auduser> imp help = y

Import: Release 9.0.1.1.1-Production on Tuesday May 20 18:21:57 2008

(C) Copyright 2001 Oracle Corporation. All rights reserved.

You can enter the IMP command and your username/password.

Command followed by user name/password:

Routine: imp scott/TIGER

Alternatively, you can control "import" by entering the IMP command and various parameters"

According to different parameters. To specify parameters, you can use the Keyword:

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

USERID must be the first parameter in the command line.

Keyword description (default) keyword description (default)

--------------------------------------------------------------------------

USERID username/password FULL import the entire file (N)

BUFFER data BUFFER size FROMUSER User Name List

FILE input FILE (EXPDAT. DMP) TOUSER username list

SHOW only lists the file content (N) TABLES Table names

IGNORE ignores creation error (N) recordlength io record length

GRANTS import permission (Y) INCTYPE incremental Import Type

INDEXES import index (Y) COMMIT submit array insert (N)

ROWS import data row (Y) PARFILE parameter file name

Restrictions on importing LOG files output by LOG screens (Y)

DESTROY overwrite the tablespace data file (N)

INDEXFILE writes table/index information to the specified file

SKIP_UNUSABLE_INDEXES skips maintenance of unavailable indexes (N)

The progress of FEEDBACK is displayed on every x rows (0)

TOID_NOVALIDATE skips the verification of the specified type ID

FILESIZE: maximum size of each dump.

STATISTICS always imports pre-calculation STATISTICS

When the RESUMABLE encounters a space-related error, it suspends (N)

RESUMABLE_NAME is a text string used to identify recoverable statements.

The waiting time of RESUMABLE_TIMEOUT RESUMABLE.

COMPILE compilation process, package and function (Y)

The following keywords are only used for table spaces that can be transferred.

TRANSPORT_TABLESPACE import the deletable tablespace metadata (N)

TABLESPACES tablespace to be transmitted to the database

Data files to be transmitted to the database

TTS_OWNERS has users who can transmit table space centralized data.

========================================================== ======

You can also enter exp help = y to obtain the exp syntax information.

Microsoft Windows XP [version 5.1.2600]

(C) Copyright 1985-2001 Microsoft Corp.

C: \ Documents ents and Settings \ auduser> exp help = y

Export: Release 9.0.1.1.1-Production on Tuesday May 20 18:26:34 2008

(C) Copyright 2001 Oracle Corporation. All rights reserved.

By entering the EXP command and user name/password, you can

Command followed by user name/password:

Routine: exp scott/TIGER

Alternatively, you can control "Export" by entering the EXP command with various parameters"

According to different parameters. To specify parameters, you can use the Keyword:

Format: exp keyword = value or KEYWORD = (value1, value2,..., valueN)

Routine: exp scott/tiger grants = y tables = (EMP, DEPT, MGR)

Or TABLES = (T1: P1, T1: P2). If T1 is a partitioned table

USERID must be the first parameter in the command line.

Keyword description (default) keyword description (default)

--------------------------------------------------------------------------

USERID username/password FULL export the entire file (N)

BUFFER data BUFFER size OWNER User Name List

FILE output FILE (EXPDAT. DMP) TABLES Table Name List

Length of the IO record that COMPRESS imports to a partition (Y) RECORDLENGTH

GRANTS export permission (Y) INCTYPE incremental export type

INDEXES export index (Y) RECORD trace incremental Export (Y)

DIRECT path (N) TRIGGERS export trigger (Y)

The LOG file STATISTICS analysis object (ESTIMATE) output by the LOG Screen)

ROWS export data row (Y) PARFILE parameter file name

CONSISTENT cross tabulation consistency CONSTRAINTS export CONSTRAINTS (Y)

The progress of FEEDBACK is displayed on every x rows (0)

FILESIZE: maximum size of each dump.

FLASHBACK_SCN is used to call back the scn of the session snapshot.

FLASHBACK_TIME is used to obtain the time closest to the SCN of the specified time.

Select clause used by QUERY to export the subset of a table

When the RESUMABLE encounters a space-related error, it suspends (N)

RESUMABLE_NAME is a text string used to identify recoverable statements.

The waiting time of RESUMABLE_TIMEOUT RESUMABLE.

TTS_FULL_CHECK performs full or partial correlation check on TTS.

Table space list of TABLESPACES to Be Exported

TRANSPORT_TABLESPACE export the table space metadata that can be transferred (N)

TEMPLATE: the name of the TEMPLATE to be exported in iAS mode.