Oracle 資料泵詳解

標籤：

一、EXPDP和IMPDP使用說明

Oracle Database 10g引入了最新的資料泵(Data Dump)技術，資料泵匯出匯入(EXPDP和IMPDP)的作用

1）實現邏輯備份和邏輯恢複.

2）在資料庫使用者之間移動對象.

3）在資料庫之間移動對象

4）實現資料表空間搬移.

 

二、資料泵匯出匯入與傳統匯出匯入的區別

在10g之前,傳統的匯出和匯入分別使用EXP工具和IMP工具,從10g開始,不僅保留了原有的EXP和IMP工具,還提供了資料泵匯出匯入工具EXPDP和IMPDP.使用EXPDP和IMPDP時應該注意的事項：

1）EXP和IMP是用戶端工具程式,它們既可以在可以用戶端使用,也可以在服務端使用。

2）EXPDP和IMPDP是服務端的工具程式,他們只能在ORACLE服務端使用,不能在用戶端使用。

3）IMP只適用於EXP匯出檔案,不適用於EXPDP匯出檔案;IMPDP只適用於EXPDP匯出檔案,而不適用於EXP匯出檔案。

資料泵匯出包括匯出表,匯出方案,匯出資料表空間,匯出資料庫4種方式.

 

三、Expdp參數

EXPDP命令列選項，可通過expdp help=y查看：

E:\emp>expdp help=y

Export: Release 10.2.0.1.0- Production on 星期日, 03 5月, 2009 17:54:49

Copyright (c) 2003, 2005, Oracle.  All rights reserved.

資料泵匯出公用程式提供了一種用於在 Oracle 資料庫之間傳輸

資料對象的機制。該公用程式可以使用以下命令進行調用:

   樣本: expdp scott/tigerDIRECTORY=dmpdir DUMPFILE=scott.dmp

您可以控制匯出的運行方式。具體方法是: 在 ‘expdp‘ 命令後輸入

各種參數。要指定各參數, 請使用關鍵字:

   格式: expdp KEYWORD=value 或 KEYWORD=(value1,value2,...,valueN)

   樣本: expdp scott/tigerDUMPFILE=scott.dmp DIRECTORY=dmpdir SCHEMAS=scott

               或 TABLES=(T1:P1,T1:P2), 如果 T1 是分區表

USERID 必須是命令列中的第一個參數。

關鍵字               說明 (預設)

------------------------------------------------------------------------------

ATTACH                串連到現有作業, 例如 ATTACH [=作業名]。

COMPRESSION           減小有效轉儲檔案內容的大小

                      關鍵字值為: (METADATA_ONLY) 和 NONE。

CONTENT               指定要卸載的資料, 其中有效關鍵字為:

                      (ALL), DATA_ONLY 和 METADATA_ONLY。

DIRECTORY             供轉儲檔案和記錄檔使用的目錄對象。

DUMPFILE              目標轉儲檔案 (expdat.dmp) 的列表,

                      例如 DUMPFILE=scott1.dmp, scott2.dmp,dmpdir:scott3.dmp。

ENCRYPTION_PASSWORD   用於建立加密列資料的口令關鍵字。

ESTIMATE              計算作業估計值, 其中有效關鍵字為:

                     (BLOCKS) 和 STATISTICS。

ESTIMATE_ONLY         在不執行匯出的情況下計算作業估計值。

EXCLUDE               排除特定的物件類型, 例如EXCLUDE=TABLE:EMP。

FILESIZE              以位元組為單位指定每個轉儲檔案的大小。

FLASHBACK_SCN         用於將會話快照設定回以前狀態的 SCN。

FLASHBACK_TIME        用於擷取最接近指定時間的 SCN 的時間。

FULL                  匯出整個資料庫 (N)。

HELP                  顯示協助訊息 (N)。

INCLUDE               包括特定的物件類型, 例如INCLUDE=TABLE_DATA。

