The use of Exp,imp in Oracle _oracle

basic syntax and examples:
1, EXP:
There are three main ways (complete, user, table)
1. Complete:
EXP System/manager buffer=64000 file=c:\full. DMP full=y
If you want to perform a full export, you must have special permissions
2, User mode:
EXP sonic/sonic buffer=64000 file=c:\sonic. DMP Owner=sonic
This allows all objects that the user sonic to be exported to the file.
3, Table mode:
EXP sonic/sonic buffer=64000 file=c:\sonic. DMP owner=sonic tables= (SONIC)
This way, the user Sonic table Sonic is exported
2. IMP:
With three modes (full, user, table)
1. Complete:
IMP System/manager buffer=64000 file=c:\full. DMP full=y
2, User mode:
IMP sonic/sonic buffer=64000 file=c:\sonic. DMP Fromuser=sonic Touser=sonic
This allows all objects that the user sonic to be imported into the file. You must specify Fromuser, Touser parameters so that you can import data.
3, Table mode:
EXP sonic/sonic buffer=64000 file=c:\sonic. DMP owner=sonic tables= (SONIC)
This allows user-sonic table Sonic to be imported.

Oracle databases have two types of backup methods. The first class is a physical backup, this method realizes the complete recovery of the database, but the database must run in the return mode (the business database runs in the non-return mode), and it needs a great external storage device, such as a tape library; the second type of backup is a logical backup, and the business database uses this method, This method does not require the database to run in the return mode, not only the backup is simple, but also can not need external storage devices.
　　
Database Logical Backup method
　　
Logical backups of Oracle databases are grouped into three modes: Table backup, user backup, and full backup.
　　
Table mode
　　
Backs up the objects (tables) specified in a user mode. Business databases typically take this form of backup.
　　
If you are backing up to a local file, use the following command:
　　
Exp ICDMAIN/ICD rows=y indexes=n compress=n buffer=65536
feedback=100000 volsize=0
File=exp_icdmain_csd_yyyymmdd.dmp
Log=exp_icdmain_csd_yyyymmdd.log
Tables=icdmain.commoninformation,icdmain.serviceinfo,icdmain.dealinfo
　　
If you are backing up to a tape device directly, use the following command:
Exp ICDMAIN/ICD rows=y indexes=n compress=n buffer=65536
feedback=100000 volsize=0
File=/dev/rmt0
Log=exp_icdmain_csd_yyyymmdd.log
Tables=icdmain.commoninformation,icdmain.serviceinfo,icdmain.dealinfo
　　
Note: In the case of disk space permitting, you should back up to the local server before copying to tape. For speed reasons, try not to back up to tape devices directly.
　　
User mode
　　
Backs up all objects in a user mode. Business databases typically take this form of backup.
If you are backing up to a local file, use the following command:
Exp ICDMAIN/ICD owner=icdmain rows=y indexes=n compress=n buffer=65536
feedback=100000 volsize=0
File=exp_icdmain_yyyymmdd.dmp
Log=exp_icdmain_yyyymmdd.log
If you are backing up to a tape device directly, use the following command:
Exp ICDMAIN/ICD owner=icdmain rows=y indexes=n compress=n buffer=65536
feedback=100000 volsize=0
File=/dev/rmt0
Log=exp_icdmain_yyyymmdd.log
Note: If there is space on the disk, it is recommended that you back up to disk and then copy to tape. If the amount of database data is small, this approach can be used to back up.

　 The following is a detailed import export instance:

　　 First, data export:

1, the database test completely exported, user Name System Password Manager exported to D:\daochu.dmp

Exp System/manager@test file=d:\daochu.dmp full=y

2, the system users in the database and SYS users of the table export

Exp system/manager@test file=d:\daochu.dmp owner= (System,sys)

3, the database in the table table1, table2 Export

Exp system/manager@test file=d:\daochu.dmp tables= (table1,table2)

4, the database in the Table table1 field filed1 with "00" Data export

Exp system/manager@test file=d:\daochu.dmp tables= (table1) query=\ "where filed1 like ' 0% '"

The above is the common export, for compression I do not care, with WinZip DMP file can be very good compression.

But it's okay to add compress=y to the command behind it.

