I. Export Tools EXP
1. It is an operational system under an executable file
Store directory / oracle_home / bin
The EXP Export Tool compresses data backup into a binary system file. You can migrate between different OS
It has three modes:
a. User mode: Export all user objects and data in the object;
b. Table Mode: Export all user tables or specified tables;
c. Overall database: Export all objects in the database.
2. Export tool EXP interactive command line mode of use
$
Exp Test / test123 @ appdb
ENTER ARRAY FETCH BUFFER SIZE: 4096> Enter
Export file: expdat.dmp>
M.DMP generates the export file name
(1) E (NTIRE DATABASE), (2) u (SERS), OR (3) T (Aables): (2) u> 3
EXPORT TABLE DATA (YES / NO): YES> Enter
Compress Extents (YES / NO): YES> Enter
Export done in zhs16gbk character set and zhs16gbk nchar character set
About to Export Specified Tables Via ConvertIal Path ...
Table (T) or Partition (T: P) to be exported: (Return to quit)> CMAMENU to be exported
. EXPORTING TABLE CMAMENU 4336 ROWS EXPORTED
Table (T) or Partition (T: P) To be exported: (Return to quit)> Table name to export N
Table (t) or partition (t: p) to be exported: (Return to quit)> Enter
Export Terminated SuccessFully without Warnings.
3. Export tool EXP non-interactive command line mode
$
Exp Scott / Tiger Tables = (EMP, DEPT) File = / Directory / Scott.dmp GRANTS = Y
Explanation: Two table EMPs in Scott users are exported to file /Directory/scott.dmp
$
Exp Scott / Tiger Tables = EMP Query = / "Where job = / 'salesman /' and SAL / <1600 /" file = / directory / scott2.dmp
Explanation: Inside the expuation of the query criterion for exporting EMP JOB = 'Salesman' and Sal <1600
(But I personally use this, or generate the record of the condition to generate a temporary table, then EXP will be convenient)
$
Exp Parfile = Username.par file = / directory1 / username_1.dmp, / directory1 / username_2.dmp filesis = 2000m log = / directory2 / username_exp.log
Parameter file username.par content
Userid = username / userpassword
Buffer = 8192000
Compress = n
GRANTS = Y
Description: Username.par is the parameter file for exporting tool EXP, and the specific parameters can be modified as needed.
FileSize Specifies the maximum number of bytes of generated binary backup files
(Can be used to solve the limitations of 2G physical files under certain OS and accelerate compression speed and convenient historical data discs, etc.). Import Tool IMP
1. It is an operating system next executable file storage directory / oracle_home / bin
The IMP Import Tool imports the binary system files formed by the EXP into the database.
It has three modes:
a. User mode: Export all user objects and data in the object;
b. Table Mode: Export all user tables or specified tables;
c. Overall database: Export all objects in the database.
Only users with IMP_FULL_DATABASE and DBA privileges can be introduced throughout the database
IMP steps:
(1) Create Table (2) Insert Data (3) Create INDEX (4) Create Triggers, Constraints
2. Example of importing tool Imp interactive command line
$
IMP
Import: Release 8.1.6.0.0 - Production on Friday December 7 17:01:08 2001
(c) Copyright 1999 Oracle Corporation. All Rights Reserved.
username:
Test
Password:
****
Connect to: Oracle8i Enterprise Edition Release 8.1.6.0.0 - 64bit Production
WITH THE PartInog Option
JServer Release 8.1.6.0.0 - Production
Import file: expdat.dmp>
/TMP/m.dmp
Enter Insert Buffer Size (Minimum 8192) 30720>
Export files created by export: v08.01.06 via a regular path
Warning: This object is exported by Test, not the current user
The import of the zhs16GBK character set and the zhs16gbk nchar character set
Only list the contents of imported files (YES / NO): NO>
Due to the object already exists, ignore the creation error (YES / NO): NO>
YES
Import Permissions (YES / NO): YES>
Import Table Data (YES / NO): YES>
Import the entire export file (YES / NO): NO>
YES
Import TEST objects into Scott
Import Table "CMAMENU" 4336 line is imported
Successfully terminated, but a warning appears.
3. Example of importing tool IMP non-interactive command line
$ IMP System / Manager fromuser = Jones Tables = (Accts)
$ IMP System / Manager fromuser = Scott Tables = (EMP, DEPT)
$ IMP SYSTEM / Manager fromuser = Scott Touser = Joe Tables = EMP
$ IMP Scott / Tiger File = EXPDAT.DMP FULL = Y
$ IMP Scott / Tiger File = / MNT1/T1.dmp Show = n Buffer = 2048000 ignore = n Commit = Y GRANTS = y full = y log = / oracle_backup / log / IMP_SCOTT.LOG
$ IMP System / Manager Parfile = params.dat
Params.dat content
File = dba.dmp show = n ignore = n grants = y fromuser = scott tables = (dept, emp) 4. Problems that import tool IMP may
(1) Database object already exists
Generally, the table, sequence, function / process, trigger, etc. under the target data should be completely deleted before importing data;
The database object already exists, the failed IMP parameters is imported,
If you use parameter ignore = y, import data content within the EXP file.
If the table has the only keyword constraint, no conditions will not be imported
If the table does not have the only keyword constraint, it will cause recording repetition.
(2) Database objects have primary key constraints
Data will import failed when the primary key constraint is not met
Solution: First import the primary table, then import the dependent table
DISABLE target import object's primary key constraint, after importing data, then enable
(3) If the permissions are not enough
If you want to import A users into B users, A users need IMP_FULL_DATABASE privileges.
(4) When introducing a large table (greater than 80m), the storage allocation failed
When default EXP, compress = Y, that is, compressing all data on a block.
When importing, if there is no continuous large data block, it will import failed.
When you export a large table of more than 80M, you can't cause this error.
(5) Different characters used in IMP and Exp
If the character set is different, the import will fail, change the UNIX environment variable or NLS_lang related information in the NT registry.
After the introduction is complete, change it.
(6) IMP and EXP version cannot be compatible
IMP can successfully import the file generated by low version Exp, cannot import the high version EXP generated file
We can use it according to the situation
$ IMP UserName / Password @ Connect_String
Description: Connect_String is in /racle_home/network/admin/tnsnames.ora
Name of the local or remote database defined
Precautions:
UNIX: / etc / hosts To define host names of local or remote database servers
Win98: WINDOWS / HOSTS and IP address correspondence
Win2000: Winnt / System32 / Drivers / etc / hosts
