Quickly import and export data with the EXP/IMP command in Oracle

from:http://blog.csdn.net/wangchunyu11155/article/details/53635602

"Export with exp data":

1 full export of database test, user Name System Password Manager exported to D:\daochu.dmp
Exp system/[email protected] file=d:\daochu.dmp full=y

2 Exporting the system user in the database to the SYS user's table
Exp system/[email protected] file=d:\daochu.dmp owner= (System,sys)

3 Exporting Tables Table1, table2 in the database
Exp system/[email protected] file=d:\daochu.dmp tables= (table1,table2)

4 Export the field filed1 in the table table1 in the database with the data that begins with the 00″
Exp system/[email protected] file=d:\daochu.dmp tables= (table1) query=\ "where Filed1like '00%& Apos;\ "

The above is a common export, for compression I do not care, with WinZip to the DMP file can be very good compression. But add compress=y to the above command.

"Import with IMP data":

1 Import the data from the D:\DAOCHU.DMP into the test database.
Imp system/[email protected] file=d:\daochu.dmp

There may be a problem, because some tables already exist, and then it is an error, and the table is not imported.
Add Ignore=y to the back.

2 Import the table table1 in D:\daochu.dmp
Imp system/[email protected] file=d:\daochu.dmp tables= (table1)

Basically, the above import and export is enough. In many cases I have completely removed the table and then imported it.

Function: Oracle data import and export imp/exp is comparable with Oracle data restore and backup.
In most cases, the data can be backed up and restored with Oracle data import and export (no loss of data).