　 second, the introduction of data

1. Import the data from the 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.

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.

The ORACLEIMP/EXP command uses the

For example, export database service ABC, users user, password PSD under the Table1,table2,table3 table to the D:\data directory, the export file name is TEST.DMP, the statement is:

Exp USER/PSD@ADC file=d:\data\test.dmp tables= (table1,table2,table3).

The statement that restores the above file is:

Exp USER/PSD@ADC full=y file=d:\data\test.dmp. Note: If you restore all the tables in the backup file, you need to add full=y. If you only need to restore Table1, the statement is:

Exp USER/PSD@ADC file=d:\data\test.dmp Tables=table1. That is, you must specify the information to be poured, or all or part of the table.

The following is a excerpt from the experience of others.

Execution environment: can be executed in SQLPLUS.EXE or DOS (command line), DOS can be executed because the installation directory \ora81\bin in Oracle 8i is set to the global path, EXP.EXE and IMP.EXE files are used to perform import export in the directory.

Oracle is written in Java, Sqlplus. EXE, EXP. EXE, IMP. EXE these two files may be packaged after the class file.

Sqlplus. EXE call EXP.EXE, IMP. EXE package, complete the import and export function.

The following is an example of an import export.

Data export:

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 inner_notify, Notify_staff_relat in the database

Exp AICHANNEL/AICHANNEL@TESTDB2 file= d:\data\newsmgnt.dmp tables= (Inner_notify,notify_staff_relat)

4 Export the fields in the table table1 in the database filed1 with the data beginning with "00"

Exp system/manager@test file=d:\daochu.dmp tables= (table1) query=\ "where filed1 like ' 0% '"

The above is commonly used for export, for compression, both with WinZip to DMP file can be very good compression.

You can also implement it by adding compress=y to the command below.

Import of data

1 Import the data from D:\DAOCHU.DMP into the test database.

Imp system/manager@test file=d:\daochu.dmp imp aichannel/aichannel@hust full=y file=file= d:\data\newsmgnt.dmp ignore= There may be a problem with Y, 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 into imp system/manager@test file=d:\daochu.dmp tables= (table1)

Basically, the import export above is sufficient. In many cases, you should first delete the table completely and then import it.

Attention:

The operator must have sufficient permissions, and the permission is not sufficient for it to prompt.

Database can be connected to. You can use tnsping test to get the database test to connect.

Appendix I:

Actions to increase user access to import data

First, start sql*puls second, to System/manager landing third, create user username identified by password (if the user has been created, this step can be omitted)

Four, GRANT CREATE user,drop user,alter USER, create any VIEW, DROP any view,exp_full_database,imp_full_database, Dba,connec T,resource,create session to User name V, run-cmd-into DMP file directory, imp userid=system/manager full=y file=*.dmp or imp userid= System/manager full=y File=filename.dmp

To execute the example:

F:\work\oracle_data\backup>imp userid=test/test full=y file=inner_notify.dmp

Screen display

Import:release 8.1.7.0.0-production on Thursday, February 16:50:05 2006 (c) to Copyright Oracle Corporation. All rights reserved.

Connect to: Oracle8i Enterprise Edition Release 8.1.7.0.0-production

With the partitioning option

Jserver Release 8.1.7.0.0-production

To export a file created by export:v08.01.07 through a regular path

Import in the ZHS16GBK character set and ZHS16GBK nchar character set has been completed

The export server uses the UTF8 nchar character set (possible ncharset conversions)

. Importing Aichannel objects to Aichannel. . Importing table "Inner_notify" 4 rows are imported ready to enable constraints ... The import was successfully terminated, but a warning appears.

Appendix II:

Oracle does not allow direct changes to the owner of the table, using Export/import to do so. First establish import9.par, then use the following command: Imp parfile=/filepath/import9.par case Import9.par contents are as follows:

Fromuser=tgpms

TOUSER=TGPMS2 (Note: The user who changes the table owner from Fromuser to Touser,fromuser and Touser can be different)

Rows=y

Indexes=y

Grants=y

Constraints=y

buffer=409600

File==/backup/ctgpc_20030623.dmp Log==/backup/import_20030623.log