Oracle Database Import and Export command statement

The following describes the Import and Export instances. The import and export operations can basically be completed. Because the Oracle Database Import and Export Office uses two Commands: exp export and imp import, the operation method is quite simple.

The oracle exp/imp command is used to export/import databases;

The exp command is used to export data from a remote database server to a local machine to generate a dmp file;

The imp command is used to import the dmp file of the local database from the local database to a remote Oracle database.

Data export:

By entering the EXP command and user name/password, you can use the command with the user name/password:

Example: exp scott/TIGER or, you can also control "Export" by entering the EXP command with various parameters"

According to different parameters. To specify parameters, you can use the Keyword:

Format: exp keyword = value or KEYWORD = (value1, value2 ,..., ValueN)

Routine: exp scott/tiger grants = y tables = (EMP, DEPT, MGR) or TABLES = (T1: P1, T1: P2), If T1 is a partitioned table

USERID must be the first parameter in the command line.

Example

1. Export the database TEST completely, and the username system Password manager is exported to D: daochu. dmp.

The Code is as follows:	Copy code
Exp system/manager @ TEST file = d: daochu. dmp full = y

2. Export the tables of system users and sys users in the database

The Code is as follows:	Copy code
Exp system/manager @ TEST file = d: daochu. dmp owner = (system, sys)

3. Export tables table1 and table2 in the database

The Code is as follows:	Copy code
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"

The Code is as follows:	Copy code
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, I can add compress = y to the command above.

Data Import

Routine: imp scott/TIGER or, you can control "import" by entering the IMP command and various parameters"

According to different parameters. To specify parameters, you can use the Keyword:

Format: imp keyword = value or KEYWORD = (value1, value2 ,..., VlaueN)

Routine: imp scott/tiger ignore = y tables = (EMP, DEPT) FULL = N

1. import data from D: daochu. dmp to the TEST database.

The Code is as follows:	Copy code
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

The Code is as follows:	Copy code
Imp system/manager @ TEST file = d: daochu. dmp tables = (table1)

 
The preceding import and export operations are sufficient. In many cases, I completely delete the table and then import it.