Oracle import and export tool

Oracle 11g R2 explicitly states that export and import are no longer supported
Reason for elimination: Import and Export speed is slow
Alternative: Data Pump, speed increased by 5 times, but cannot be remotely imported or exported, only on the server
Data Pump tools expdp and impdp

Export Database files to binary files with the. DMP suffix using the exp Tool

. Dmp file platform independence

Before exp Export
1. Run the catexp. SQL or catalog. SQL script (only manual instance creation is required, and database instances created with dbca are not required)
2. Ensure sufficient disk space
3. Confirm the required permissions: The create session permission is required for this user, and the EXP_FULL_DATABASE role is required for other users.

Exp xxwz/xxwz @ XXWZ file = xxwz. dmp owner = xxwz log = xxwz. log

Call Method
Command Line
Interactive Mode
Parameter file Method
OEM

Command Line Method:
Export mode:
Table mode:
Exp hr/hr tables = employees, departments, % A %, emp: emp1 rows = yes file = exp1.dmp // % A % wildcard, export all tables with A; emp: emp1 exports the partition emp of the Partition Table emp.

User Mode:
Exp sys/sys owner = hr direct = y file = expdat. dmp // export hr user data in direct mode

Tablespace mode:
Exp \ 'sys/sys as sysdba \ 'transport_tablespace = y tablespaces = xxwz_data log = xxwz. log
// Export the data dictionary information of the xxwz tablespace without importing data

Full database mode
Exp sys/sys FULL = y file = d: \ xxwz. dmp grants = y rows = y // GRANTS is the export permission, rows is the export data

Interactive
Exp hr/hr

Parameter file Method
Write an example file exp.txt
File = xxwz. dmp
Log = xxwz. log
Owner = xxwz
Rows = y
Then execute the exp command
Exp xxwz/xxwz @ XXWZ parfile = d: \ exp.txt

Import/Export Character Set Conversion
Source database and exp Character Set conversion, exp Character Set and imp Character Set conversion, imp Character Set and target database character set Conversion
If this character set is set in advance, the conversion can be avoided.

View database character set information
Select * from nls_database_parameters;
The query parameters are combined into character sets: NLS_LANGUAGE.NLS_TERRITORY.NLS_CHARACTERSET
View the exp and imp character sets to view the Registry's Local Machine-software-oracle-nls_lang or cmd> chcp and then query the character set corresponding to the code.

Set the character set of exp and imp
Cmd> set NLS_LANG = American_America.AL32UTF8

View the dmp file Character Set
Its second and third bytes are character sets.
Or
Imp xxwz/xxwz @ XXWZ show = y file = d: \ xxwz. dmp // just check if you do not import

Export method: the normal and direct path modes (avoid checking and speed up doubled)
Features:
The direct mode cannot be interactive.
The client and server character set are the same (if not set, there will be warning)
The buffer parameter does not work.
Command Format: Add direct = y to the command line

Imp command
Imp sys/sys file = d: \ xxwz. dmp fromuser = xxwz touser = xxwz1 table = *;

Import Data Order
Create a table
Import Data
Create a B-Tree Index
Import trigger
Integrity constraints
Bitmap, function, domain index