Oh, turn. ! ! > Exp Help = Y is very important! ! I. Export Tools EXP
1. It is an operating system next executable file storage 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 objects of the user and the data in the object; b. Table mode: Export all the tables or specified tables; c. Overall database: Export all objects in the database.
2. Export tool EXP interactive command line mode of use
$ EXPDBENTER ARRAY FETCH BUFFER SIZE: 4096> Enter Export File: Expdat.dmp> M.dmp Generates Exported File Name (1) E (NTIRE Database), (2) U (SERS), OR 3) T (Aables): (2) U> 3export table data (yes / no): YES> Enter compress extents (yes / no): YES> Enter Export Done in zhs16gbk character set and zhs16gbk nchar character setabout to export Specified Tables Via Convenctional Path ... Table (T) or Partition (T) to be exported: (Return to quit)> CMAMENU To export the table name. EXPORTING TABLE CMAMENU 4336 ROWS ExportTable (T) or Partition (T) To Be exported: (Return to quit)> Table name to be exported NTABLE (T) or partition (t) to be exported: 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 / userpasswordbuffer = 8192000compress = ngrants = 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 some OS 2G physical files and accelerate compression speed and convenient historical data discs, etc.)
II. 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 objects of the user and the data in the object; b. Table mode: Export all the 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 import tool IMP interactive command line Way $ IMPIMPORT: Release 8.1.6.0.0 - Production On Friday December 7 17:01:08 2001 (C) Copyright 1999 Oracle Corporation. All Rights Reserved. User Name: Test Password: **** Connect to: Oracle8i Enterprise Edition Release 8.1.6.0.0 - 64bit ProductionWith The Partitioning OptionjServer Release 8.1.6.0.0 - Production Import File: Expdat.dmp> /TMP/m.dmp Enter Insert Buffer Size (Minter of 8192) 30720> Files created by Export: V08.01.06 via a conventional path WARNING: This object is exported by Test, not the current user has completed the import of import files in the zhs16GBK character set and the zhs16GBK nchar character set. Content (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's object to Scott.. Import Table "CMAMENU" 4336 row is imported to successfully terminate import, but a warning.
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. Import tool IMP may have problems
(1) The database object already exists, and the table, sequence, function / process, trigger, etc. under target data should be completely deleted before importing data; the database object already exists, and if the default Imp parameter is imported, if used The parameter ignore = y will import the data content within the EXP file. If the table has a unique keyword constraint, the non-conditionation will not be imported if the table does not have a unique keyword constraint, will cause record repetition (2) database object When the primary key constraint does not meet the primary key constraint, the data will import the failure solution: first import the primary table, then import the primary key constraints of the object import object, import the data, then enable them (3) permission Not enough If you want to import A users into B users, A users need IMP_FULL_DATABASE permissions.
(4) When introducing a large table (greater than 80m), store the assignment failure default EXP, compress = Y, that is, compress all data on a block. When importing, if there is no continuous large data block, The import failed. When you export a large table of more than 80M, you don't cause this error.
(5) If the character set used by IMP and Exp is different, the import will fail, change the UNIX environment variable or NLS_LANG in the NT registry. After the import is completed, change it.
(6) IMP and EXP version cannot be compatible with IMP to successfully import the low version EXP generated files, can not import high versions of EXP generated files Based on the case, we can use $ IMP username / password @ connect_string description: Connect_String is in / oracle_home / NetWork / Admin / TNSNames.ora Name Name Name Note: UNIX: / etc / hosts To define the host name of the local or remote database server WIN98: WindowsHosts and IP addresses
Win2000: WinntSystem32DriverseetChosts
