Oracle logic import and export tool exp/imp

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

Exp data export method:

1. Full Database Export is generally not required)

2. Export by user

3. Export by table

Exp common parameters: exp help = y

650) this. width = 650; "title =" exp .png "src =" http://www.bkjia.com/uploads/allimg/131228/2235421939-0.png "/>

If you use the exp/imp tool, you need to enable the listener on the server. You 'd better configure the connection string on the client.

The character set of the client must be the same as the character set of the server to avoid conversion of different character sets, resulting in unavailability of exported data.

The client version must be the same as the server version. For example, if the server is 10 GB and the client is 11 GB, the following error is reported: 650) this. width = 650; "title =" 11gclient Terminal 10 gdata warehouse object error .jpg "src =" http://www.bkjia.com/uploads/allimg/131228/2235422141-1.jpg "/>

 

OK. start exporting data.

Environment: linux redhat 5.4 oracle 10g 32-bit

1. Full Database Export

Exp system/oracle @ lck full = y file = d: \ full_database.dmp

650) this. width = 650; "style =" float: none "title =" full export 1.jpg "src =" http://www.bkjia.com/uploads/allimg/131228/2235423P2-2.jpg "/>

650) this. width = 650; "style =" float: none "title =" Full Database Export 2.jpg "src =" http://www.bkjia.com/uploads/allimg/131228/2235422441-3.jpg "/>

2. Export by user

Exp system/oracle owner = lck file = d: \ lck_tables.dmp

650) this. width = 650; "title =" .jpg "src =" http://www.bkjia.com/uploads/allimg/131228/2235423Z9-4.jpg "/>

3. Export by table

650) this. width = 650; "title =" .jpg "src =" http://www.bkjia.com/uploads/allimg/131228/223542M31-5.jpg "/>

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

650) this. width = 650; "title =" only guide the structure of the table .jpg "src =" http://www.bkjia.com/uploads/allimg/131228/2235425431-6.jpg "/>

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.

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

Common imp parameters: imp help = y

650) this. width = 650; "title =" imp .png "src =" http://www.bkjia.com/uploads/allimg/131228/22354245O-7.png "/>

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; 650) this. width = 650; "title =" importing .jpg without creating a user "src =" http://www.bkjia.com/uploads/allimg/131228/2235424193-8.jpg "/>

An error is reported. The user does not exist. Okay, create an lck user on the target database.

650) this. width = 650; "title =" create lck .jpg "src =" http://www.bkjia.com/uploads/allimg/131228/223542F56-9.jpg "/>

Import another 650) this. width = 650; "title =" .png "src =" http://www.bkjia.com/uploads/allimg/131228/223542FQ-10.png "/>

An error is reported again. You do not have permission for the data tablespace, because the lck user is not assigned a quota for the data tablespace. In fact, the table structure has already been imported. Let's take a look.

650) this. width = 650; "title =" table structure imported .jpg "src =" http://www.bkjia.com/uploads/allimg/131228/2235423260-11.jpg "/>

Okay, then we will add the lck quota, and then import 650) this. width = 650; "title =" for the reason that the import has created the table, the error reported by the object has been .jpg "src =" http://www.bkjia.com/uploads/allimg/131228/223542A62-12.jpg "/>

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

650) this. width = 650; "title =" successful import .jpg "src =" http://www.bkjia.com/uploads/allimg/131228/223542K41-13.jpg "/>

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 650) this. width = 650; "title =" ..jpg "src =" http://www.bkjia.com/uploads/allimg/131228/2235426494-14.jpg "/>

OK. Import successful !!

This article is from the blog "Kick little sister", please be sure to keep this source http://chunke.blog.51cto.com/2753715/1219686