Use the IMP and exp commands of Oracle to import and export data

This article introduces the import and export of Oracle Data IMP and exp commands, describes the corresponding parameters, and then uses some examples for further 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 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.

Iii. Example

3.1 data export:

1. Completely export the database sampledb, and export the username System Password Manager to E: \ sampledb. dmp.

Exp system/manager @ testdb file = E: \ sampledb. dmp full = y

2. Export the tables of system users and SYS users in the database

Exp system/manager @ testdb file = E: \ sampledb. dmp owner = (system, sys)

3. Export the tables tablea and tableb in the database.

Exp system/manager @ testdb file = E: \ sampledb. dmp tables = (tablea, tableb)

4. Export the data with the field filed1 in Table A in the database as "Wang Wu"

Exp system/manager @ testdb file = E: \ sampledb. dmp tables = (tablea) query = 'where filed1 = 'wang 5'
To compress the DMP file, add compress = Y to the end of the preceding command.

3.2 Data Import

1. Import the data in the backup database file to the specified database sampledb. If sampledb already exists in this table, it will not be imported;

IMP system/manager @ test file = E: \ sampledb. dmp full = y ignore = y

2. Import table 1 in D: \ daochu. dmp

IMP system/manager @ test file = E: \ sampledb. dmp tables = (Table1)

3. Import a complete database

IMP system/manager file = bible_db log = dible_db full = y ignore = y

4. import one or more specified tables, indexes, and other objects to which the user belongs.

IMP system/manager file = seapark log = seapark fromuser = seapark imp
System/manager file = seapark log = seapark fromuser = (seapark, Amy, amyc, Harold)

5. Import the data of one user to another.

IMP system/manager file = Tank log = Tank fromuser = seapark touser = seapark_copy
IMP system/manager file = Tank log = Tank fromuser = (seapark, Amy)
Touser = (seapark1, amy1)

6. Import a table

IMP system/manager file = Tank log = Tank fromuser = seapark tables = (a, B)

7. Import from multiple files

IMP system/manager file = (paycheck_1, paycheck_2, paycheck_3, paycheck_4)
Log = paycheck, filesize = 1g full = y

8. Use the parameter file

IMP system/manager parfile = bible_tables.par
Bible_tables.par parameter file:
# Import the sample tables used for the Oracle8i Database Administrator's
Bible. fromuser = seapark touser = seapark_copy file = seapark log = seapark_import
For parameter file examples, see Appendix

9. incremental Import

IMP system./manager inctype = rectore full = y file =

In many cases, you must first completely delete the table and then import it.

Iv. parameter description

4.1 common 8i exp options
1. Full. This is used to export the entire database. When rows = N is used together, the structure of the entire database can be exported. For example:
Exp sys file =./db_str.dmp log =./db_str.log full = y rows = n compress = y direct = y
2. Buffer and feedback. When exporting a large amount of data, I will consider setting these two parameters. For example:
Exp new file = yw97_2003.dmp log = yw97_2003_3.log feedback = 10000 buffer = 100000000 tables = wo4, OK _yt
3. Fill and log. The two parameters respectively specify the DMP name and log name for the backup, including the file name and directory. For example, see the preceding figure.
It should be noted that exp can be directly backed up to the tape, that is, file =/dev/rmt0 (the name of the tape device) is used, but we generally do not do this for two reasons: 1. This operation will be much slower. 2. Currently, tape libraries are generally used. You are not advised to operate the tapes directly. If you do not use a tape library, you can use it with the tar of UNIX.
If you really want to use exp directly to tape, you can refer to the Metalink article "exporting to tape on UNIX systems" (document No.: 30428.1). This article provides a detailed explanation.
4. The compress parameter will merge chunks while exporting, and try to compress the data to the initial extent. The default value is N. It is generally recommended to use. The direct parameter tells exp to directly read the data, instead of using select to read the data in the table as the traditional exp, thus reducing the SQL statement processing process. It is also recommended. However, in some cases, direct parameters cannot be used.
5. How can I use sysdba to execute exp/imp?
This is a very practical problem. Sometimes we need to use sysdba to execute exp/IMP, for example, to transmit the exp/imp of the tablespace, sysdba is required for executing exp/imp with sys users in 9i. We can use the following method to connect to exp/IMP:
Exp "'sys/sys as sysdb'" file = 1.dmp tables = gototop. t rows = N
6. the query parameter is followed by the where condition. It is worth noting that the entire WHERE clause must be enclosed by "". The write method of the where clause is the same as that in the select clause, for All "and 'on UNIX platforms, use \ u000069 to block their special meanings:
Exp gototop/gototop file = 1.dmp log = 1.log tables = cyx. t query = "where c1 = 20 and C2 = gototop"
For Windows, the following format is used:
Exp C/C @ NCN file = C. dmp log = C. Log tables = T query = "where id = 1 and name = 'gototop '"""
4.2 common 8i imp options
1. Use fromuser and touser to import data from one schema to another.
2. Ignore, grants, and indexes. The ignore parameter ignores the existence of the table and continues the import. This is useful when you need to adjust the storage parameters of the table, we can create a table with reasonable storage parameters according to the actual situation, and then import the data directly. Grants and indexes indicate whether to import authorization and indexes. If you want to use the new storage parameter to re-create the index, or to speed up the import, you can consider setting indexes to n, grants is generally y.
Another exp/IMP parameter is parfile, which is a parameter file used to define exp/imp. That is to say, the above parameters can be written in a parameter file, but we rarely use it.
4.4. Oracle9i exp Function Description
Oracle9i exp adds some new parameters on the basis of the original, which are mainly divided into the following parts by function:
1. object_consistent-used to set the exp object to read-only to maintain object consistency. The default value is N.
2. flashback_scn and flashback_time-are added to support the flashback function.
3. resumable, resumable_name, and resumable_timeout-are added to support resumable space allocation.
4. tts_full_check-used to use dependency check when transferring tablespaces.
5. template-is used to support IAS.
6. tablespaces-set the tablespace export mode. I personally think that for general users, this is the most practical new parameter. It allows users to have another option based on the original full, owner, and tables, making exp more flexible.

