ORACLE exp Command

In this paper, the import and export of Oracle data IMP, exp Two commands are introduced, and its corresponding parameters are described, and then through a number of demonstration examples to drill, deepen understanding.
At the end of this paper, we discuss the possible problems of using these two commands (such as insufficient authority, different Oracle version number), 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

The syntax information for IMP can be obtained 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.

Ability to 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
Depending on the number of references. To specify a number of parameters, you can use 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), assuming 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 All people username list
File input files (expdat. DMP) Touser Username list
SHOW only lists file contents (N) List of TABLES table names
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 run progress per x line (0)
Toid_novalidate skipping validation of a specified type ID
FILESIZE the maximum size of each dump file
STATISTICS always import estimates for statistical information
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 keyword are only available for transportable tablespace
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

==============================================
Same ability to get EXP's syntax information by entering exp help=y

Microsoft Windows XP [version number 5.1.2600]
(C) All copyrights 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 the 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
Depending on the number of references. To specify a number of parameters, you can use 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), assuming 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 buffers size owner all username 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 run progress per x line (0)
FILESIZE the maximum size of each 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 Full or partial correlation check for TTS operation
tablespaces List of table spaces to export
Transport_tablespace exporting the Transportable tablespace metadata (N)
Template call IAS schema exported templates name

Three. Use the Demo sample

3.1 Data export:

1 Export the database SampleDB completely, Usernamesystem Passwordmanager export 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 '

Assuming that you want to compress the DMP file, you can add compress=y to the command behind it.

3.2 Import of data

1 Import the data from the backup database file into the specified database SampleDB, assuming that the table SampleDB already exists, 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 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 from one user to 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 files:
#Import the sample tables used for the oracle8i Database Administrator ' s
Bible. Fromuser=seapark touser=seapark_copy File=seapark Log=seapark_import
Sample Document Demo example see 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. Explanation of the parameters

4.1.8i exp frequently used option
1, full, this is used to export the entire database, when used in conjunction with rows=n, the ability to export the entire database structure. Like what:
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. Like what:
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 specify the DMP name and log name of the backup, including the file name and folder, the sample see above.
It should be noted that EXP can be directly backed up to tape, that is, the use of file=/dev/rmt0 (tape device name), but generally we do not do so, for 2:1 of the time, it is very slow to do so much, two, now generally use the tape library, it is not recommended to operate directly on the tape. Friends who are not using a tape library can consider using the tar with UNIX.
If you really want to use exp directly to tape, you can refer to Metalink article "exporting to TAPE on UNIX SYSTEMS" (Document Number: 30428.1), which is detailed in this article.
4, compress will be exported at the same time to merge pieces, as far as possible to compress the data into the initial extent, the default is N, generally recommended use. Direct parameters will tell exp to read the data directly, rather than using Select to read the data in the table, as traditional exp does, thus reducing the SQL statement processing process. It is generally recommended to use. There are only a few cases where direct parameters are not available.
5, how to use sysdba run Exp/imp?
This is a very real problem, sometimes we need to use SYSDBA to run exp/imp, such as the exp/imp of the transmission table space, and in the 9i under the SYS user to run exp/imp, all need to use SYSDBA. We are able to connect to Exp/imp in the following ways:
Exp "' Sys/sys as Sysdba '" File=1.dmp tables=gototop.t rows=n
6, the query is followed by the Where condition, it is worth noting that the entire WHERE clause needs to use "", the WHERE clause in the same wording and select, it is assumed that the UNIX platform all "and" need to use/u26469 to mask their special meaning:
Exp gototop/gototop file=1.dmp log=1.log tables=cyx.t query= "where c1=20 and C2=gototop"
Assume that the Windows platform uses 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 frequently used options
1, Fromuser and Touser, using them to import data from one schema into another schema.
2, IGNORE, grants and indexes, among which the IGNORE will ignore the existence of the table, continue to import, this is necessary to adjust the table storage parameters is very practical, we can first based on the actual situation with a reasonable number of storage parameters to build a table, and then directly import data. While grants and indexes indicate whether to import authorizations and indexes, false assumptions to rebuild the index with new storage parameters, or to speed up to speed, we can consider setting indexes to n, and grants is generally y.
Another exp/imp is the Parfile, which is used to define the exp/imp of the parameters, that is, the above can be written in a parameter file, but we generally very little use.
4.4, oracle9i exp function Descriptive narration
Oracle9i exp On the basis of the original addition of new parts, according to the function is 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 general user, this is the most useful one of the new parameters, can let the user in the original full, OWNER, tables on the basis of a more choice, making exp more flexible.

Five, different version number of the Exp/imp problem?
In general, the issue of importing from a low version number to a higher version number is not a problem, but it is troublesome to import the data of the high version number into the lower version number, before Oracle9i, the exp/imp between different versions of Oracle can be resolved by the following methods:
1. Perform the catexp.sql of the base version number on the high version database;
2. Use exp of the lower version to export the data of the high version number;
3, using the low version of IMP to import the database into the version number database;
4. The catexp.sql script of the high version number is executed again on the high version database.
But in 9i, the above method does not solve this problem. Assume that using the base version number Exp/imp will appear for example the following error:
Exp-00008:oracle Error%lu encountered
Ora-00904:invalid Column Name
This is already a release bug, need to wait until Oracle10.0 talent resolution, the bug number is 2261, you can go to Metalink to see the specific information about this bug.
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 Exu81rls view in oracle9i.
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 number, in 8.0.X, except that the query parameters can not be used, the other difference is not big. 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. As for Oracle7 because of the few people currently using, gototop do not intend to do this in detail, if the reader friend needs, you can participate in the Metalink document: "Overview of the Export and Import in Oracle7" (Document number: 61949.1). Specific reference information about Exp/imp you can get through Exp/imp help=y.
In addition, a lot of other information about the transfer table space can refer to 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, assuming that the IMP process is indexed, it is not recommended to execute more than 5 imp at the same time, assuming you want to raise the speed, you can not build the index in the imp, so that only the memory consent, 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.
The database can be connected, can use tnsping TestDB to get the database TestDB can connect.

Appendix I:
  Add to user? Import data Permissions
  First, start Sql*puls
  Second, System/manager login
  Third, create user Username identified by password (assuming that the user has already been created, this step can be omitted)
  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 username
  V, execute-cmd-Enter the folder 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 agree to directly alter the owner of the table, using Export/import to achieve this goal.
Build Import9.par First,
Then, use the commands such as the following: Imp Parfile=/filepath/import9.par
Example Import9.par content such as the following:
Fromuser=tgpms
TOUSER=TGPMS2 (Note: Users who change the table owner 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 exp Command