Shulou(Shulou.com)05/31 Report--

What is the difference between exp and imp in oracle? aiming at this problem, this article introduces the corresponding analysis and solution in detail, hoping to help more partners who want to solve this problem to find a more simple and feasible way.

The inputs to exp and imp are both name and value pairs: exp parameter_name=value or exp parameter_name= (value1,value2,value3..)

Both exp and imp support the help=y option. The command format is: exp/imp help=y

Parameters in exp: suggestion on the meaning of default values for parameter names

Compress Y does not compress the contents of the exported data compress=N

Whether rows Y exports row data in the table / that is, if only the table structure is imported, it is set to N

If filesize 0 is set to a positive value, it sets the maximum size of the dmp file to be created for export.

Query NCMA allows you to associate where statements to the exported table. Full N is set to Y and all databases are exported. Everything that includes the users used, tablespace definitions, system permissions, and databases.

Ower NumberA allows you to export a series of schemas. Used to clone patterns or "rename" users

Tables NCMA allows you to export a series of tables

Parfile NumberA specifies the name of the file that contains the parameter_name=values parameter. Can be used to replace the specified parameter on the command line.

Consitent N specifies whether the export occurs in a read-only transaction.

Transport_tablespace N can transfer tablespace settings to specify whether exp is used to export metadata.

Tablespaces NumberA lists the tablespaces to be transferred together with transport_tablespace-- the default value meaning of the 4.imp parameter name

If show N is set to Y, the import will show what has been completed

If ignore N is set to Y, most object creation errors are ignored on import.

This parameter is useful when objects are pre-created in the database and you want to use imp just to add data to the table.

If specified, indexfile imp imports all create index commands and many other DDL statements into the specified file.

If set, this parameter specifies a series of users from the dmp file for import.

You can use this parameter to restore a single schema from a single database export

If specified, touser fromuser A will import objects from the user specified in the user parameter to

The user specified by the touser parameter. This allows users to be "cloned". Commit N specifies whether the imp is committed after each array is inserted, which is controlled by the buffer parameter. Recommended setting to N

Tis_owners NumberA is used with transporttable_tablespace to list the owner of the object in the transport tablespace--

Data export:

1. Export the database TEST completely, and export the username gdoa password 123 to D:\ TEST_BK.dmp

Exp gdoa/123@TEST file=d:\ TEST_BK.dmp full=y

Or exp gdoa/123@TEST file=d:\ TEST_BK.dmp

2 export the tables of gdoa users and sys users in the database

Exp gdoa/123@TEST file=d:\ TEST_BK.dmp owner= (gdoa,sys)

3 Export the tables table1 and table2 in the database

Exp gdoa/123@TEST file=d:\ TEST_BK.dmp tables= (table1,table2)

4 export the field filed1 in the table table1 in the database with the data starting with "00"

Exp gdoa/123@TEST file=d:\ TEST_BK.dmp tables= (table1) query=\ "where filed1 like'00%'\"

The above are commonly used exports. I don't care much about compression. I can compress the dmp file very well with winzip.

But just add compress=y after the above command.

Data Import 1 imports the data from D:\ TEST_BK.dmp into the TEST database.

Imp gdoa/123@TEST file=d:\ TEST_BK.dmp

There may be a problem above, because some table already exists, and then it reports an error and does not import the table.

Just add ignore=y at the end.

2 Import the table table1 in d:\ TEST_BK.dmp

Imp gdoa/123@TEST file=d:\ TEST_BK.dmp tables= (table1)

Basically, the above import and export is enough. In many cases, I delete the table completely and then import it.

Note:

If you have enough authority, it will prompt you if you don't have enough authority.

You can connect to the database. You can use tnsping TEST to find out whether the database TEST can be connected.

This is the answer to the question about what is the difference between exp and imp in oracle. I hope the above content can be of some help to you. If you still have a lot of doubts to be solved, you can follow the industry information channel to learn more about it.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.