Common Oracle EXP and IMP sorting

Oracle data export and import are often used in work, especially in database backup.

Exp and imp usage collection network, or that sentence, "Come out and mix, always have to pay back ".

I. Definition

The oracle exp/imp command is used to export/import databases;
The exp command is used to export data from a remote database server to a local machine to generate a dmp file;
The imp command is used to import the dmp file of the local database from the local database to a remote Oracle database.

Ii. Syntax format

Format:

Export

> Exp userid/password @ instance file = 'path' log = 'Log. log' Parameter

Import

> Imp userid/password @ instance file = 'path' log = 'Log. log' Parameter

Run the CMd command line command to enter imp help = y or exp help = y to obtain the syntax parameter information of imp or exp:

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)
The progress of FEEDBACK is displayed on every x rows (0)
TOID_NOVALIDATE skips the verification of the specified type ID
FILESIZE: maximum size of each dump.
STATISTICS always imports pre-calculation STATISTICS
When the RESUMABLE encounters a space-related error, it suspends (N)
RESUMABLE_NAME is a text string used to identify recoverable statements.
The waiting time of RESUMABLE_TIMEOUT RESUMABLE.
COMPILE compilation process, package and function (Y)

1) Data export:

1. Completely export the database SampleDB, and export the username system Password manager to E:/SampleDB. dmp.

Exp system/manager @ TestDB file = E:/sampleDB. dmp log = E:/sample. log full = y

2. Export the tables of system users and sys users in the database

Exp system/manager @ TestDB file = E:/sampleDB. dmp log = E:/sample. log owner = (system, sys)

3. Export the tables TableA and TableB in the database.

Exp system/manager @ TestDB file = E:/sampleDB. dmp log = E:/sample. log tables = (TableA, TableB)

4. Export the data with the field filed1 in Table A in the database as "Wang Wu"

Exp system/manager @ TestDB file = E:/sampleDB. dmp log = E:/sample. log tables = (tableA) query = 'where filed1 = 'wang 5'

To compress the dmp file, add compress = y to the end of the preceding command.

If you want to export only the structure of the database object, the parameter is obtained by adding rows = no. All objects are full = y, and various errors are ignored. ignore = y

2) Data Import

1. Import the data in the backup database file to the specified database SampleDB. If SampleDB already exists in this table, it will not be imported;

Imp system/manager @ TEST file = E:/sampleDB. dmp log = E:/sample. log full = y ignore = y
 

2. Import table 1 in d:/daochu. dmp

Imp system/manager @ TEST file = E:/sampleDB. dmp log = E:/sample. log tables = (table1)

3. Import a complete database

Imp system/manager file = bible_db log = dible_db full = y ignore = y

4. import one or more specified tables, indexes, and other objects to which the user belongs.

Imp system/manager file = E:/sampleDB. dmp log = E:/sample. log fromuser = seapark
Imp system/manager file = E:/sampleDB. dmp log = E:/sample. log fromuser = (seapark, amy, amyc, harold)

5. Import the data of one user to another.

Imp system/manager file = E:/sampleDB. dmp log = E:/sample. log fromuser = seapark touser = seapark_copy
Imp system/manager file = E:/sampleDB. dmp log = E:/sample. log fromuser = (seapark, amy)
Touser = (seapark1, amy1)

6. Import a table

Imp system/manager file = E:/sampleDB. dmp log = E:/sample. log fromuser = seapark TABLES = (a, B)

7. Import from multiple files

Imp system/manager file = (paycheck_1, paycheck_2, paycheck_3, paycheck_4)
Log = paycheck, filesize = 1G full = y

8. Use the parameter file

Imp system/manager parfile = bible_tables.par
Bible_tables.par parameter file:
# Import the sample tables used for the Oracle8i Database Administrator's
Bible. fromuser = seapark touser = seapark_copy file = seapark log = seapark_import

9. incremental Import

Imp system./manager inctype = rectore full = y file =