Exp, IMP in Oracle

Basic syntax and example: 1. Exp: There are three main methods (full, user, table) 1. Full: EXP system/Manager Buffer = 64000 file = c: \ full. DMP full = y if you want to perform full export, you must have special permissions. 2. User Mode: EXP Sonic/sonic buffer = 64000 file = c: \ sonic. DMP owner = Sonic so that all the objects of the user Sonic are output to the file. 3. Table mode: EXP Sonic/sonic buffer = 64000 file = c: \ sonic. DMP owner = sonic tables = (sonic) so that the user's sonic table sonic will be exported 2. IMP: three modes (complete, 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 so that all the objects of the user's sonic are imported into the file. You must specify the fromuser and touser parameters to import data. 3. Table mode: EXP Sonic/sonic buffer = 64000 file = c: \ sonic. dmp owner = sonic tables = (sonic) so that the sonic table is imported.

Oracle databases have two backup methods. The first type is physical backup. This method enables complete database recovery, but the database must run in the recovery mode (the business database runs in the non-recovery mode ), this method requires a large number of external storage devices, such as tape libraries. The second type of backup mode is logical backup, which is used by the business database. This method does not require the database to run in the back-to-back mode, which is not only simple, in addition, external storage devices are not required. Database logical backup methods Oracle database logical backup is divided into three modes: Table backup, user backup, and full backup. Table mode: backs up the specified objects (tables) in a user mode ). Business databases usually adopt this backup method. To back up a local file, run 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 = paitables = icdmain. commoninformation, icdmain. serviceinfo, icdmain. if dealinfo is directly backed up to the tape device, run 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 = icdmai N. commoninformation, icdmain. serviceinfo, icdmain. dealinfo Note: If the disk space permits, back up the data to the local server and copy the data to the tape. For speed considerations, do not back up data directly to tape devices. In user mode, all objects in a user mode are backed up. Business databases usually adopt this backup method. To back up a local file, run 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 = volumes if you back up directly to the tape device, run 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, we recommend that you back up the disk and copy it to the tape. If the database data volume is small, backup can be performed in this way.

 

The detailed import and export instances are as follows:

I. Data export:

 

1. Export the database test completely, and the username System Password Manager is exported to D: \ daochu. dmp.

 

Exp system/manager @ test file = D: \ daochu. dmp full = y

 

2. Export the tables of system users and SYS users in the database

 

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

 

3. Export tables Table1 and Table2 in the database

 

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

 

4. Export the data with the field filed1 in table 1 in the database starting with "00"

 

Exp system/manager @ test file = D: \ daochu. dmp tables = (Table1) query = \ "where filed1 like '201312 '\"

 

The above is a commonly used export. I am not very concerned about compression. I can use WinZip to compress the DMP file.

 

However, add compress = Y to the command above.

 

Ii. Data Import

 

1. import data from D: \ daochu. dmp to the test database.

 

IMP system/manager @ test file = D: \ daochu. dmp

 

The above may be a problem, because some tables already exist, and then it will report an error, the table will not be imported.

 

Add ignore = Y to the end.

 

2. Import table 1 in D: \ daochu. dmp

 

IMP system/manager @ test file = D: \ daochu. dmp tables = (Table1)

 

The preceding import and export operations are sufficient. In many cases, I completely delete the table and then import it.

 

Note:

 

If you have sufficient permissions, the system will prompt you.

 

the database can be connected. You can use tnsping test to obtain whether the database test can be connected.