Oracle has a benefit, although your computer is not a server, but you installed the Oracle client and established a connection
(Add the correct service naming by Net8 Assistant local –> service naming
In fact, you can think of the client and the server to fix the road, and then the data can be pulled over.
This allows you to export the data locally, although the server may be far away from you.
You can also import DMP files from a local to a remote database server.
With this feature you can build two identical databases, one for testing and one for formal use.

Execution environment: can be executed in SQLPLUS.EXE or DOS (command line),
DOS can be executed due to the installation of directory \ora81\bin in Oracle 8i is set to the global path,
This directory has EXP.EXE and IMP.EXE files that are used to perform import and export.
Oracle is written in Java, I want to SQLPLUS.EXE, EXP. EXE, IMP. EXE these two files are packaged after the class file.
SQLPLUS. EXE calls EXP.EXE, IMP. EXE They wrap the class, complete the import and export function.

Attention:
You need to have enough permissions, not enough permissions, and it will prompt you.
Database, you can connect to the. You can use tnsping test to get the database test to connect.

--------------------------------------------------------------------------------------------------------------- ---------------

Detailed references are as follows:

I. EXPORT TOOL EXP

1. It is the next executable file of the operating systemStorage Directory/oracle_home/bin

The EXP export tool compresses the data backup in the database into a binary system file. Can be migrated between different OS

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

2. Export tool example of use of exp Interactive command line mode

$exp Test/[email protected]
Enter Arrayfetch buffer size:4096 > enter
Export FILE:EXPDAT.DMP > M.dmp generate the exported file name
(1) E (Ntire database), (2) U (sers), or (3) T (Ables): (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 conventional Path ...
Table (T) or Partition (T:P) to IS exported: (RETURN to quit) >cmamenu the name of the tables to export
. . Exportingtable cmamenu 4336 rows Exported
Table (T) or Partition (T:P) to being exported: (RETURN to quit) > name N for exported tables
Table (T) or Partition (T:P) to being exported: (Return to quit) > carriage return
Export terminated successfully without warnings.

3. Export tool Example of exp non-interactive command line mode

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

Description: Export two tables emp,dept from Scott users to a file/directory/scott.dmp

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

Description: Add the query Condition job= ' salesman ' and sal<1600 to export EMP in EXP

(But I seldom use it personally, or if I generate a temporary table of records that meet the conditions, then exp will be handy)

$expparfile =username.par file=/directory1/username_1.dmp,/directory1/username_2.dmpfilesize=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 parameter file, inside the specific parameters can be modified as needed

FILESIZE Specifies the maximum number of bytes of the generated binary preparation file

(can be used to solve the limitations of some OS 2G physical files and speed up the compression speed and easy to engrave historical data disc, etc.)

Two. Import Tool imp

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

The Imp Import tool imports binary system files formed by 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 tables or tables specified by the user;
C. Entire database: Exports all objects in the database.

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

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

2. Import Tool IMP Interactive command-line mode 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: * * *
Connected 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>
Export files created by export:v08.01.06 through a regular path
Warning: This object is exported by TEST, not the current user
Import is completed in ZHS16GBK character set and ZHS16GBK NCHAR character set
Lists only the contents of the imported file (yes/no):no>
Creation error (YES/NO) ignored because the object already exists:no> Yes
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 to SCOTT
. . Importing table "Cmamenu" 4336 rows are imported
The import was terminated successfully, but a warning appears.


3. Importing Tools Imp example of non-interactive command line mode

$ 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=ygrants=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.Import Tool IMP problems may occur

(1) Database object already exists
In general, you should completely delete the table, sequence, function/process, trigger, etc. of the target data before importing the data.
The database object already exists, and the import fails by the default imp parameter
If the parameter ignore=y is used, the data contents inside the exp file are imported

at this point,
If the (target) table "is the imported table" has a unique keyword constraint, the non-conditional will not be imported
If the (target) Table "Import table" does not have a constraint on a unique keyword, it causes the record to be duplicated

(2)The database object has a primary foreign KEY constraint
Data import fails when the primary foreign key constraint is not met
Workaround: Import the primary table first, and then import the dependent tables
(or) Disable the primary foreign key constraints of the target import object, and after importing the data, enable them???
(3) Insufficient authority
If you want to import a user's data into a B user, a user needs to have Imp_full_database permissions

(4) Storage allocation failure when importing large tables (greater than 80M)
The default exp, compress = Y, is to compress all the data in aDataThe block.
On import, if there is no contiguous large block of data, the import fails.
when exporting large tables above 80M, (E x P) remembers compress=n, it does not cause this error.

(5) Different character sets used by IMP and EXP
If the character set is different, the import will fail, you can change the UNIX environment variables or NT registry Nls_lang related information.
After the import is complete, change back.

(6) IMP and EXP versions are not compatible
IMP can successfully import low version exp-generated files and cannot import high-version exp-generated files
According to the situation, we can use
$ imp Username/[email protected]_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

Exp can be exported as multiple dump files

Many DBAs are unaware that export can use multiple data files to store the exported data. If a single disk volume does not have enough space, or if the database exceeds the size limit of a single file in the operating system, it is easy to split the exported data into multiple files.

Beginning with Oracle 8i, the file parameter can accommodate multiple files separated by commas. The FileSize parameter lets you specify how much data can be written to a single file before moving to the next file. If export runs out of names in the file list, it will prompt for additional file names.

For example, let's assume that all database exports require 6G of space, and that the limit for a single file size in the operating system is 2G. You want to place the exported file in the Prod directory under the/exp file system. This parameter file should contain the following content:

File=/exp/prod/exp01.dmp,/exp/prod/exp02.dmp,/exp/prod/exp03.dmp filesize=2g

In Oracle 10g, the new data Pump export (EXPDP) is used in a similar but slightly changed manner. The output file path is no longer written as it did in earlier versions, instead, the directory object is used to refer to directories in the operating system. The file parameter is replaced by the DumpFile parameter and, for ease of use, you can specify wildcards to automatically generate filenames instead of all columns.

In Oracle 10g, if you are creating a directory called Expdir that points to the/exp/prod, the parameters in the above example will look like this:

Dumpfile=expdir:exp%u.dmp filesize=2g

Filenames Exp01.dmp, Exp02.dmp, and exp03.dmp are generated automatically during export.

Quickly import and export data with the EXP/IMP command in Oracle