Oracle import and export, including tables, table structures, solutions, and databases

Export

Write commands directly in the command line

1. Export your own table

Exp userid = Scott/tiger @ myoral tables = (EMP, Dept) file = D: \ e1.dmp

 

2. Export tables of other schemes. If you want to export tables of other schemes, you need DBA or exp_full_database permissions. For example, system can export Scott tables.

Exp userid = system/manager @ myoral tables = (Scott. EMP) file = D: \ e2.emp

3. Export the table structure

Exp userid = Scott/tiger @ ACCP tables = (EMP) file = D: \ e3.dmp rows = N

4. Use Direct Export

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.

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

1. Export your own solution

Exp userid = Scott/tiger @ myorcl owner = Scott file = D: \ Scott. dmp

2. Export other schemes if the user wants to export other schemes, the DBA permission or the exp_full_database permission is required. For example, the System user can export any scheme.

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

Export Database

Exporting a database is to use export to export objects and data in all databases. This user is required to have DBA or exp_full_database permissions for Incremental Backup (the advantage is that after the first backup, the second backup is much faster)

Exp userid = system/manager @ myorcl full = y inctype = complete file = D: \ All. dmp

Import

1. Import your own table

IMP userid = Scott/tiger @ myorcl tables = (EMP) file = D: \ XX. dmp

2. to import a table to another user, the user must have DBA permissions.

Imp_full_database imp userid = system/tiger @ myorcl tables = (EMP) file = D: \ XX. dmp touser = Scott

3. Structure of the imported table. Only the structure of the imported table is not imported 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

The import scheme is to use the import tool to import objects and data in files to 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. Importing other solutions requires that the user have DBA permissions.

IMP userid = system/manager file = D: \ XXX. dmp fromuser = system touser = 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 = y file = D: \ XXX. dmp