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)
