Use EXP/IMP for logical backup and recovery of database tables

Logical backup refers to the process of using the export tool to export the structure and data of the data object to a file, logical recovery refers to the process of using the import tool to import data objects to the database when database objects are damaged due to misoperation.

Physical backup can be performed either in the open or closed database state, but logical backup and recovery can only be performed in the open state.

Export

You can export tables, export schemes, and export databases.

Export is completed using the exp command. Common options of this command are:

Userid: Specifies the username, password, and connection string used to execute the export operation.

Tables: used to specify the table for the Export Operation

Owner: used to specify the scheme for executing the Export Operation

Full = y: Specifies the database for the export operation.

Inctype: Specifies the incremental type of the export operation.

Rows: used to specify whether to export data in the table during the export operation.

File: Specifies the exported file name.

 

 

Exp userid = scott/tiger @ myoral tables = (emp, dept) file = d: \ e1.dmp

If you want to export tables of other schemes, you need dba permission or exp_full_database permission. For example, system can export scott tables.

E: \ oracle \ ora92 \ bin> exp userid = system/manager @ myoraltables = (scott. emp) file = d: \ e2.emp

Note: When importing and exporting data, go to the bin directory of the oracle directory.

Exp userid = scott/tiger @ accp tables = (emp) file = d: \ e3.dmp rows = n

Exp userid = scott/tiger @ accp tables = (emp) file = d: \ e4.dmp direct = y

This method is faster than the conventional method by default. When the data volume is large, you can consider using this method.

In this case, the character set of the database must be exactly the same as that of the client. Otherwise, an error is reported...

 

An export scheme is to use the export tool to export all objects (tables, indexes, constraints...) and data in one or more schemes. Save it to the file.

Solution

Exp userid = scott/tiger @ myorcl owner = scott file = d: \ scott. dmp

If you want to export other schemes, you need the dba permission or the exp_full_database permission. For example, the system user can export any scheme.

Exp userid = system/manager @ myorcl owner = (system, scott) file = d: \ system. dmp

 

To export a database is to use export to export objects and data in all databases. This user must have dba or exp_full_database permissions.

Incremental Backup (the advantage is that after the first backup, the second backup will be much faster)

Exp userid = system/manager @ myorcl full = y inctype = completefile = d: \ all. dmp

 

Introduction

The import tool imports the objects and data in the file into the database, but the file to be imported must be the file exported by the export. Similar to export, the import can be divided into three methods: Import table, import solution, and import database.

Common options of imp are:

Userid: Specifies the user name, password, and connection string used to perform the import operation.

Tables: used to specify the table for the Import Operation

Formuser: used to specify the source user

Touser: used to specify the target user

File: Specifies the import file name.

Full = y: used to import the entire file

Inctype: Specifies the incremental type of the import operation.

Rows: Specifies whether to import table rows (data)

Ignore: If a table exists, only data is imported.

Import table

1. Import your own table

Imp userid = scott/tiger @ myorcl tables = (emp) file = d: \ xx. dmp

2. Import tables to other users

This user is required to have dba permissions or imp_full_database

Imp userid = system/tiger @ myorcl tables = (emp) file = d: \ xx. dmptouser = scott

3. Structure of the import table

Only import the table structure without importing data

Imp userid = scott/tiger @ myorcl tables = (emp) file = d: \ xx. dmp rows = n

4. Import Data

If an object (such as a table) already exists, you can only import table data.

Imp userid = scott/tiger @ myorcl tables = (emp) file = d: \ xx. dmp ignore = y

Import Scheme

The import scheme refers to the use of the import tool to import objects and data in files into one or more schemes. If you want to import another scheme, you must have the dba permission or imp_full_database

1. Import your own solution

Imp userid = scott/tiger file = d: \ xxx. dmp

2. Import other solutions

This user is required to have dba Permissions

Imp userid = system/manager file = d: \ xxx. dmp fromuser = systemtouser = scott

Import Database

By default, all object structures and data are imported when the database is imported. The example is as follows:

Imp userid = system/manager full = yfile = d: \ xxx. dmp