Use of the Exp/imp Export Import Tool

More than 2004-02 Maple

A. Export tool exp

1. It is the next executable file storage directory for the operating system/oracle_home/bin

The EXP export tool compresses data backups in a database into a binary system file. Can migrate between different OS

It has three different modes:
A. User mode: Export all user objects and data in the object;
B. Table mode: Export all the user tables or specified tables;
C. Entire database: Export all objects in the database.

2. Export tool exp Interactive command line usage examples

$exp test/test123@appdb
Enter array fetch buffer size:4096 > return
Export file:expdat.dmp > m.dmp  Build Export FileName
(1) E (Ntire database), (2) U (sers), or (3) T (Ables): (2) U > 3
Export table data (yes/no): yes > Return
COMPR ESS extents (yes/no): yes > Carriage return
Export done in ZHS16GBK character set and ZHS16GBK NCHAR set
about to ex Port specified tables via conventional Path ...
Table (T) or Partition (T:P) to IS exported: (Return to quit) > cmamenu        &N BSP; table name to export
.. exporting table                         cmamenu       4336 Rows exported
Table (t) or Partition (T:P) to IS exported: (Return to quit) > table name to export n
Table (T) or Partition (T:P) to Be exported: (returns to quit) > carriage return
Export terminated successfully without warnings.

3. Export tool exp Non-interactive command line example

$exp Scott/tiger tables= (emp,dept) file=/directory/scott.dmp grants=y

Description: Export two table emp,dept from Scott user to file/directory/scott.dmp

$exp Scott/tiger tables=emp query=/"where job=/' salesman/' and sal/<1600/" file=/directory/scott2.dmp

Description: Add the query condition of the EMP in exp inside job= ' salesman ' and sal<1600

(but I personally rarely use this, or after the conditions of the record to generate temporary tables, then exp will be more convenient)

$exp parfile=username.par file=/directory1/username_1.dmp,/directory1/username_2.dmp Filesize=2000M log=/ Directory2/username_exp.log

Parameter file Username.par content
Userid=username/userpassword
buffer=8192000
Compress=n
Grants=y

Description: Username.par for export tool exp used in the parameter file, the specific parameters can be modified according to the need

FILESIZE Specifies the maximum number of bytes generated for binary prepared files

(can be used to solve the limitations of 2G physical files under some OS and to speed up compression and facilitate engraving of historical data CDs, etc.)

Two. Import Tools Imp

1. It is the next executable file storage directory for the operating system/oracle_home/bin

The Imp Import tool imports the binary system files that are formed by exp into the database.

It has three different modes:
A. User mode: Export all user objects and data in the object;
B. Table mode: Export all the user tables or specified tables;
C. Entire database: Export all objects in the database.

Only users with Imp_full_database and DBA authority can do the entire database import

Imp Step:
(1) Create TABLE (2) Insert data (3) create INDEX (4) Create triggers,constraints

2. Import Tools Imp Interactive command line example
$ 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.
User name: Test
Password: * * *
Connect to: Oracle8i Enterprise Edition release 8.1.6.0.0-64bit Production
With the partitioning option
Jserver Release 8.1.6.0.0-production
Import file: expdat.dmp>/tmp/m.dmp
Enter the insertion buffer size (min. 8192) 30720>
To export a file created by export:v08.01.06 through a regular path
Warning: This object is exported by TEST instead of the current user
Import in the ZHS16GBK character set and ZHS16GBK NCHAR character set has been completed
List only the contents of the import file (yes/no):no>
Ignore creation error (yes/no):no> Yes because the object already exists
Import permissions (yes/no):yes>
Import table Data (yes/no):yes>
Import the entire export file (yes/no):no> Yes
. Importing the object of test into SCOTT
. . Importing table "Cmamenu" 4336 rows are imported
The import was successfully terminated, but a warning appears.

3. Import Tools imp non-interactive command line example

$ 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_s Cott.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. Possible problems with importing tools imp

(1) A database object already exists
  Generally, you should delete the table, sequence, function/procedure, triggers, etc.;  &NBSP under the target data before importing data;
  Database object already exists, by default imp parameter, The import fails
  If the parameter ignore=y is used, the data content in the exp file is imported
   if the table has a unique keyword constraint, the condition will not be imported
   If the table does not have a unique keyword constraint, it will cause the record to repeat
  
(2) The database object has a primary foreign KEY constraint
      does not conform to a primary foreign KEY constraint, data import fails &NBSP
      Workaround: Import the primary table first, import the dependency table
  disable The primary foreign KEY constraint of the target import object, and then enable them after the data is imported
(3)   Insufficient permissions
  If you want to import a user's data into B users, a user needs to have Imp_full_database permissions
 
(4)   Import large table (greater than 80M), Storage Allocation failure
      default exp, compress = Y, which compresses all data on a block of data.
      Import fails if there is no contiguous large block of data.
      When you export a large table above 80M, remember compress= N, this error is not caused.
 
(5) IMP and exp use different sets of characters
      if the character set is different, the import will fail, you can change the UNIX environment variable or the NT Registry Nls_ Lang related information.
      After the import is complete, change it back.

(6) IMP and EXP versions are not compatible
IMP can successfully import the files generated by the low version exp and cannot import the files generated by the high version exp
According to the situation, we can use
$ imp username/password@connect_string
Description: Connect_string is in/oracle_home/network/admin/tnsnames.ora
Name of the local or remote database defined
Precautions:
UNIX:/etc/hosts to define the host name of a local or remote database server
The correspondence between Win98:windows/hosts and IP address

Win2000:winnt/system32/drivers/etc/hosts

Interested can look at SQL Server export Import data method