JOB_NAME              要建立的匯出作業的名稱。

LOGFILE               記錄檔名 (export.log)。

NETWORK_LINK          連結到源系統的遠端資料庫的名稱。

NOLOGFILE             不寫入記錄檔 (N)。

PARALLEL              更改當前作業的活動 worker 的數目。

PARFILE               指定參數檔案。

QUERY                 用於匯出表的子集的謂詞子句。

SAMPLE                要匯出的資料的百分比;

SCHEMAS               要匯出的方案的列表 (登入方案)。

STATUS                在預設值 (0) 將顯示可用時的新狀態的情況下,

                      要監視的頻率 (以秒計) 作業狀態。

TABLES                標識要匯出的表的列表 - 只有一個方案。

TABLESPACES           標識要匯出的資料表空間的列表。

TRANSPORT_FULL_CHECK  驗證所有表的儲存段 (N)。

TRANSPORT_TABLESPACES 要從中卸載中繼資料的資料表空間的列表。

VERSION               要匯出的對象的版本, 其中有效關鍵字為:

                      (COMPATIBLE), LATEST 或任何有效資料庫版本。

 

下列命令在互動模式下有效。

注: 允許使用縮寫

 

命令               說明

------------------------------------------------------------------------------

ADD_FILE              向轉儲檔案集中添加轉儲檔案。

CONTINUE_CLIENT       返回到記錄模式。如果處於空閑狀態, 將重新啟動作業。

EXIT_CLIENT           退出客戶機會話並使作業處於運行狀態。

FILESIZE              後續 ADD_FILE 命令的預設檔案大小 (位元組)。

HELP                  總結互動命令。

KILL_JOB              分離和刪除作業。

PARALLEL              更改當前作業的活動 worker 的數目。

                      PARALLEL=<worker 的數目>。

START_JOB             啟動/恢複當前作業。

STATUS                在預設值 (0) 將顯示可用時的新狀態的情況下,

                      要監視的頻率 (以秒計) 作業狀態。

                      STATUS[=interval]

STOP_JOB              順序關閉執行的作業並退出客戶機。

                      STOP_JOB=IMMEDIATE 將立即關閉

                      資料泵作業。

1）ATTACH

該選項用於在客戶會話與已存在匯出作用之間建立關聯.文法如下

ATTACH=[schema_name.]job_name

Schema_name用於指定方案名,job_name用於指定匯出作業名.注意,如果使用ATTACH選項在命令列除了連接字串和ATTACH選項外,不能指定任何其他選項,樣本如下:

Expdp scott/tiger ATTACH=scott.export_job

2） CONTENT

該選項用於指定要匯出的內容.預設值為ALL

CONTENT={ALL | DATA_ONLY | METADATA_ONLY}

當設定CONTENT為ALL 時,將匯出對象定義及其所有資料.為DATA_ONLY時,只匯出對象資料,為METADATA_ONLY時,只匯出對象定義

Expdp scott/tiger DIRECTORY=dumpDUMPFILE=a.dump

CONTENT=METADATA_ONLY

3） DIRECTORY

指定轉儲檔案和記錄檔所在的目錄

DIRECTORY=directory_object

Directory_object用於指定目錄對象名稱.需要注意,目錄對象是使用CREATE DIRECTORY語句建立的對象,而不是OS 目錄

Expdp scott/tiger DIRECTORY=dumpDUMPFILE=a.dump

建立目錄:

SQL> createdirectory dump_dir as ‘d:\dump‘;

目錄已建立。

SQL> grantread,write on directory dump_dir to scott;

授權成功。

查詢建立了那些子目錄:

SELECT * FROM dba_directories;

4） DUMPFILE

用於指定轉儲檔案的名稱,預設名稱為expdat.dmp

DUMPFILE=[directory_object:]file_name [,….]

