A detailed explanation of the use of Exp,imp in Oracle

basic syntax and examples:
1. EXP:
There are three main ways (full, 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 a 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:
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 sonic by the user to be imported into the file. You must specify the Fromuser, touser parameter so that data can be imported.
3. Table mode:
EXP sonic/sonic buffer=64000 file=c:\sonic. DMP owner=sonic tables= (SONIC)
This way the user Sonic table Sonic is imported.

There are two types of backup methods for Oracle databases. The first class is a physical backup, which implements a full database recovery, but the database must be run in the back-up mode (the business database runs in non-return mode) and requires a great deal of external storage devices such as a tape library, a second type of backup as a logical backup, a business database in this way, This method does not require that the database be run in the back-up mode, it is simple to backup, and can not require an external storage device.
　　
Database Logical Backup method
　　
The logical backup of an Oracle database is divided into three modes: Table backup, user backup, and full backup.
　　
Table mode
　　
Backs up the specified object (table) in a user mode. The business database typically uses this backup method.
　　
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 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 directly to the tape device.
　　
User mode
　　
Backs up all objects in a user mode. The business database typically uses this backup method.
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 the disk has space, it is recommended that you back up to disk and then copy to tape. If the amount of database data is small, this method can be used for backup.

　 The following is a detailed import and export instance:

　　 First, data export:

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

Exp system/[email protected]File=d:\daochu.dmp full=y

2. Export the system user in the database and the SYS user's table

Exp system/[email protected]File=d:\daochu.dmp owner= (System,sys)

3. Export the table table1 and table2 in the database

Exp system/[email protected]File=d:\daochu.dmp tables= (Table1,table2)

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

Exp system/[email protected]File=d:\daochu.dmp tables= (table1) query=\ "where filed1 like ' 0% ' \"

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 upper order.

　 second, the import of 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.

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.

The ORACLEIMP/EXP command uses

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/[email protected] file=d:\data\test.dmp tables= (table1,table2,table3).

The statement to restore the above file is:

Exp User/[email protected] 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 only the Table1 needs to be restored, the statement is:

Exp User/[email protected] file=d:\data\test.dmp tables=table1. That is, you must specify that the information be poured into either all or part of the table.

The following is a excerpt of the experience of others.

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

Oracle is written in Java, SQLPLUS. EXE, EXP. EXE, IMP. EXE these two files are likely to be packaged after the class file.

SQLPLUS. EXE calls EXP.EXE, IMP. EXE to complete the import and export function.

The following is an example of importing and exporting.

　Data export:

1 The database test is fully exported, 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 Inner_notify, Notify_staff_relat in the database

Exp Aichannel/[email protected] file= d:\data\newsmgnt.dmp tables= (Inner_notify,notify_staff_relat)

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

Exp System/[email protected] file=d:\daochu.dmp tables= (table1) query=\ "where filed1 like ' 0% ' \"

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

It can also be implemented by adding compress=y to the above command.

　　Import of data

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

Imp system/[email protected] file=d:\daochu.dmp imp aichannel/[email protected] full=y file=file= d:\data\newsmgnt.dmp Ignore=y above may be a problem, because some tables already exist, and then it is an error, the table will not be 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, you must first delete the table and then import it.

Attention:

The operator must have sufficient permissions, and it will prompt for insufficient permissions.

Database, you can connect to the. You can use tnsping test to get the database test to connect.

Appendix I:

Adding permissions to the import data to the user

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

IV, 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 the directory where the DMP file is located, imp userid=system/manager full=y file=*.dmp or imp userid= System/manager full=y File=filename.dmp

Example execution:

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 Thu February 16:50:05 2006 (c) Copyright: Oracle Corporation. All rights reserved.

Connected to: Oracle8i Enterprise Edition Release 8.1.7.0.0-production

With the partitioning option

Jserver Release 8.1.7.0.0-production

Export files created by export:v08.01.07 through a regular path

Import is completed in ZHS16GBK character set and ZHS16GBK nchar character set

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

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

Appendix II:

Oracle does not allow direct changes to the table owner, which can be achieved with export/import. First establish import9.par, then, use the command as follows: Imp Parfile=/filepath/import9.par example Import9.par content is as follows:

Fromuser=tgpms

TOUSER=TGPMS2 (Note: Users who change the owner of the table 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

A detailed explanation of the use of Exp,imp in Oracle