Oracle logic import and export tool exp/imp

Introduction: expimpexpimp, a logical import and export tool in oracle, is the oldest two command line backup tools survived by oracle.

Introduction to the logical import and export tool exp/impexp/imp in oracle: exp/imp is the oldest two command line backup tools survived by oracle, small database dump, table space migration, table Extraction

Oracle logic import and export tool exp/imp

Exp/imp introduction:

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.

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, Vm, constructs DDL statements, inserts statements for creating tables and other objects, and adds data.

Exp data export method:

1. Full Database Export (this method is generally not required)

2. Export by user

3. Export by table

Exp common parameters: exp help = y

3. Export by table

4. If yes, only the table structure is exported and no data is exported.

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 and the website space is exceeded, exp needs to be exported from the beginning without resumable data transfer.

Consumes server resources and can only be used on the premise of server services

Common imp parameters: imp help = y

Import the exported data to oracle 11g 64-bit on windows.

Import data using 11g customer segments

1. Import by user

Imp system/oracle @ orcl fromuser = lck touser = lck file = d: \ lck_tables.dmp;

Import again

An error is reported again. You do not have permission for the data tablespace, because the lck user is not allocated a quota for the data tablespace. The Hong Kong space is actually imported into the table structure. Let's take a look.

Okay, then we need to add the lck quota and import it again.

Ah, an error is still reported. The object already exists. Add the ignore = y parameter (ignore the creation error) and import the object again.

OK. The import is successful. It turns out that importing data is not smooth.

Conclusion: before importing data, you must create a corresponding user in the target database and grant the user the corresponding permissions and the user's quota in the default tablespace.

2. Import by table

Exp system/oracle @ orcl talbes = tab1 fromuser = lck, test touser = lck, test file = d: \ lck_test_tables.dmp

OK. Import successful !!

This article is from the blog "kick and play". Please keep this source