列出前後版本語法差異(粗體字是之前常用指令)。

Original Export Parameter

Comparable Data Pump Export Parameter

BUFFER

A parameter comparable to BUFFER is not needed.

COMPRESS

A parameter comparable to COMPRESS is not needed.

CONSISTENT

A parameter comparable to CONSISTENT is not needed. Use FLASHBACK_SCN and FLASHBACK_TIME for this functionality.

CONSTRAINTS

EXCLUDE=CONSTRAINT

DIRECT

A parameter comparable to DIRECT is not needed. Data Pump Export automatically chooses the best method (direct path mode or external tables mode).

FEEDBACK

STATUS

FILE

DUMPFILE

FILESIZE

FILESIZE

FLASHBACK_SCN

FLASHBACK_SCN

FLASHBACK_TIME

FLASHBACK_TIME

FULL

FULL

GRANTS

EXCLUDE=GRANT

HELP

HELP

INDEXES

EXCLUDE=INDEX

LOG

LOGFILE

OBJECT_CONSISTENT

A parameter comparable to OBJECT_CONSISTENT is not needed.

OWNER

SCHEMAS

PARFILE

PARFILE

QUERY

QUERY

RECORDLENGTH

A parameter comparable to RECORDLENGTH is not needed because sizing is done automatically.

RESUMABLE

A parameter comparable to RESUMABLE is not needed. This functionality is automatically provided for privileged users.

RESUMABLE_NAME

A parameter comparable to RESUMABLE_NAME is not needed. This functionality is automatically provided for privileged users.

RESUMABLE_TIMEOUT

A parameter comparable to RESUMABLE_TIMEOUT is not needed. This functionality is automatically provided for privileged users.

ROWS=N

CONTENT=METADATA_ONLY

ROWS=Y

CONTENT=ALL

STATISTICS

A parameter comparable to STATISTICS is not needed. Statistics are always saved for tables.

TABLES

TABLES

TABLESPACES

TABLESPACES (Same parameter; slightly different behavior)

TRANSPORT_TABLESPACE

TRANSPORT_TABLESPACES (Same parameter; slightly different behavior)

TRIGGERS

EXCLUDE=TRIGGER

TTS_FULL_CHECK

TRANSPORT_FULL_CHECK

USERID

A parameter comparable to USERID is not needed. This information is supplied as the username/password when you invoke Export.

VOLSIZE

A parameter comparable to VOLSIZE is not needed.

 

Examples of Using Data Pump Export

Example 2-1 Performing a Table-Mode Export

expdp hr/hr TABLES=employees,jobs DUMPFILE=dpump_dir1:table.dmp NOLOGFILE=y

 

Example 2-2 Data-Only Unload of Selected Tables and Rows
這段裡面蠻多技巧,是蠻值得學習的、1.parfile,2.CONTENT,2.EXCLUDE,3.QUERY。
首先在C:\TEMP\建立一PARFILE,named c:\temp\exp.par
參數說明:
1.CONTENT=DATA_ONLY     ==>表示只轉出資料,不含DDL SCRIPT

2.EXCLUDE=TABLE:"IN ('COUNTRIES', 'REGIONS')"    ==>表示排除'COUNTRIES', 'REGIONS' 這兩個TABLE

3.QUERY=employees:"WHERE department_id !=50 ORDER BY employee_id"   ==>表示遇到employees Table時 依where條件轉出資料
-----------------------------------------------------

DIRECTORY=dpump_dir1
DUMPFILE=dataonly.dmp
CONTENT=DATA_ONLY
EXCLUDE=TABLE:"IN ('COUNTRIES', 'REGIONS')"
QUERY=employees:"WHERE department_id !=50 ORDER BY employee_id"
---------------------------------------------------------------

執行指令如下:

> expdp hr/hr PARFILE=c:\temp\exp.par
這裡其實有個陷阱,就是你會發現我沒有下FULL=Y,但怎麼幾乎所有的Table都匯出來,
不過設定但EXCLUDE的TABLE是有排除,且employees TABLE只轉出WHERE條件。
 
DIRECTORY=dpump_dir1 這個指令到底dpump_dir1是在哪個目錄呢?
請下select * from ALL_DIRECTORIES; 就可以知道
 

Example 2-3 Estimating Disk Space Needed in a Table-Mode Export
若您需要了解您下的匯出佔用SIZE顯示在畫面上,但不轉出,下面是很棒的例子。

> expdp hr/hr DIRECTORY=dpump_dir1 ESTIMATE_ONLY=y TABLES=employees, 
departments, locations LOGFILE=estimate.log
 

Example 2-4 Performing a Schema Mode Export
預設狀態是匯出登入帳號的SCHEMA,無需指定。

> expdp hr/hr DUMPFILE=dpump_dir1:expschema.dmp LOGFILE=dpump_dir1:expschema.log
 
參考來源:Chapter 2, "Data Pump Export"
文章標籤
創作者介紹
創作者 味味A 的頭像
味味A

味味A

味味A 發表在 痞客邦 留言(0) 人氣()