Oracle Imp Usage Details

Use of Oracle import program Imp

Oracle Import utility allows you to extract data from a database and write the data to an operating system file. The basic format used by imp: imp [username [/password [@ service]. The following describes common usage of imp.

1. Get help

 imp help=y

2. Import a complete database

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

3. 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)

4. 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)

5. Import a table

 imp system/manager file=tank log=tank fromuser=seapark TABLES=(a,b)

6. Import from multiple files

 imp system/manager file=(paycheck_1,paycheck_2,paycheck_3,paycheck_4) 

log=paycheck, filesize=1G full=y

7. 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
8. incremental Import
 imp system./manager inctype= RECTORE FULL=Y FILE=A
Oracle imp/exp
C: \ Documents ents and Settings \ administrator> exp help = y
Export: Release 9.2.0.1.0-Production on Wednesday July 28 17:04:43 2004
Copyright (c) 1982,200 2, 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 (N) CONSTRAINTS exported by CONSTRAINTS (Y)
OBJECT_CONSISTENT only sets transaction processing to read during object Export (N)
Progress of each row of FEEDBACK (0)
FILESIZE: maximum size of each dump.
FLASHBACK_SCN is used to set the session snapshot back to the previous state SCN
FLASHBACK_TIME is used to obtain the SCN time closest to the specified time.
QUERY: select clause used to export a subset of a table
When the RESUMABLE encounters a space-related error, it suspends (N)
RESUMABLE_NAME is a text string used to identify a recoverable statement.
The waiting time of RESUMABLE_TIMEOUT RESUMABLE.
TTS_FULL_CHECK performs a complete 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: name of the TEMPLATE to be exported in iAS mode.
The export is successfully terminated without warning.
========================================================== ============
C: \ Documents ents and Settings \ administrator> imp help = y
Import: Release 9.2.0.1.0-Production on Wednesday July 28 17:06:54 2004
Copyright (c) 1982,200 2, 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 a space error occurs, the RESUMABLE 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)
STREAMS_CONFIGURATION import the General metadata of Streams (Y)
STREAMS_INSTANITATION import the instantiation metadata of Streams (N)
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.
Import is terminated successfully, but a warning is displayed.
Oracle imp and exp usage --
 
Oracle8i/9i EXP/IMP experience
I. 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 '"""
Ii. 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.
Iii. 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.
4. 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;
/
V. 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.
Trackback: http://tb.blog.csdn.net/TrackBack.aspx? PostId = 898496