Detailed introduction to logical export Exp/import Imp in Oracle and parameter Introduction

Import and export are two of the oldest command line tools survived by ORACLE. In fact, I never think ExpImp is a good backup method. The correct statement is that ExpImp can only be a good dumping tool, especially in the dumping of small databases, table space migration, table extraction, and detection of logical and physical conflicts. Of course, we can also take it

Import/export is the oldest two surviving ORACLE command line tools. In fact, I never think Exp/Imp is a good backup method, the correct statement is that Exp/Imp can only be a good dump tool, especially in small database dump, tablespace migration, table extraction, and detection of logical and physical conflicts. Of course, we can also take it

Import/export is the oldest two surviving ORACLE command line tools. In fact, I never think Exp/Imp is a good backup method, the correct statement is that Exp/Imp can only be a good dump tool, especially in small database dump, tablespace migration, table extraction, and detection of logical and physical conflicts. Of course, we can also use it as a logical secondary backup after physical backup of small databases, which is also a good suggestion. EXP/IMP becomes increasingly inadequate for larger databases, especially TB-level databases and more data warehouses. At this time, database backup is switched to RMAN and third-party tools. The following describes the use of EXP/IMP.

How to display exp help in different character sets: set nls_lang = simplified chinese_china.zhs16gbk. By setting environment variables, you can display exp help in Chinese. If set nls_lang = American_america. character Set, so the help is English.

All EXP parameters (the default values in parentheses ):

USERID username/password, for example, USERID = duanl/duanl

FULL export the entire database (N)

BUFFER data BUFFER size

OWNER owner user name list. If you want to export the user's object, use OWNER = username

FILE output FILE (EXPDAT. DMP)

TABLES table Name List, specifying the name of the exported table, such as: TABLES = table1, table2

COMPRESS imports an extent (Y)

Recordlength io record length

GRANTS export permission (Y)

INCTYPE incremental export type

INDEXES export index (Y)

Incremental export of RECORD tracking (Y)

ROWS export data ROWS (Y)

PARFILE parameter file name. If you have many exp parameters, you can save them as parameter files.

CONSTRAINTS export constraint (Y)

CONSISTENT cross tabulation consistency

LOG File output by LOG Screen

STATISTICS Analysis object (ESTIMATE)

DIRECT path (N)

TRIGGERS export trigger (Y)

FEEDBACK displays the progress of each x row (0)

FILESIZE maximum size of each dump file

QUERY the clause of the selected export table subset

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

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

Table space list of TABLESPACES to be transmitted

All IMP parameters (the default value of the parameter in parentheses ):

USERID username/password

FULL import of the entire file (N)

BUFFER data BUFFER size

FROMUSER User Name List

FILE input FILE (EXPDAT. DMP)

TOUSER User Name List

SHOW only lists file content (N)

TABLES Table Name List

IGNORE creation error (N)

Recordlength io record length

GRANTS import permission (Y)

INCTYPE incremental Import Type

INDEXES import index (Y)

COMMIT submits array insert (N)

ROWS import data ROWS (Y)

PARFILE parameter file name

LOG File output by LOG Screen

CONSTRAINTS import restrictions (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)

ANALYZE executes the ANALYZE Statement (Y) in the dump file)

FEEDBACK displays the progress of each x row (0)

TOID_NOVALIDATE skips the verification of the specified type id

FILESIZE maximum size of each dump file

RECALCULATE_STATISTICS recalculates the statistical value (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.

Description of incremental parameters: the increment of exp/imp is not a real increment, so it is best not to use it.

Usage:

Exp parameter_name = value or Exp parameter_name = (value1, value2 ......)

You only need to input help = y to view all the help information.

Common 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 userid = test/test file =./db_str.dmp log =./db_str.log full = y rows = n compress = y direct = y

Note: In oracle10g, the userid can be directly written into the username/password, as shown in the following code:

Exp test/test file =./db_str.dmp log =./db_str.log full = y rows = n compress = y direct = y

2. OWNER and TABLE. These two options are used to define the EXP object. The OWNER defines to export the object of the specified user; the TABLE specifies the table name of the EXP, for example:

Exp userid = test/test file =./db_str.dmp log =./db_str.log owner = duanl

Note: Sometimes we may encounter exporting a single table only. The following method can help you solve the problem:

Exp userid = test/test file =./db_str.dmp log =./db_str.log table = nc_data, fi_arap

3. BUFFER and FEEDBACK. When exporting a large amount of data, I will consider setting these two parameters. For example:

Exp userid = test/test file = yw97_2003.dmp log = yw97_2003_3.log feedback = 10000 buffer = 100000000 tables = WO4, OK _YT

4. FILE and LOG. The two parameters respectively specify the DMP name and LOG name of the backup, including the FILE name and directory. For example, see the preceding figure.

5. The COMPRESS parameter does not COMPRESS the exported data. Controls how the storage statement of the exported object is generated. The default value is Y. The default value is used. The init extent of the Object Storage statement is equal to the total extent of the current exported object. COMPRESS = N is recommended.

6. FILESIZE this option is available in 8i. If the exported dmp file is too large, use the FILESIZE parameter to limit the file size to 2 GB. For example:

Exp userid = duanl/duanl file = f1, f2, f3, f4, f5 filesize = 2G owner = scott

In this way, a series of files such as f1.dmp and f2.dmp will be created, each of which is 2 GB. If the total exported amount is less than 10 Gb

Expdoes not have to create f5.bmp.

Common IMP options

1. Use FROMUSER and TOUSER to import data from one SCHEMA to another. For example, if we export the object 'test' as the exp, we want to import the object to the user:

Imp userid = test1/test1 file = expdat. dmp fromuser = test1 touser = test1

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. Example: imp userid = test1/test1 file = expdat. dmp fromuser = test1 touser = test1 indexes = N

Tablespace Transmission