Oracle Import and Export commands

Oracle Import Export Command parsing

In this paper, the import and export of Oracle data IMP, exp Two commands are described, and its corresponding parameters are described, and then through some examples to

Drills to deepen understanding.
At the end of this paper, we discuss the possible problems of using these two commands (such as insufficient authority, different Oracle versions), and propose corresponding solutions.
This part of the content excerpt from the network, thanks to the user's experience summary;

I. Description

Oracle's EXP/IMP command is used to implement export/import operations to the database;
The EXP command is used to export data from the remote database server to local, generating DMP files;
The IMP command is used to import local database dmp files from local to the remote Oracle database.

Two. Syntax

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

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 username/password:

Routine: IMP Scott/tiger

Alternatively, you can control the import by entering the IMP command and various parameters
According to different 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

==============================================
You can also get the EXP syntax information by entering exp help=y

Microsoft Windows XP [version 5.1.2600]
(C) Copyright 1985-2001 Microsoft Corp.

C:\Documents 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 username/password, you can
Command followed by username/password:

Routine: EXP Scott/tiger

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

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

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

Three. Using the example

3.1 Data export:

1 full export of database SampleDB, user Name System Password Manager exported to E:\SampleDB.dmp

Exp system/[email protected] file=e:\sampledb.dmp full=y

2 Exporting the system user in the database to the SYS user's table

Exp system/[email protected] file=e:\sampledb.dmp owner= (System,sys)

3 Export the table Tablea,tableb in the database

Exp system/[email protected] file=e:\sampledb.dmp tables= (Tablea,tableb)

4 Export the data in the table TableA in the database to a filed1 value of "Harry"

Exp system/[email protected] file=e:\sampledb.dmp tables= (TableA) query= ' where filed1= ' Harry '

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

3.2 Import of data

1 imports the data from the backup database file into the specified database SampleDB, and if the table is already present in SampleDB, it is no longer imported;

Imp system/[email protected] file=e:\sampledb.dmp full=y ignore=y

2 Import the table table1 in D:\daochu.dmp

Imp system/[email protected] file=e:\sampledb.dmp tables= (table1)

3. Import a full database

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

4. Import all of the tables, indexes, and other objects that a specified user or group belongs to

Imp system/manager file=seapark log=seapark Fromuser=seapark Imp
System/manager File=seapark Log=seapark fromuser= (seapark,amy,amyc,harold)

5. Import data belonging to one user into another user

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. Importing from multiple Files

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

8. Using 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
Examples of parameter files are shown in appendix

9. Incremental Import

Imp system./manager inctype= rectore full=y file=a

In many cases, the table must be completely removed and then imported.

Four. Parameter description

4.1.8i Exp Common Options
1, full, this is used to export the entire database, when used in conjunction with rows=n, you can export the entire database structure. 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 more 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, these two parameters specify the DMP name and log name of the backup, including the file name and directory, see above for example.
It should be noted that EXP can be directly backed up to tape, that is, using FILE=/DEV/RMT0 (tape device name), but generally we do not do so because of

2:1, the speed will be much slower, second, now generally use the tape library, it is not recommended to operate directly on the tape. As for not using the tape library

Friends can consider using the tar with 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 number:

30428.1), which is explained in detail in this article.
4, compress parameters will be exported at the same time merge pieces, as far as possible to compress the data into initial extent, the default is N, generally recommended use. DIRECT

The parameter will tell exp to read the data directly, instead of using Select to read the data in the table as the traditional exp does, thus reducing the SQL statement process

。 It is generally recommended to use. In some cases, however, the direct parameter is not available.
5, how to use SYSDBA to perform exp/imp?
This is a very real problem, and sometimes we need to use SYSDBA to perform exp/imp, such as exp/imp for the transfer table space and SYS at 9i

When the user executes the exp/imp, it is necessary to use SYSDBA.

We can use the following method to connect to the Exp/imp:
Exp "' Sys/sys as Sysdba '" File=1.dmp tables=gototop.t rows=n
6, the query parameter followed by the Where condition, it is worth noting that the entire WHERE clause needs to use "", the WHERE clause in the wording and select

