[Go]-How to use Exp/imp in Oracle

Exp/imp is a two command-line tool for Oracle Backup data

1. Local database import and export

1. Export

(Operation---cmd)

EXP username/Password @ DB instance name file= Local storage path

Eg:exp Jnjp/[email protected] File=c:/jnjp.dmp

2. Import

IMP User name/password @ DB instance name file= Local database file storage path Full=y ignore=y

Eg:imp Jnjp/[email protected] file=e:\jnjp.dmp full=y ignore=y

2. Remote database import and Export

1. Export

EXP Username/password @ip address/DB instance name file= Local storage path

Eg:exp Jnjp/[email PROTECTED]/ORCL file=c:/jnjp.dmp

2. Import

IMP username/password @ip address/DB instance name file= Local database file storage path full=y

all parameters of 3.EXP (default values for parameters in parentheses)

USERID username/password such as: userid=duanl/duanl

Full export entire database (N)

Size of buffer data buffers

Owner user Name list, which user object you want to export, use Owner=username

File output files (expdat. DMP)

TABLES table Name list, specifying exported table names, such as: Tables=table1,table2

COMPRESS Importing a extent (Y)

Length of RecordLength IO record

GRANTS Export Permissions (Y)

Inctype Incremental Export Type

INDEXES Export Index (Y)

RECORD Tracking Incremental Export (Y)

Rows Export Data rows (Y)

Parfile parameter file name, if you exp parameters a lot, can be saved as parameter files.

CONSTRAINTS export Constraints (Y)

Consistent cross-table consistency

Log file on screen output

STATISTICS Analysis Object (ESTIMATE)

Direct Path (N)

TRIGGERS Export Trigger (Y)

FEEDBACK shows the progress of each x line (0)

FILESIZE maximum size of each dump file

QUERY selects the clause that exports a subset of tables

The following keywords are only available for transportable tablespaces

Transport_tablespace exporting the Transportable tablespace metadata (N)

Tablespaces List of tablespaces to be transferred

all parameters of 4.IMP (default values for parameters in parentheses)

UserID username/Password

Full import of entire file (N)

Buffer size of data buffers

Fromuser Owner User Name list

File input files (expdat. DMP)

Touser List of user names

SHOW only lists file contents (N)

List of TABLES table names

IGNORE Ignore Create error (N)

Length of RecordLength IO record

GRANTS Import Permissions (Y)

Inctype Incremental Import Type

INDEXES Import Index (Y)

Commit commit array Insert (N)

Rows Import data rows (Y)

Parfile parameter file name

Log file on screen output

CONSTRAINTS Import Limit (Y)

DESTROY Overlay tablespace data file (N)

Indexfile Writing table/index information to the specified file

Skip_unusable_indexes Skip maintenance of indexes that are not available (N)

ANALYZE executing the ANALYZE statement in the dump file (Y)

FEEDBACK shows the progress of each x line (0)

Toid_novalidate skips a checksum of the specified type ID

FILESIZE maximum size of each dump file

Recalculate_statistics recalculation of statistical values (N)

The following keywords are only available for transportable tablespaces

Transport_tablespace import of transportable tablespace metadata (N)

Tablespaces the tablespace that will be transferred to the database

Datafiles data files that will be transferred to the database

Tts_owners has users who can transmit data in a table-space set

5.EXP Common Options

1.FULL

This is used to export the entire database, and when used in conjunction with ROWS=N, 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, UserID This can be written directly without writing the username/password can be like this:

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 an exp object. The owner defines the object that exports the specified user; table specifies the name of the exp table, for example:

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

Note: Sometimes we may encounter only the case of exporting a single table, 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.file and log

These two parameters specify the DMP name and the log name of the backup, including the file name and directory, as shown in the example above.

4.COMPRESS

The COMPRESS parameter does not compress the contents of the exported data. The storage statement that controls how the exported object is generated. The default value is Y, which uses the default value, and the init extent of the object's storage statement equals the sum of the extent of the currently exported object. It is recommended to use Compress=n.

6.IMP Common Options

1, Fromuser and Touser

Use them to implement importing data from one schema into another schema. For example: Let's say we do exp when we export the object as test, and now we want to import the object into the user:

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

2,IGNORE, grants and indexes

Where the Ignore parameter will ignore the existence of the table, continue to import, this is useful for the need to adjust the table storage parameters, we can first based on the actual situation with reasonable storage parameters to build a table, and then directly import data. While grants and indexes indicate whether to import authorizations and indexes, if you want to rebuild the index with new storage parameters, or to speed up to the speed, we can consider setting indexes to n, and grants is generally y. For example: Imp userid=test1/test1 file=expdat.dmp fromuser=test1 touser=test1 indexes=n

7. Import and Export Instances

The following is an example of importing and exporting, and seeing an instance of import and export is basically done, because importing and exporting is simple.

1. Data export:

1.1 Full export of database test, user Name System Password Manager exported to D:\daochu.dmp

Exp System/[email protected] file=d:\daochu.dmp full=y

1.2 Exporting the system user in the database to the SYS user's table

Exp System/[email protected] file=d:\daochu.dmp owner= (System,sys)

1.3 Exporting Tables Table1, table2 in the database

Exp System/[email protected] file=d:\daochu.dmp tables= (table1,table2)

1.4 Export the field filed1 in table table1 in the database with data beginning with "00"

Exp System/[email protected] file=d:\daochu.dmp tables= (table1) query=\ "where filed1 like ' 0% ' \"

2. Import of data:

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

Imp System/[email protected] File=d:\daochu.dmp

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.2 Import the table table1 in D:\daochu.dmp

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

Basically, the above import and export is enough. In many cases I have completely removed the table and then imported it.

3. Use of the export and import wildcard characters:

Want to export the format of a table such as table_1,table_2,table_3, such a table in the library a total of 300, do you want a single write statement? It's too much to worry about. Oracle provides wildcards that can be used to meet such requirements:

Exp System/[email PROTECTED]/ORCL file=d:\exp_test.dmptables=table_% log=exp_test.log

Reprint Source:http://blog.csdn.net/qq877507054/article/details/52818609

[Go]-How to use Exp/imp in Oracle