ORACLE 10G 有新版的EXP程式稱為EXPDP，但怎麼用？有那些新的指令？
發現Oracle offical 網站：Chapter 2, "Data Pump Export"，介紹的很好，
有新舊版指令對照，及一些參數的介紹跟範例，以下是自已截錄出來的重點，
若有更詳細的還需要至上述網站查，不過都是原文的。
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條件。
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列出ORACLE EXPORT DATA，前後版本語法差異（粗體字是之前常用指令）。
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. |
留言列表
Windows Server (2)
