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

This article shows you what import and export commands are available in the oracle database, which are concise and easy to understand, which will definitely brighten your eyes. I hope you can get something through the detailed introduction of 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).

The 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)

DOS can be executed 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.dmp

Log=c:\ orabackup\ hkbimp.log

What are the import and export commands in the oracle database? have you learned any knowledge or skills? If you want to learn more skills or enrich your knowledge reserve, you are welcome to follow the industry information channel.

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.