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