Introduction to Oracle Database Export and Import

Use the exp;impcommand to export and import racledata databases and execute them in the environment of the local or sqlplus.exe command. The exp command can export data from the remote database server to a local dmp file, and the imp command can import the dmp file from the local to a distant database server. The command line executes the Import and Export commands through the imp.exeand exp.exe programs under the binfolder of the oracleinstallation directory.

Check the path of "environment variable" and add D: oracleora92bin as the global variable (If your Oracle is installed on the D disk ).

2.1.1 four exp modes:

1. Table mode: used to export a table.

2. User Mode: used to export a user's Schema.

3. The tablespace mode is used to export the tablespace. A tablespace is composed of data files. The data files are copied from the current database to the target database. The exp tool is used to export the dictionary information of the tablespace from the current database and then import it to the target database, take two steps. There are many restrictions.

4. database mode. Used to export the entire database, not suitable for large data volumes.

 

2.1.2 export example

 

Export 1-User Mode

Exp username/password @ network service name file = d:/oralce_bak_20101001.dmp owner = username log = d:/exp. log direct = y

File: the exported *. dmp file is output to the specified directory.

Owner: the user's Schema to be exported.

Log: log file is lost to the specified directory (optional)

Direct: y indicates that direct export (optional) is more than twice faster than normal export. The default value is n.

Rows: y indicates that data is exported at the same time (Optional). The default value is y. n indicates that only the table structure is exported.

 

Export 2 -- table mode

Exp username/password @ network service name file = 20101001.dmp tables = table name 1, table name 2 rows = y log = exp. log

File: the exported *. dmp file is output to the current directory.

Tables: Specifies the name of the exported table, which can be multiple and separated by commas (,).

Rows: y indicates that data is exported at the same time (Optional). The default value is y. n indicates that only the table structure is exported.

Log: log file is lost to the current directory (optional)

 

Export 3-database mode

Exp username/password @ network service name file = 20101001.dmp full = y rows = y log = exp. log grants = y

File: the exported *. dmp file is output to the current directory.

Full: export the entire database

Rows: y indicates that data is exported at the same time (Optional). The default value is y. n indicates that only the database structure is exported.

Log: log file is lost to the current directory (optional)

Grants: y indicates export authorization (optional)

 

The following example describes the format of the Export and Import commands:

Database Export:

1. Completely export the database TEST with the username system Password manager 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 the inner_policy and policy_staff_relat tables in the database.

The Code is as follows:	Copy code
Exp aichannel/aichannel @ TESTDB2 file = d: datanewsmgnt. dmp tables = (inner_policy, policy_staff_relat)

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 '"

Database Import:

First, Use Database Configuration Assistant to create an empty database daoru for no Database and import the database TEST to the Database daoru.

The Code is as follows:	Copy code
Imp user/pwd @ daoru file = d: TEST. dmp fromuser = user touser = user buffer = 10240000

Note: if you have sufficient permissions, the system will prompt you.
Databases can be connected. You can use tnsping TEST to obtain whether the database TEST can be connected.

There is also a dmp command. Let's talk about it here.

To export a dmp File

Input: Run CMD? Exp(exp.exe under the binary directory of external oracle)

Username/password @ Database Name (for example, NCS_TEST/K @ GAICHU)

Export path (c: text. dmp)

A series of default carriage return

Exported

2. Import dmp files

Input: Run CMD? Imp( imp.exe under the binary directory of oracle)

Username/password @ Database Name (for example, NCS_TEST/K @ GAICHU)

Import path (c: text. dmp)

A series of default carriage return

Import completed