Oracle import and export tool exp/imp

1. Introduction to exp/imp

Exp/imp is the oldest two command line backup tools survived by oracle. It is widely used in small database dump, tablespace migration, table extraction, detection logic, and physical conflicts, we can use it as a logical backup after physical backup of a small database. It can be cross-platform and cross-version.

Ii. Working Principle of exp/imp:

Exp user processes connect to the database through server processes, enable the shadow process, execute the select statement to query data in the database, use the buffer cache and use the SQL statement processing layer to transfer the exported exp file, that is, the exp process needs to occupy the SGA and PGA resources on the server.

Imp reads the. dmp file exported by exp, constructs DDL statements, inserts statements for creating tables and other objects, and adds data.

Iii. Export data using exp

Exp data export Method

Full Database Export by user export by Table 1. View exp parameter [oracle @ honey lost + found] $ exp help = y

2. Full Database Export

[Oracle @ honey lost + found] $ exp system/systempassword @ orcl full = y file =/home/lost + found/full. dmp

3. Export by user

[Oracle @ honey lost + found] $ exp system/systempassword @ orcl owner = olap file =/home/lost + found/olap. dmp

4. Export by table

[Oracle @ honey lost + found] $ exp system/systempassword @ orcl tables = olap. D_TF_DATA_M4, olap. TB_TEST_1 file =/home/lost + found/olap_table_schem.dmp

5. Only export the table structure and not export data

[Oracle @ honey lost + found] $ exp system/systempassword @ orcl owner = olap rows = n file =/home/lost + found/olap_tables_nodata.dmp

6. disadvantages of exp Tool

The speed is slow because exp needs to select the data to be exported before being transmitted to exp through SGA and PGA. If the connection is disconnected, exp needs to be exported from the beginning without resumable data transfer. It consumes server resources and can only be used on the premise of server services. Iv. imp import data 1. View imp parameters [oracle @ honey lost + found] $ imp help = y

2. Import [oracle @ honey lost + found] $ imp system/systempassword @ orcl001 fromuser = olap touser = user001 ignore = y file =/home/lost + found/olap by user. dmp
Note: Check whether the oracle user user001, touser = user001, exists. In short, you need to create a corresponding user in the target database before importing data, and grant the user the corresponding permissions and the user's quota in the default tablespace. 3. Import by table: [oracle @ honey lost + found] $ imp system/systempassword @ orcl001 tables = tab1 fromuser = olap, user001 touser = user001, user001 ignore = y file =/home/lost + found/olap1.dmp