Directory_object用於指定目錄對象名,file_name用於指定轉儲檔案名稱.需要注意,如果不指定directory_object,匯出工具會自動使用DIRECTORY選項指定的目錄對象

Expdp scott/tiger DIRECTORY=dump1DUMPFILE=dump2:a.dmp

5.）ESTIMATE

指定估算被匯出表所佔用磁碟空間分方法.預設值是BLOCKS

EXTIMATE={BLOCKS | STATISTICS}

設定為BLOCKS時,oracle會按照目標對象所佔用的資料區塊個數乘以資料區塊尺寸估算對象佔用的空間,設定為STATISTICS時,根據最近統計值估算對象佔用空間

Expdp scott/tiger TABLES=empESTIMATE=STATISTICS

DIRECTORY=dump DUMPFILE=a.dump

6.）EXTIMATE_ONLY

指定是否只估算匯出作業所佔用的磁碟空間,預設值為N

EXTIMATE_ONLY={Y | N}

設定為Y時,匯出作用只估算對象所佔用的磁碟空間,而不會執行匯出作業,為N時,不僅估算對象所佔用的磁碟空間,還會執行匯出操作.

Expdp scott/tiger ESTIMATE_ONLY=yNOLOGFILE=y

7.）EXCLUDE

該選項用於指定執行操作時釋放要排除物件類型或相關對象

EXCLUDE=object_type[:name_clause] [,….]

Object_type用於指定要排除的物件類型,name_clause用於指定要排除的具體對象.EXCLUDE和INCLUDE不能同時使用

Expdp scott/tiger DIRECTORY=dumpDUMPFILE=a.dup EXCLUDE=VIEW

8）FILESIZE

指定匯出檔案的最大尺寸,預設為0,(表示檔案尺寸沒有限制)

9. FLASHBACK_SCN

指定匯出特定SCN時刻的表資料

FLASHBACK_SCN=scn_value

Scn_value用於標識SCN值.FLASHBACK_SCN和FLASHBACK_TIME不能同時使用

Expdp scott/tiger DIRECTORY=dumpDUMPFILE=a.dmp

FLASHBACK_SCN=358523

10）FLASHBACK_TIME

指定匯出特定時間點的表資料

FLASHBACK_TIME=”TO_TIMESTAMP(time_value)”

Expdp scott/tiger DIRECTORY=dumpDUMPFILE=a.dmp FLASHBACK_TIME=

“TO_TIMESTAMP(’25-08-200414:35:00’,’DD-MM-YYYYHH24:MI:SS’)”

11）FULL

指定資料庫模式匯出,預設為N

FULL={Y | N}

為Y時,標識執行資料庫匯出.

12）HELP

指定是否顯示EXPDP命令列選項的協助資訊,預設為N

當設定為Y時,會顯示匯出選項的協助資訊.

Expdp help=y

13）INCLUDE

指定匯出時要包含的物件類型及相關對象

INCLUDE = object_type[:name_clause] [,… ]

14）JOB_NAME

指定要匯出作用的名稱,預設為SYS_XXX

JOB_NAME=jobname_string

15）LOGFILE

指定匯出記錄檔檔案的名稱,預設名稱為export.log

LOGFILE=[directory_object:]file_name

Directory_object用於指定目錄對象名稱,file_name用於指定匯出記錄檔名.如果不指定directory_object.匯出作用會自動使用DIRECTORY的相應選項值.

Expdp scott/tiger DIRECTORY=dumpDUMPFILE=a.dmp logfile=a.log

16）NETWORK_LINK

指定資料庫鏈名,如果要將遠端資料庫對象匯出到本地常式的轉儲檔案中,必須設定該選項.

17）NOLOGFILE

該選項用於指定禁止產生匯出記錄檔,預設值為N.

18）PARALLEL

指定執行匯出操作的並行進程個數,預設值為1

19）PARFILE

指定匯出參數檔案的名稱

PARFILE=[directory_path] file_name

20）QUERY