5. What are the exp/IMP problems of different versions?
Generally, it is not a problem to import data from a lower version to a later version. The trouble is to import data from a later version to a lower version. Before Oracle9i, EXP/IMP between Oracle versions can be solved through the following methods:
1. Run catexp. SQL of the base version on the High Version database;
2. Use exp of a lower version to export data of a later version;
3. Use the lower-version IMP to import the database to the database of the actual version;
4. Run the catexp. SQL script in the later version of the database.
But in 9i, the above method cannot solve the problem. If you use the base version exp/IMP directly, the following error occurs:
EXP-00008: Oracle error % lu encountered
ORA-00904: Invalid column name
This is a published bug that can be solved only after oracle10.0. The bug number is 2261. You can go to Metalink to view details about this bug.
Bug is a bug. We still need to do our work. We should solve the bug by ourselves before we have Oracle Support. Execute the following SQL statement in Oracle9i to recreate the exu81rls view.
Create or replace view exu81rls
(Objown, objnam, policy, polown, polsch, polfun, mongots, chkopt, enabled, spolicy)
As select U. Name, O. Name, R. pname, R. pfschma, R. ppname, R. pfname,
Decode (bitand (R. stmt_type, 1), 0, '', 'select ,')
| Decode (bitand (R. stmt_type, 2), 0, '', 'insert ,')
| Decode (bitand (R. stmt_type, 4), 0, '', 'Update ,')
| Decode (bitand (R. stmt_type, 8), 0, '', 'delete ,'),
R. check_opt, R. enable_flag,
Decode (bitand (R. stmt_type, 16), 0, 0, 1)
From user $ U, OBJ $ o, RLS $ R
Where U. User # = O. Owner #
And R. OBJ # = O. OBJ #
And (uid = 0 or
Uid = O. Owner # Or
Exists (select * From session_roles where role = 'select _ catalog_role ')
)
/
Grant select on SYS. exu81rls to public;
/

6. Other problems

This article only discusses some situations of exp/IMP in Oracle8i and 9i. For earlier versions, except query parameters cannot be used in 8.0.x, there is little difference. In the absence of query, We can first create a temporary intermediate table using the query conditions in the database, and then export the intermediate table using exp. As for oracle7, because there are few users currently, gototop does not intend to explain it in detail here. If you have any requirements, you can refer to the Metalink Document: "Overview of export and import in oracle7" (document No.: 61949.1 ). You can obtain detailed parameters of exp/IMP through exp/IMP help = y.
For more information about the tablespace to be transmitted, refer to the metelink document below.
[Note: 77523.1] transportable tablespaces -- An Example to setup and use.
[Note: 100698.1] perform tablespace point-in-time recovery using transportable tablespace.
During parallel exp/IMP operations, if the IMP process creates an index, we do not recommend that you run more than five imp operations at the same time. If you want to speed up, You can do not create an index at Imp, in this way, you can run a few more times as long as the memory permits, and then create the required index in the SQL script.

Note:
If the operator has sufficient permissions, a prompt is displayed.
Whether the database can be connected or not. You can use tnsping testdb to obtain whether the database can be connected.

Appendix 1:
Add data import permissions to users
First, start SQL * puls
Second, log in with system/Manager
Third, create user username identified by password (this step can be omitted if you have already created a user)
Fourth, grant create user, drop user, alter user, create any view,
Drop any view, exp_full_database, imp_full_database,
DBA, connect, resource, create session to Username
Fifth, run-cmd-to enter the directory where the DMP file is located,
IMP userid = system/manager full = y file = *. dmp
Or imp userid = system/manager full = y file = filename. dmp

Appendix 2:
Oracle cannot directly change the table owner. Export/Import can be used to achieve this purpose.
First create import9.par,
The command is as follows: IMP parfile =/filepath/import9.par
The content of import9.par is as follows:
Fromuser = tgpms
Touser = tgpms2 (Note: You can change the table owner from fromuser to touser. Users of fromuser and touser can be different)
Rows = y
Indexes = y
Grants = y
Constraints = y
Buffer= 409600
File =/backup/ctgpc_20030623.dmp
Log =/backup/import_20030623.log