Import/Export exp/imp from Oracle
Export: The dmp file is actually select.
Import data is actually insert
Defect: full table export is required during export, which is slow, not real-time, and logical backup
1: EXP
Exp export is divided into several modes
Table mode: exports the specified table under a user.
User Mode: export all objects under a user
Database mode: export all objects in the database except sys, with the exp_full_database permission
Convertible tablespace: exports all objects in a tablespace.
Exp statement help: $ exp help = y
1.1: export the specified table under a user
$ Exp doiido/doiido file = doiido. dmp tables = (doiido, boy) log = doiido. log
1.2: export all objects of a user
$ Exp doiido/doiido @ doiido file = doiido. dmp owner = doiido log = doiido. log
1.3: export the entire database (except the data dictionary of the sys user)
SQL> grant exp_full_database to doiido;
$ Exp doiido/doiido file = doiido. dmp full = y log = doiido. log
1.4: Export tablespace
$ Exp system/doiido @ orcl file = doiido. dmp tablespaces = (doiido)
1.5: Remote export in windows
C: \ Windows \ system32> exp doiido/doiido@192.168.100.200: 1521/doiido file = d: \ doiido. dmp tables = boy log = d: \ doiido. log
Two export Methods
Traditional path: the query method using SQL statements must be cached
Direct path: data block extraction method, direct = y
2: IMP Import
1: import a table specified by a user
$ Imp doiido/doiido file = doiido. dmp tables = (doiido, boy) log = doiido. log
2. Import all the objects of user test to doiido.
$ Imp doiido/doiido file = doiido. dmp fromuser = test touser = doiido log = doiido. log
Import execution sequence
Create Table Structure
Import table data
Create related indexes
Import trigger
Enable integrity constraints for imported tables
Create all bitmaps, functions, and other indexes
If the imported table already exists at imp
Ignore = y # indicates that the object with the same name is ignored during import. The import operation is append.
3: Others
(1) The user can be changed during the import and export process, but the same tablespace must exist so that the import can succeed.
(2) wainings is normal during export, because some indexes or foreign keys are not exported.
(3) When importing and exporting data, note that the character set must be the same or a subset.