用於指定過濾匯出資料的where條件

QUERY=[schema.] [table_name:] query_clause

Schema用於指定方案名,table_name用於指定表名,query_clause用於指定條件限制子句.QUERY選項不能與CONNECT=METADATA_ONLY,EXTIMATE_ONLY,TRANSPORT_TABLESPACES等選項同時使用.

Expdp scott/tiger directory=dumpdumpfiel=a.dmp

Tables=emp query=’WHERE deptno=20’

21）SCHEMAS

該方案用於指定執行方案模式匯出,預設為目前使用者方案.

22）STATUS

指定顯示匯出作用進程的詳細狀態,預設值為0

23）TABLES

指定表模式匯出

TABLES=[schema_name.]table_name[:partition_name][,…]

Schema_name用於指定方案名,table_name用於指定匯出的表名,partition_name用於指定要匯出的分區名.

24）TABLESPACES

指定要匯出資料表空間列表

25）TRANSPORT_FULL_CHECK

該選項用於指定被搬移資料表空間和未搬移資料表空間關聯關係的檢查方式,預設為N.

當設定為Y時,匯出作用會檢查資料表空間直接的完整關聯關係,如果資料表空間所在資料表空間或其索引所在的資料表空間只有一個資料表空間被搬移,將顯示錯誤資訊.當設定為N時,匯出作用只檢查單端依賴,如果搬移索引所在資料表空間,但未搬移表所在資料表空間,將顯示出錯資訊,如果搬移表所在資料表空間,未搬移索引所在資料表空間,則不會顯示錯誤資訊.

26）TRANSPORT_TABLESPACES

指定執行資料表空間模式匯出

27）VERSION

指定被匯出對象的資料庫版本,預設值為COMPATIBLE.

VERSION={COMPATIBLE | LATEST |version_string}

為COMPATIBLE時,會根據初始化參數COMPATIBLE產生對象中繼資料;為LATEST時,會根據資料庫的實際版本產生對象中繼資料.version_string用於指定資料庫版本字串.

 

四、EXPDP用法

使用EXPDP工具時,其轉儲檔案只能被存放在DIRECTORY對象對應的OS目錄中,而不能直接指定轉儲檔案所在的OS目錄.因此,使用EXPDP工具時,必須首先建立DIRECTORY對象.並且需要為資料庫使用者授予使用DIRECTORY對象許可權.

CREATE DIRECTORY dump_dir AS ‘c:\emp’;

GRANT READ, WRITE ON DIRECTORY dump_dir TO scott;

 

 

 

1、匯出表

Expdp scott/tiger DIRECTORY=dump_dir DUMPFILE=dept.dmpTABLES=dept

 

 

 

註：在unix下要注意directory目錄的讀寫權限問題，如：

 

 

查看dump_dir所在的目錄：用sys使用者查看資料字典dba_directories 

更該該檔案夾的許可權：chown–R oracle:dba /exp,問題解決

 

2、匯出方案

expdp scott/tiger directory=dump_dirdumpfile=schema.dmp logfile=schema.log schemas=system

 

 

3、匯出資料表空間

expdp scott/tiger directory=dump_dirdumpfile=tb.dmp logfile=tb.log tablespaces=users

 

 

4、匯出資料庫

Expdp system/manager DIRECTORY=dump_dirDUMPFILE=full.dmp FULL=Y

Expdp scott/tiger DIRECTORY=dump_dirDUMPFILE=full.dmp FULL=Y

 

 

註：提示scott使用者沒有相應的許可權，給scott相應的許可權或使用system來做全庫匯出 。

SQL> grant exp_full_database to scott;

Grant succeeded.

然後在做全庫的匯出：

 

五、IMPDP參數

IMPDP與EXPDP的不同參數:

1、REMAP_DATAFILE

該選項用於將來源資料檔案名稱轉變為目標資料檔案名,在不同平台之間搬移資料表空間時可能需要該選項.