Similarly, if all "and" of the UNIX platform need to use \u26469 to mask their special meanings:
Exp gototop/gototop file=1.dmp log=1.log tables=cyx.t query= "where c1=20 and C2=gototop"
If it is a Windows platform, use the following format:
Exp c/[email protected] file=c.dmp log=c.log tables=t query= "" "where id=1 and Name= ' Gototop '" "
4.2.8i Imp Common options
1, Fromuser and Touser, using them to import data from one schema into another schema.
2, IGNORE, grants, and indexes, where the IGNORE parameter ignores the existence of the table and continues the import, which is useful when you need to adjust the table's storage parameters

, we can build the table with reasonable storage parameters according to the actual situation, and then import the data directly. Grants and indexes, however, indicate whether to import authorization and

Index, if you want to rebuild the index with a new storage parameter, or to speed it up, we can consider setting indexes to n, whereas grants is generally

Y.
Another exp/imp parameter is Parfile, which is used to define the Exp/imp parameter file, that is, the above parameters can be written in a parameter

Few files, but we seldom use them in general.
4.4. oracle9i Exp Function Description
Oracle9i Exp added some new parameters on the basis of the original, according to the function mainly divided into the following parts:
1, object_consistent-used to set the Exp object to read-only to preserve object consistency. The default is N.
2, FLASHBACK_SCN and Flashback_time-added to support FLASHBACK functionality.
3, Resumable, Resumable_name, and Resumable_timeout-added to support resumable space allocation.
4, Tts_full_check-used to use dependency checking when transferring table space.
5. TEMPLATE-Used to support IAS.
6, tablespaces-Set the table space Export mode. Personally, for the average user, this is the most useful one of the new parameters, you can let

Customers in the original full, OWNER, tables on the basis of a choice, making exp more flexible.

Five, different version of the Exp/imp problem?
In general, the issue of importing from a lower version to a higher version is not a big problem, but it is troublesome to import the high version of the data into the lower version, before oracle9i, the different versions

The exp/imp between Oracle can be resolved in the following ways:
1. Run the low version of Catexp.sql on the high version database;
2, use the lower version of EXP to export the high version of the data;
3. Use the lower version of IMP to import the database into the version database;
4. Rerun the high-version Catexp.sql script on the high-version database.
But in 9i, the above method does not solve the problem. If you use the lower version of Exp/imp directly, the following error will occur:
Exp-00008:oracle Error%lu encountered
Ora-00904:invalid Column Name
This is already a published bug, need to wait until Oracle10.0 to solve, the bug number is 2261, you can go to Metalink to see the details about this bug

Information.
Bugs are bugs, and our job is to do it, and we'll fix it ourselves before we have Oracle support. Perform the following SQL rebuild in Oracle9i

Exu81rls view.
CREATE OR REPLACE View Exu81rls
(Objown,objnam,policy,polown,polsch,polfun,stmts,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;
/

Vi. Other issues

This article only discusses some of the exp/imp in Oracle8i and 9i, for the previous version, in 8.0.X, except that the query parameter is not available, the other difference

Not much. For cases where there is no query, we can first create a temporary intermediate table in the database using the query criteria, and then export the intermediate table using exp.

Can As for Oracle7 because of the current use of fewer people, gototop do not intend to do a detailed explanation in this, if the reader friends have needs, you can refer to

Metalink Document: "Overview of Export and Import in Oracle7" (Document Number: 61949.1). Detailed parameter information about the EXP/IMP

You can get it by Exp/imp Help=y.
Additional information about the transfer table space can be found in the following Metelink documentation, which is not detailed in this article.
[note:77523.1] Transportable tablespaces-a Example to setup and use.
[note:100698.1] Perform tablespace point-in-time Recovery using transportable tablespace.
In parallel exp/imp, if the IMP process is indexed, it is not recommended to run more than 5 imp at the same time, if you want to speed up, you can

Do not build indexes, so as long as the memory allows, you can run a few more, and then the SQL script to create the required index.

Attention:
The operator must have sufficient permissions, and it will prompt for insufficient permissions.
Whether the database can be connected, you can use tnsping TestDB to get the database TestDB can be connected.

Appendix I:
Adding permissions to the import data to the user
First, start sql*puls
Second, landing with System/manager
Third, create user username identified by password (this step can be omitted if the user has already been created)
IV, 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 User name
V, run-cmd-into 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 II:
Oracle does not allow direct changes to the table owner, which can be achieved with export/import.
Build Import9.par First,
Then, use the command as follows: Imp Parfile=/filepath/import9.par
Example Import9.par content is as follows:
Fromuser=tgpms
TOUSER=TGPMS2 (Note: Users who change the owner of the table from Fromuser to Touser,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

Oracle Import and Export commands