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.

全站熱搜

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