REMAP_DATAFIEL=source_datafie:target_datafile

2、REMAP_SCHEMA

該選項用於將源方案的所有對象裝載到目標方案中.

REMAP_SCHEMA=source_schema:target_schema

3、REMAP_TABLESPACE

將源資料表空間的所有對象匯入到目標資料表空間中

REMAP_TABLESPACE=source_tablespace:target:tablespace

4、REUSE_DATAFILES

該選項指定建立資料表空間時是否覆蓋已存在的資料檔案.預設為N

REUSE_DATAFIELS={Y | N}

5、SKIP_UNUSABLE_INDEXES

指定匯入是是否跳過不可使用的索引,預設為N

6、SQLFILE

指定將匯入要指定的索引DDL操作寫入到SQL指令碼中

SQLFILE=[directory_object:]file_name

Impdp scott/tiger DIRECTORY=dumpDUMPFILE=tab.dmp SQLFILE=a.sql

7、STREAMS_CONFIGURATION

指定是否匯入流中繼資料(StreamMatadata),預設值為Y.

8、TABLE_EXISTS_ACTION

該選項用於指定當表已經存在時匯入作業要執行的操作,預設為SKIP

TABBLE_EXISTS_ACTION={SKIP | APPEND |TRUNCATE | FRPLACE }

當設定該選項為SKIP時,匯入作業會跳過已存在表處理下一個對象;當設定為APPEND時,會追加資料,為TRUNCATE時,匯入作業會截斷表,然後為其追加新資料;當設定為REPLACE時,匯入作業會刪除已存在表,重建表並追加資料,注意,TRUNCATE選項不適用與簇表和NETWORK_LINK選項

9、TRANSFORM

該選項用於指定是否修改建立對象的DDL語句

TRANSFORM=transform_name:value[:object_type]

Transform_name用於指定轉換名,其中SEGMENT_ATTRIBUTES用於標識段屬性(物理屬性,儲存屬性,資料表空間,日誌等資訊),STORAGE用於標識段儲存性,VALUE用於指定是否包含段屬性或段儲存屬性,object_type用於指定物件類型.

Impdp scott/tiger directory=dumpdumpfile=tab.dmp

Transform=segment_attributes:n:table

10、TRANSPORT_DATAFILES

該選項用於指定搬移空間時要被匯入到目標資料庫的資料檔案

TRANSPORT_DATAFILE=datafile_name

Datafile_name用於指定被複製到目標資料庫的資料檔案

Impdp system/manager DIRECTORY=dumpDUMPFILE=tts.dmp

TRANSPORT_DATAFILES=’/user01/data/tbs1.f’

 

六、IMPDP用法

 

1、匯入表

impdp hsiufo/hsiufo directory=dump_dirdumpfile=full.dmp tables=scott.emp remap_schema=scott:scott

 

註：為為有一個全庫的邏輯備份 full.dmp，然後刪除使用者scott的emp表，在full.dmp中匯入emp到使用者scott

impdp hsiufo/hsiufo directory=dump_dirdumpfile=full.dmp tables=scott.test remap_schema=scott:system

第一種方法表示將EMP表匯入到SCOTT方案中,第二種方法表示將test表匯入的SYSTEM方案中.

注意,如果要將表匯入到其他方案中,必須指定REMAP SCHEMA選項.

2、匯入方案

impdp hsiufo/hsiufodirectory=dump_dir dumpfile=full.dmp schemas=scott

Impdp system/manager DIRECTORY=dump_dirDUMPFILE=schema.dmp

SCHEMAS=scott REMAP_SCHEMA=scott:system

3、匯入資料表空間

Impdp system/manager DIRECTORY=dump_dirDUMPFILE=tablespace.dmp

TABLESPACES=user01

4、匯入資料庫

Impdp system/manager DIRECTORY=dump_dirDUMPFILE=full.dmp FULL=y

Oracle 資料泵詳解