Two ways to export a table (DMP file) from Oracle

Source: Internet
Author: User

Two ways to export a table (DMP file) from Oracle

Oracle exports tables (DMP files) in two ways:

Method 1: export using PL/SQL Developer:
Menu Bar ----> Tools ----> Export Tables, for example, you can set relevant parameters:

Method 2: Use the cmd Operation Command to export data. The details are as follows (Note: method 2 is to repost the online tutorial ):
1: There is a tnsname. ora file in the directory G: \ Oracle \ product \ 10.1.0 \ Client_1 \ NETWORK \ ADMIN. The content is as follows:
CMSTAR =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP) (HOST = 172.18.13.200) (PORT = 1521 ))
)
(CONNECT_DATA =
(SERVICE_NAME = cmstar)
)
)
Here, CMSTAR is the database name and HOST is the IP address, so you can follow the example above to manually add a data record connection.
2: Run cmd to enter the command line.
Input: tnsping cmstar
Is to test whether the connection is successful 172.18.13.200
3. Import and export:

Data export:
1. Export the database TEST completely, and the username system Password manager is exported 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 tables table1 and table2 in the database
Exp system/manager @ TEST file = d: \ daochu. dmp tables = (table1, table2)
4. Export the data with the field filed1 in table 1 in the database starting with "00"
Exp system/manager @ TEST file = d: \ daochu. dmp tables = (table1) query = \ "where filed1 like '201312 '\"

The above is a commonly used export. I am not very concerned about compression. I can use winzip to compress the dmp file.
However, add compress = y to the command above.
Data Import
1. import data from D: \ daochu. dmp to the TEST database.
Imp system/manager @ TEST file = d: \ daochu. dmp
The above may be a problem, because some tables already exist, and then it will report an error, the table will not be imported.
Add ignore = y to the end.
2. Import table 1 in d: \ daochu. dmp
Imp system/manager @ TEST file = d: \ daochu. dmp tables = (table1)

Note: You must have the permission to export tables when exporting dmp data. Otherwise, you cannot export the data.

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.