Oracle IMP and EXP (import and export) data tools use

Oracle provides two tools Imp.exe and exp.exe for importing and exporting data, respectively. The two tools are located in the Oracle_home/bin directory.

Import data Exp

1 full export of database atstestdb, user Name System password 123456 exported to C:\export.dmp

Exp System/[email protected] file=c:\export.dmp full=y

Where Atstestdb is the database name, system is the account in the database, 123456 is its password.

2 Export all related resources of system users and SYS users in the database (tables, stored procedures, methods, views, etc.)

Exp System/[email protected] file= c:\export.dmp owner= (System,sys)

3 Exporting Tables Sys.table1, owbsys.table2 in the database

Exp System/[email protected] file= c:\export.dmp tables= (Sys.table1, Owbsys.table2)

Note that the schema name of the table needs to be added, if not added, the default is to guide the current connected user's table, of course, you connect to the account to the corresponding table has permissions.

4 Export the field in the table table1 in the database with the title beginning with "Gangge"

Exp System/[email protected] file= c:\export.dmp tables= (table1) query=\ "where Title like ' gangge% ' \"

The slash "\" is followed by a colon to escape the character colon "", since it is followed by a conditional query statement. The query parameter can only specify one, if query is to be more than one table, so similarly, tables can only have a table, or multiple tables, and then query the conditions inside the table can be run. Otherwise you have to write several exp statements.

After exporting, it may be found that the data is larger, we can use some compression tools to compress the data two times, such as WinZip, WinRAR, 7zip and other third-party tools. Similarly, exp supports a parameter user to compress data directly: Compress = y, this parameter is added directly to the back of the command to export the data while compressing it.

Import Data Imp

We know how to extract the data, then we need to use the IMP command to load the exported data when we restore it.

1 loading c:\export.dmp data into Atstestdb

Imp System/[email protected] File=c:\export.dmp

Well, when the data is available, there may be an error. Why? There are two main reasons for this:

A. Imported objects (tables, views, methods, and so on) that are not originally part of the current connected user

B. The imported object already exists under the specified user of the database

C. The original user of the imported object is no longer in this database

All objects are imported under the specified account:

IMP System/[email protected] file=c:\export.dmp Fromuser=sys Touser=system

where fromuser=sys is the original owner of the object in the. dmp file, the touser=system is the new owner of the object being imported.

Ignore/Insert Data

IMP System/[email protected] file=c:\export.dmp ignore=y

Where Ignore=y tells Imp.exe to insert data directly into the object (and if there are other objects in the imported object, such as constraints, indexes, etc., will be created after the data is inserted).

2 loading the specified table Table1,table2

Imp system/[email protected] file=c:\export.dmp tables= (table1,table2)

3 Ignore load constraints

Sometimes when the data comes in, we do not need to put its constraints, such as some foreign key constraints, etc., can be combined with parameters Constraints=n

Imp system/[email protected] file=c:\export.dmp tables= (table1,table2) constraints=n

4 index not loaded (e.g., uniqueness index)

Imp system/[email protected] file=c:\export.dmp tables= (table1,table2) indexs=n

5 load structure only, do not load data

If as long as the structure of the table definition (constraints, triggers), then do not have the data inside, you can add parameters rows=n

Imp system/[email protected] file=c:\export.dmp tables= (table1,table2) rows=n

For the above operation of the object system is the administrator, if not the administrator, but the ordinary user, then this user must have the right to create delete objects, objects may include tables, views, methods, stored procedures, and so on common objects. Why is "possible" included? This is dependent on whether the import and export is related to objects of the relevant type.

At this point, is the command for the simple Oracle Import and export data available? I believe it's enough.

Oracle IMP and EXP (import and export) data tools use