Oracle learning-dmp file (table) Import and Export

Oracle learning-dmp file (table) Import and Export
Preface

We still use a lot of dmp files, which are used as the file format for oracle Import and Export tables. Today we will learn how to export and import dmp files.

Dmp File Export

There are generally three types of dmp File Export: export all data in the entire database instance, export all tables of the specified user, and export the specified table.

My database is used as an example. My Oracle database instance is "ORACLE". You can view your database instance in "task manager --> service", as shown in figure

Open the command line:

1. Completely export the database ORACLE, and export the username system Password manager to c: \ daochu. dmp.

Exp system/manager @ ORACLE file = c: \ daochu. dmp full = y

2. Export the RFD user and JYZGCX user tables in the database.

Exp system/manager @ ORACLE file = d: \ daochu. dmpowner = (RFD, JYZGCX)

3. Export the T_USER and T_ROLE tables in the database.

ExpJYZGCX/JYZGCX @ ORACLEfile = d: \ data \ newsmgnt. dmp tables = (T_USER, T_ROLE)

The above system is the user name, manager is the password, and ORACLE is the database instance name. In fact, it is not necessarily a system user, as long as it is a user with administrator permissions.

Dmp File Import

1. Open start --> Enter cmd-> sqlplus/nolog;

2. Enter the conn/as sysdba Administrator Account to log on;

3. Create a tablespace. If you know that the tablespace of the database to be imported is directly created, open the dmp file in txt, use the shortcut key ctrl + F to search for tablespace. Then you can find the tablespace of the database corresponding to the dmp file, and then create the tablespace we found;

Step. Create a user and assign permissions to the tablespace that is not created;

5. Open start --> Enter cmd (Note: The cmd window is not the sqlplus window !!!), Enter the following statement: "imp kang/123456 @ localhost/ORACLEfile =" C: \ daochu. dmp "full = y ignore = y"

The preceding statements are described as follows:

Kang is the username of the created login database;

123456 is the password used to log on to the database;

Localhost: indicates that the address of the current database is local. If it is a remote import, replace it with the corresponding IP address.

ORACLE: Instance name)

File: the path where your current dmp file is stored

Full = y, which seems to be all

Ignore = y, ignore some errors

Note: For basic concepts and statements for creating database instances, tablespaces, and users, refer to the previous article "Oracle learning-literacy"

Summary

This article describes how to export and import oracle data. You can import a dmp file even if you do not know the tablespace, user name, and password. Oracle is a long way to learn!