Oracle Data Import Export IMP/EXP

In the Oracle installation directory, there are 2 files, EXP.EXE and IMP.EXE, which are used to perform import and export of the database respectively. So Oracle data import and export imp/exp is comparable with Oracle data restore and backup.

Oracle Data Export Exp

Exp Detailed parameters:

Userid

Username/password of the account running the Export command

Buffeer

The size of the buffer used to fetch the data rows

FILE

Export the name of the dump file

COMPRESS

Whether the export should compress a range of fragmented interleaved, which will affect the storage clause

GRANTS

Export when no authorization is exported on the database object

INDEXES Yes|no

Do you want to export the indexes on the table

ROWS

Whether the row should be exported. If it is ' N ', then only the DDL of the database object is generated in the export file

Consstraints

Whether to export table old contract conditions

Full

If set to ' Y ', then an entire database export operation will be performed

OWNER

A series of database accounts to export, and then perform user export operations for those accounts

TABLES

A list of tables to be exported; Table export operation to perform these tables

RecordLength

Dump the length of the exported file log (in bytes), usually using the default unless you want to pass the export file in two different operating systems

Inctype

Type of export operation being performed (valid values are complete (default), Cumulative and Ivcremental)

RECORD

A tag in the incremental export type that indicates whether the log is stored in a data dictionary in a log-exported table

Parfile

The name of the parameter file to pass to the export. This file will contain entry entries for all parameters

ANALYZE

Indicates whether the statistics of the moved object are written to the export dump file

Consistent

Indicates whether read-consistent replication of all exported objects is preserved

LOG

The name of the file to which the export log will be written

MLS

Indicate if you want to export the MLS designator

Mls_label

Specifies the format of the MLS designator

Oracle Data Import Imp.exe

Imp Detailed parameters:

Userid

Username/password of the account running the import command

Buffeer

The size of the buffer used to fetch the data rows

FILE

Import the name of the dump file

SHOW

Specifies whether the contents of the file are displayed, not executed

Egnore

Indicates whether the error encountered is ignored when executing the ' CREATE ' command. This parameter should be used if the object being imported already exists

Full

If set to ' Y ', then an entire database import operation will be performed

Fromuser

In the case of full=n, parameters are only possible. It is a series of database accounts whose objects should be read from the export dump file

Touser

A series of database accounts, objects that export dump file buckles will be imported into these accounts

GRANTS

No authorization to import on database objects when importing

INDEXES

Whether to import indexes on a table

ROWS

Whether rows should be imported. If it is ' N ', then execute the DDL of the database object in the import file

TABLES

Series of tables to import

RecordLength

The length of the dump import file log, in bytes, is usually used by default unless the import file is to be passed in two different operating systems

Inctype

Type of import operation being performed (valid values are complete (default), Cumulative and Ivcremental)

Parfile

The name of the parameter file to pass to the import. This file will contain entry entries for all parameters

ANALYZE

Indicates whether the statistics of the moved object are written to the import dump file

LOG

The name of the file to which the import log will be written

MLS

Indicate if you want to import MLS labels

Indexfile

Do not perform import actions, only generate indexed text

Exp , IMP Introduction to Tool usage

Exp It has three modes:

A. User mode: Export all user objects and data in the object;

B. Table mode: Export all tables or tables specified by the user;

C. Entire database: Exports all objects in the database.

Exp using the example

1. Export the entire database, including table spaces, tables, constraints, functions, users, permissions, and so on.

EXP System/[email protected] file=d:\backup.dmp full=y

1. Export two table emp,dept from Scott users to a file/directory/scott.dmp

Exp Scott/tiger tables=emp,dept file=d:\scott.dmp grants=y

1. Export data that meets the specified criteria in the EMP

Exp Scott/scott tables=emp query=\ "where job= ' salesman ' and Sal<1600\" file=d:\scott2.dmp

1. Other...

Imp There are three types of modes:

A. User mode: Export all user objects and data in the object;

B. Table mode: Export all tables or tables specified by the user;

C. Entire database: Exports all objects in the database.

IMP using the example

1. Import the data from the D:\DAOCHU.DMP into the test database.

Imp system/[email protected] file=d:\daochu.dmp full=y

There may be a problem, because some tables already exist, and then it is an error, and the table is not imported.

Add Ignore=y to the back.

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

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

1. Filter the EMP, Dept table for Scott users from D:\daochu.dmp, and then import

Imp System/manager Fromuser=scott file=d:\daochu.dmp tables= (emp,dept)

1. Filter the EMP tables for Scott users from D:\daochu.dmp and import them into the Joe user

Imp System/manager Fromuser=scott Touser=joe tables=emp

1. Other...

Import Tool Imp problems that may occur

1. Database object already exists

In general, you should completely delete the table, sequence, function/process, trigger, etc. of the target data before importing the data.

The database object already exists, and the import fails by the default imp parameter

If the parameter ignore=y is used, the data contents inside the exp file are imported

If the table has constraints for unique keywords, the condition will not be imported

If the table does not have a constraint on a unique keyword, it causes the record to repeat

1. When exporting data, if the password is a pure number prompt user name or password error

In Oracle 10g, the Exp tool uses a password that cannot be a pure number, so you need to change the password to the letter first.

1. The database object has a primary foreign KEY constraint

Data import fails when the primary foreign key constraint is not met

Workaround: Import the primary table first, and then import the dependent tables

Disable the primary foreign key constraints of the target import object, after importing the data, enable them

1. Insufficient authority

If you want to import a user's data into a B user, a user needs to have Imp_full_database permissions

1. Storage allocation fails when importing large tables (greater than 80M)

The default exp, compress = Y, is to compress all the data on a block of data.

On import, if there is no contiguous large block of data, the import fails.

When exporting large tables larger than 80M, remember that compress= N does not cause this error.

1. IMP and exp use different character sets

If the character set is different, the import will fail, you can change the UNIX environment variables or NT registry Nls_lang related information. After the import is complete, change back.

1. IMP and EXP versions cannot be compatible

IMP can successfully import low version exp-generated files and cannot import high-version exp-generated files

According to the situation, we can use

$ imp [email=username/[email protected]_string]username/[email Protected]_string[/email]

Description: Connect_string is the name of the local or remote database defined in/oracle_home/network/admin/tnsnames.ora.

Oracle Data Import Export IMP/EXP