Oracle Data Import Export IMP/EXP

Source: Internet
Author: User
Tags sqlplus

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 Data Import Export IMP/EXP

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.