Oracle Data Import and Export

Oracle Data Import Export Imp/exp

EXP is available under the Oracle installation directory . EXE with IMP. EXE for these 2 files, they were used to perform the import and export of the database. So Oracle data import and export imp/exp is comparable with Oracle data restore and backup.

I. Oracle DATA EXPORT EXP

1,the EXP parameter detailed ( the red font represents the more important parameter ):

(1) USERID

Username/password of the account running the Export command

(2) Buffeer

The size of the buffer used to fetch the data rows

(3) FILE

Export the name of the dump file

(4) COMPRESS

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

(5) GRANTS

Export when no authorization is exported on the database object

(6) INDEXES Yes|no

Do you want to export the indexes on the table

(7) 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

(8) Consstraints

Whether to export table old contract conditions

(9) Full

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

(Ten) OWNER

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

(one) 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

Second, Oracle Data Import Imp

1,imp parameter detailed ( red font represents more important parameters ) :

(1) Userid

Username/password of the account running the import command

(2) Buffeer

The size of the buffer used to fetch the data rows

(3) FILE

Import the name of the dump file

(4) SHOW

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

(5) IGNORE

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

(6) Full

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

(7) 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

(8) Touser

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

(9) GRANTS

No authorization to import on database objects when importing

(10) INDEXES

Whether to import indexes on a table

(11) ROWS

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

(12) TABLES

Series of tables to import

(13) 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

(14) Inctype

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

(15) Parfile

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

(16) ANALYZE

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

(17) LOG

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

(18) MLS

Indicate if you want to import MLS labels

(19) Indexfile

Do not perform import actions, only generate indexed text

Third, Exp , IMP Introduction to Tool usage

1.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.

2.Exp using the example

1. Export the entire database, including table spaces, tables, constraints, functions, users, permissions, and so on.
2. EXP System/[email protected] file=d:\backup.dmp full=y
3. Export two table emp,dept from Scott users to a file/directory/scott.dmp
4. Exp Scott/tiger tables=emp,dept file=d:\scott.dmp grants=y
5. Export data that meets the specified criteria in the EMP
6. Exp Scott/scott tables=emp query=\ "where job= ' salesman ' and Sal<1600\" file=d:\scott2.dmp

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

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

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

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

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

(4). Filter the EMP table of the Scott user from D:\daochu.dmp and import it into the Joe user

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

5. 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

(2). database objects have 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

(3). Insufficient authority

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

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

(5). 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.

(6). IMP and EXP versions are not 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 and Export