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

This article will explain in detail how to use the import and export command in the oracle database. The editor thinks it is very practical, so I share it for you as a reference. I hope you can get something after reading this article.

Oracle data Import and Export imp/exp

Function: Oracle data import and export imp/exp is equivalent to oracle data restore and backup.

In most cases, you can use Oracle data import and export to complete data backup and restore (without data loss).

One advantage of Oracle is that although your computer is not a server, you have installed an oracle client and established a connection (add the correct service name through Net Configuration Assistant, in fact, you can think of it as a road between the client and the server, and then the data can be pulled over)

This way you can export the data locally, although the server may be far away from you.

You can also import dmp files locally to a remote database server.

With this feature, you can build two identical databases, one for testing and one for formal use.

Execution environment: can be executed in SQLPLUS.EXE or DOS (command line). When executed in DOS, because the installation directory\ $ora10g\ BIN in oracle 8i is set to the global path, there are EXP.EXE and IMP.EXE files in this directory that are used to perform import and export.

Oracle is written in java. I think SQLPLUS.EXE, EXP.EXE, and IMP.EXE are wrapped class files.

SQLPLUS.EXE calls the classes wrapped by EXP.EXE and IMP.EXE to complete the import and export function.

The following is the import and export of the instance, the wizard import and export to see the instance can basically be completed, because import and export is very simple.

Data export:

1 Export the database TEST completely, and export the user name system password manager to D:\ daochu.dmp

Exp system/manager@TEST file=d:\ daochu.dmp full=y

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

Exp system/manager@TEST file=d:\ daochu.dmp owner= (system,sys)

3 Export the tables table1 and table2 in the database

Exp system/manager@TEST file=d:\ daochu.dmp tables= (table1,table2)

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

Exp system/manager@TEST file=d:\ daochu.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.

Import of data

1 Import the data from D:\ daochu.dmp into the TEST database.

Imp system/manager@TEST file=d:\ daochu.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:\ daochu.dmp

Imp system/manager@TEST file=d:\ daochu.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.

Data export:

Exp hkb/hkb@boss_14 full=y file=c:\ orabackup\ hkbfull.dmp log=c:\ orabackup\ hkbfull.log

Export considerations: export is the current user's data, if the current user has the permission of DBA, then export all the data!

Data import between users with the same name:

Imp hkb/hkb@xe file=c:\ orabackup\ hkbfull.dmp log=c:\ orabackup\ hkbimp.log full=y

Data import between different names:

Imp system/test@xe fromuser=hkb touser=hkb_new file=c:\ orabackup\ hkbfull.dmplog=c:\ orabackup\ hkbimp.log; this article on "how to use import and export commands in oracle database" ends here. I hope the above content can be of some help to you, so that you can learn more knowledge. If you think the article is good, please share it for more people to see.

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.