Exp, expdp command practice

In the Windows platform:

View help: exp/IMP help = y allows you to view command parameters and parameter default values.

Expdp/impdp help = y is also available. EXP/IMP has been used as early as 9i. Expdp/impdp in Oracle 10 Gb usage. Oracle 10g also supports exp/IMP usage.

EXP/IMP, expdp/impdp are used for logical backup of data.

 

According to the given exp/expdp parameter description: You can import and export tables (queries), users, tablespaces, and full databases respectively.

When using these commands, specify the location of the exported log file (together with the data file), so that even if the file is exported for a long time, remember the exported format.

 

These are taken from: http://www.cnblogs.com/furenjun/archive/2008/05/20/oracleImpExp.html

 

Data Export

1. Completely export the database sampledb, and export the username System Password Manager to E:/sampledb. dmp.

Exp system/manager @ testdb file = full. dmp full = y log = full. Log

-- File: You can also write a file name: full. dmp. In this way, the exported file is under the current folder.

The exported logs are as follows:

. Exporting table space Definition
. Exporting configuration file
. Exporting User Definitions
. Exporting role...
. Exporting resource cost...
. Exporting rollback segment Definition
. Exporting database link...
. Exporting sequence number
. Exporting directory alias
. Exporting the context Name Space
. Exporting external function library name
. Export public type Synonyms
. Export private type Synonyms
. Exporting Object Type Definition
. Exporting system process objects and operations
. Exporting objects and operations in the pre-schema Process
. Exporting cluster definition...
. The system table will be exported through the regular path...
... Exporting table...

....

.. Export partition costs_q1_1998 row 57879 exported

. Exporting Synonym
. Exporting view...
. The reference integrity constraints are being exported.
. The stored procedure is being exported.
. Exporting Operator
. Exporting index type...
. Exporting bitmap, functional index, and scalable Index
. Exporting table activity later
. Exporting trigger...
Exporting the Materialized View
Exporting snapshot logs
. Exporting Job Queue...
. Exporting refresh group and sub-group
. Exporting dimension...
. Exporting objects and operations in the Post-schema Process
Exporting the user history table
. Exporting default value and system audit Option
. Exporting statistics

 

 

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

Exp system/manager @ testdb file = E:/sampledb. dmp owner = (system, sys)

3. Export the tables tablea and tableb in the database.

Exp system/manager @ testdb file = E:/sampledb. dmp tables = (tablea, tableb)

4. Export the data with the field filed1 in Table A in the database as "Wang Wu"

Exp HR/hr tables = (employees) file = query. dmp log = query. log query = 'where employee_id ">" 110'

-- Data that meets the conditions will be exported.

 

Below are some special characters for conversion

Processing in Windows:

Exp... query = 'where deptno = 10'
Exp... query = 'where deptno = ''10 '''
Exp... query = 'where deptno "<" 10'

In Solaris (C shell), how to specify the query parameter:

Exp ...... query =/"where col1/<1000 /"
Exp ...... query =/"where col1/<'20140901 '/"

To compress the DMP file, add compress = Y to the end of the preceding command.

 

Data Import

1. Import the data in the backup database file to the specified database sampledb. If sampledb already exists in this table, it will not be imported;

IMP system/manager @ test file = E:/sampledb. dmp full = y ignore = y

2. Import table 1 in D:/daochu. dmp

IMP system/manager @ test file = E:/sampledb. dmp tables = (Table1)

3. Import a complete database

IMP system/manager file = bible_db log = dible_db full = y ignore = y

4. import one or more specified tables, indexes, and other objects to which the user belongs.

IMP system/manager file = seapark log = seapark fromuser = seapark

IMP system/manager file = seapark log = seapark fromuser = (seapark, Amy, amyc, Harold)

5. Import the data of one user to another.

IMP system/manager file = Tank log = Tank fromuser = seapark touser = seapark_copy
IMP system/manager file = Tank log = Tank fromuser = (seapark, Amy) touser = (seapark1, amy1)

6. Import a table

IMP system/manager file = Tank log = Tank fromuser = seapark tables = (a, B)

7. Import from multiple files

IMP system/manager file = (paycheck_1, paycheck_2, paycheck_3, paycheck_4)
Log = paycheck, filesize = 1g full = y

8. Use the parameter file

IMP system/manager parfile = bible_tables.par

Bible_tables.par parameter file:
# Import the sample tables used for the Oracle8i Database Administrator's
Bible. fromuser = seapark touser = seapark_copy file = seapark log = seapark_import

The content of import9.par is as follows:
Fromuser = tgpms
Touser = tgpms2 (Note: You can change the table owner from fromuser to touser. Users of fromuser and touser can be different)
Rows = y
Indexes = y
Grants = y
Constraints = y
Buffer= 409600
File =/backup/ctgpc_20030623.dmp
Log =/backup/import_20030623.log

9. incremental Import

IMP system./manager inctype = rectore full = y file =
In many cases, you must first completely delete the table and then import it.

 

Performance Comparison between General exp mode, direct exp path mode, and expdp Export

Normal exp mode format: (the direct parameter in the exp command is not specified. The default value is n)

Exp Zhejiang/Zhejiang file =/data‑regular.dmp buffer = 20480000

Exp direct path mode:

Exp Zhejiang/Zhejiang file =/data‑regular.dmp buffer = 20480000 direct = y

Expdp Data Pump:

Expdp Zhejiang/Zhejiang dumpfile = zj_datapump.dp directory = d_test schemas = Zhejiang