Oracle database Import and Export command!

Oracle database Import and Export command!

Oracle Data Import Export IMP/EXP
Function: Oracle data import and export imp/exp is comparable with Oracle data restore and backup.

In most cases, the data can be backed up and restored with Oracle data import and export (no loss of data).
Oracle has a benefit, although your computer is not a server, but you installed the Oracle client and established a connection
(with the net Configuration Assistant add the correct service naming, you can actually think of the client and the server side to fix the road, then the data can be pulled over)
This allows you to export the data locally, although the server may be far away from you.
You can also import DMP files from a local 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 due to the installation of directories in Oracle 8i \ $ora 10g\bin is set to the global path,
This directory has EXP.EXE and IMP.EXE files that are used to perform import and export.
Oracle is written in Java, I want to SQLPLUS.EXE, EXP. EXE, IMP. EXE these two files are packaged after the class file.
SQLPLUS. EXE calls EXP.EXE, IMP. EXE They wrap the class, complete the import and export function.

The following is an example of importing and exporting, and seeing an instance of import and export is basically done, because importing and exporting is simple.
Data export:
1 full export of database test, user Name System Password Manager exported to D:\daochu.dmp
Exp System/[email protected] file=d:\daochu.dmp full=y
2 Exporting the system user in the database to the SYS user's table
Exp System/[email protected] file=d:\daochu.dmp owner= (System,sys)
3 Exporting Tables Table1, table2 in the database
Exp System/[email protected] file=d:\daochu.dmp tables= (table1,table2)
4 Export the field filed1 in table table1 in the database with data beginning with "00"
Exp System/[email protected] file=d:\daochu.dmp tables= (table1) query=\ "where filed1 like ' 0% ' \"

The above is a common export, for compression I do not care, with WinZip to the DMP file can be very good compression.
But add compress=y to the upper order.

Import of data
1 Import the data from the D:\DAOCHU.DMP into the test database.
Imp System/[email protected] File=d:\daochu.dmp
There may be a problem, because some tables already exist, and then it is an error, and the table is not imported.
Add Ignore=y to the back.
2 Import the table table1 in D:\daochu.dmp
Imp system/[email protected] file=d:\daochu.dmp tables= (table1)

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

Attention:
You need to have enough permissions, not enough permissions, and it will prompt you.
Database, you can connect to the. You can use tnsping test to get the database test to connect.

Data export:
Exp hkb/[email protected]_14 full=y file=c:\orabackup\hkbfull.dmp log=c:\orabackup\hkbfull.log;

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

Data import between users with the same name:
Imp hkb/[email protected] file=c:\orabackup\hkbfull.dmp log=c:\orabackup\hkbimp.log full=y

Data import between different names:
Imp system/[email protected] FROMUSER=HKB touser=hkb_new file=c:\orabackup\hkbfull.dmp

Log=c:\orabackup\hkbimp.log;

Oracle database Import and Export command!