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. |
程式設計 (8)