Rapid import of exported data using the Exp/imp command in Oracle ____oracle

Rapid import of Export data using the EXP/IMP command in Oracle

Export with exp data:

1 completely export database test, user Name System Password Manager exported to D:\daochu.dmp
Exp system/manager@test file=d:\daochu.dmp full=y

2 Exporting a table from the system user in the database to the SYS user
Exp system/manager@test file=d:\daochu.dmp owner= (System,sys)

3 Export the table table1, table2 in the database
Exp system/manager@test file=d:\daochu.dmp tables= (table1,table2)

4 Export the fields in the table table1 in the database filed1 with "00″ data"
Exp system/manager@test file=d:\daochu.dmp tables= (table1) query=\ "where Filed1like '00%'\"

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

"Import with IMP data":

1 Import the data from D:\DAOCHU.DMP into the test database.
Imp system/manager@test file=d:\daochu.dmp

There may be something wrong with it, because some tables already exist, and then it complains, and the table is not imported.
Just add ignore=y to the back.

2 Import the table table1 in D:\daochu.dmp
Imp system/manager@test file=d:\daochu.dmp tables= (table1)

Basically, the import export above is sufficient. There are a lot of situations where I delete the table completely and import it.

Features: Oracle data Import Export IMP/EXP is equivalent to Oracle data restore and backup.
In most cases, you can use an Oracle data import export to complete the backup and restore of your data (without causing data loss).

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 name by naming the local –> service in Net8 Assistant
In fact, you can think of the client and server side of the path, and then the data can be pulled over the
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 performed because the install directory \ora81\bin in Oracle 8i is set to the global path,
There are EXP.EXE and IMP.EXE files under this directory that are used to perform import export.
Oracle is written in Java, I would like to SQLPLUS.EXE, EXP. EXE, IMP. EXE these two files are wrapped in the class file.
Sqlplus. EXE call EXP.EXE, IMP. EXE they are wrapped in the class, completing the import export function.

Attention:
You need to have enough permissions, you don't have enough permissions, it will prompt you.
Database can be connected to. You can use tnsping test to get the database test to connect.

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

detailed reference is as follows:

A. Export tool exp

1. It is the next executable file of the operating systemStore Directory/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 Arrayfetch buffer size:4096 > Carriage
Export FILE:EXPDAT.DMP > M.dmp generate exported file names
(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 IS exported: (Return to quit) > list name to export
Table (T) or Partition (T:P) to IS exported: (Return to quit) > enter
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\ ' andsal\<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)

$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 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=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 Possible problems

(1) The database object already exists
In general, you should delete the table, sequence, function/process, triggers, etc. before the data is imported.
The database object already exists, and the default IMP parameter will import the failed
If the parameter ignore=y is used, the data content in the exp file is imported

 

at this point,
If the (target) table "imported table" has a unique keyword constraint, the condition will not be imported
If the (destination) table "imported table" does not have a unique keyword constraint, it will cause the record to repeat

(2)database object has primary FOREIGN KEY constraint
Data will fail to import if the primary foreign key constraint is not met
Workaround: Import primary table First, then import dependency table
(or) Disable the primary foreign KEY constraint of the target import object, and then enable them after importing the data???
(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) When importing large tables (greater than 80M), storage allocation fails
The default exp, compress = Y, is to compress all the data on a block of data.
When imported, failure is imported if there is no contiguous large block of data.
when you export a large table above 80M, (E x P) Remember Compress=n, this error is not caused.

(5) IMP and exp use different character sets
If the character set is different, the import fails, and you can change the UNIX environment variable or the NT registry Nls_lang related information.
When 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

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.

As early as Oracle 8i begins, the file parameter can accommodate multiple files paths 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 prompts for additional file names.

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

File=/exp/prod/exp01.dmp,/exp/prod/exp02.dmp,/exp/prod/exp03.dmp FILESIZE=2G

In Oracle 10g, the new data Pump Export (EXPDP) was used in a similar manner but slightly changed. The output file path is no longer written in the same way as in earlier versions, instead, use the directory object to refer to the directory 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 file names instead of listing them all.

In Oracle 10g, if you want to create a directory called Expdir that points to the/exp/prod directory, the parameters in the example above will become this:

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

FileName Exp01.dmp, exp02.dmp, and exp03.dmp are automatically generated during export.