PLSQL_資料泵Datapump匯入匯出資料IMPDP / EXPDP（概念）（Oracle資料匯入匯出工具）

標籤：des   blog   http   io   os   使用   java   ar   strong   

一、摘要

在平常備庫和資料庫遷移的時候，當遇到大的資料庫的時候在用exp的時候往往是需要好幾個小時，耗費大量時間。oracle10g以後可以用expdp來匯出資料庫花費的時間要遠小於exp花費的時間，而且檔案也要小很多。

二、exp/imp與expdp/impdp區別

(1) 把使用者usera的對象導到使用者userb,用法區別在於fromuser=usera touser=userb ,remap_schema=’usera’:‘usera’ 。

例如:imp system/passwd fromuser=usera touser=userb file=/oracle/exp.dmp log=/oracle/exp.log;

      impdp system/passwd directory=expdp dumpfile=expdp.dmp remap_schema=’usera’:‘userb’ logfile=/oracle/exp.log;

(2) 更換資料表空間，用exp/imp的時候，要想更改表所在的資料表空間，需要手工去處理一下，

如alter table xxx move tablespace_new之類的操作。

用impdp只要用remap_tablespace=’tabspace_old’:‘tablespace_new’

(3) 當指定一些表的時候，使用exp/imp 時，tables的用法是 tables=(‘table1′,’table2′,’table3′)。

expdp/impdp的用法是tables=’table1′,’table2′,’table3′

(4) 是否要匯出資料行

exp　（ROWS=Y 匯出資料行，ROWS=N 不匯出資料行）

expdp content（ALL:對象＋匯出資料行，DATA_ONLY：只匯出對象，METADATA_ONLY：只匯出資料的記錄）

(5) expdp是[10g]的新特性而且只能在伺服器執行。而exp/imp是通用的。

(6) oracle11g中有個新特性，當表無資料時，不分配segment，以節省空間的,所以exp導不出空表。解決的辦法是用expdp， 當然也可以設定deferred_segment_creation 參數 或者 insert一行，再rollback，但是這樣很麻煩。

三、匯出資料

1. 匯出expdb的過程

(1). 按使用者導

        expdp scott/[email protected] schemas=scott dumpfile=expdp.dmp DIRECTORY=dpdata1;

(2). 並行進程parallel

        expdp scott/[email protected] directory=dpdata1 dumpfile=scott3.dmp parallel=40 job_name=scott3

(3). 按表名導

        expdp scott/[email protected] TABLES=emp,dept dumpfile=expdp.dmp DIRECTORY=dpdata1;

(4). 按查詢條件導

        expdp scott/[email protected] directory=dpdata1 dumpfile=expdp.dmp Tables=emp query=‘WHERE deptno=20‘;

(5). 按資料表空間導

        expdp system/manager DIRECTORY=dpdata1 DUMPFILE=tablespace.dmp TABLESPACES=temp,example;

(6). 導整個資料庫

        expdp system/manager DIRECTORY=dpdata1 DUMPFILE=full.dmp FULL=y;

2. 參數說明 - 匯出expdb

(1). CONTENT：該選項用於指定要匯出的內容.預設值為ALL

      CONTENT={ALL | DATA_ONLY | METADATA_ONLY}

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

(2). DIRECTORY：指定轉儲檔案和記錄檔所在的目錄：DIRECTORY=directory_object

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

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

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

      Expdp scott/tiger DIRECTORY=dump DUMPFILE=a.dup EXCLUDE=VIEW

(4). INCLUDE：匯出時包含指定的類型

        (例:INCLUDE=TABLE_DATA,

             INCLUDE=TABLE:"LIKE ‘TAB%‘"

             INCLUDE=TABLE:”NOT LIKE ‘TAB%’”…)

             EXCLUDE:匯出時排除的資料類型(例:EXCLUDE=TABLE:EMP)

(5). FILESIZE：指定匯出檔案的最大尺寸,預設為0,(表示檔案尺寸沒有限制)(單位為bytes).

(6). JOB_NAME：此次匯出進程使用的名稱,方便跟蹤查詢(可選)

(7). FLASHBACK_SCN：指定匯出特定SCN時刻的表資料

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

       Expdp scott/tiger DIRECTORY=dump DUMPFILE=a.dmp

       FLASHBACK_SCN=358523

(8). FLASHBACK_TIME：指定匯出特定時間點的表資料：FLASHBACK_TIME=“TO_TIMESTAMP(time_value)”

       Expdp scott/tiger DIRECTORY=dump DUMPFILE=a.dmp FLASHBACK_TIME=“TO_TIMESTAMP(’25-08-2004 14:35:00’,’DD-MM-YYYY HH24:MI:SS’)”

(9). TABLESPACE：指定一個資料表空間匯出.

(10). QUERY=[schema.] [table_name:] query_clause

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

        Expdp scott/tiger directory=dump dumpfiel=a.dmp Tables=emp query=’WHERE deptno=20’

(11). PARALLEL：並行操作： 指定執行匯出操作的並行進程個數,預設值為1

可以通過PARALLEL 參數為匯出使用一個以上的線程來顯著地加速作業。每個線程建立一個單獨的轉儲檔案，因此參數dumpfile 應當擁有和並行度一樣多的項目。

可以指定萬用字元作為檔案名稱，而不是顯式地輸入各個檔案名稱，例如：

expdp ananda/abc123 tables=CASES directory=DPDATA1 dumpfile=expCASES_%U.dmp parallel=4 job_name=Cases_Export

注意:dumpfile 參數擁有一個萬用字元%U，它指示檔案將按需要建立，格式將為expCASES_nn.dmp，其中nn 從01 開始，然後按需要向上增加。

在並行模式下，狀態螢幕將顯示四個背景工作處理序。（在預設模式下，只有一個進程是可見的）所有的背景工作處理序同步取出資料，並在狀態螢幕上顯示它們的進度。

分離訪問資料檔案和轉儲目錄檔案系統的輸入/輸出通道是很重要的。否則，與維護Data Pump 作業相關的開銷可能超過並行線程的效益，並因此而降低效能。並行方式只有在表的數量多於並行值並且表很大時才是有效。

四、還原資料

1. 匯入impdp的過程

(1). 導到指定使用者下

        impdp scott/tiger DIRECTORY=dpdata1 DUMPFILE=expdp.dmp SCHEMAS=scott;

(2). 改變表的owner

        impdp system/manager DIRECTORY=dpdata1 DUMPFILE=expdp.dmp TABLES=scott.dept REMAP_SCHEMA=scott:system;

(3). 匯入資料表空間

        impdp system/manager DIRECTORY=dpdata1 DUMPFILE=tablespace.dmp TABLESPACES=example;

(4). 匯入資料庫

        impdb system/manager DIRECTORY=dump_dir DUMPFILE=full.dmp FULL=y;

(5). 追加資料

        impdp system/manager DIRECTORY=dpdata1 DUMPFILE=expdp.dmp SCHEMAS=system TABLE_EXISTS_ACTION

2. 參數說明 - 匯入impdp

(1). TABBLE_EXISTS_ACTION={SKIP | APPEND | TRUNCATE | FRPLACE }

當設定該選項為SKIP時，匯入作業會跳過已存在表處理下一個對象；

當設定為APPEND時，會追加資料；

當設定為TRUNCATE時，匯入作業會截斷表,然後為其追加新資料；

當設定為REPLACE時，匯入作業會刪除已存在表,重建表病追加資料；

注意，TRUNCATE選項不適用與簇表和NETWORK_LINK選項；

(2). REMAP_SCHEMA

該選項用於將源方案的所有對象裝載到目標方案中：REMAP_SCHEMA=source_schema:target_schema

(3). REMAP_TABLESPACE

將源資料表空間的所有對象匯入到目標資料表空間中：REMAP_TABLESPACE=source_tablespace:target:tablespace

(4). REMAP_DATAFILE

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

REMAP_DATAFIEL=source_datafie:target_datafile

四、案例 - 從PROD中匯出資料，後匯入SIT環境中

Step1. 建立邏輯目錄，該命令不會在作業系統建立真正的目錄，最好以system等管理員建立。

create directory dpdata as ‘/home/oracle/expdb‘;

Step2. 查看管理理員目錄（同時查看作業系統是否存在，因為Oracle並不關心該目錄是否存在，如果不存在，則出錯）

select * from dba_directories;

Step3. 給scott使用者賦予在指定目錄的操作許可權，最好以system等管理員賦予。

grant read,write on directory dpdata to oracle;

Step4. 在PROD測試表中匯入10條記錄，並匯出

create table scott.emp (  emp_id number,  name varchar2(50),       sex varchar(2),  age number,  country varchar(10),  salary number);

begin  insert into scott.emp values (1, ‘baoxinjian1‘, ‘M‘, 27, ‘China‘, 10000);  insert into scott.emp values (1, ‘baoxinjian1‘, ‘M‘, 27, ‘China‘, 20000);  insert into scott.emp values (1, ‘baoxinjian1‘, ‘M‘, 27, ‘China‘, 30000);  insert into scott.emp values (1, ‘baoxinjian1‘, ‘M‘, 27, ‘China‘, 40000);  insert into scott.emp values (1, ‘baoxinjian1‘, ‘M‘, 27, ‘China‘, 50000);  insert into scott.emp values (1, ‘baoxinjian1‘, ‘M‘, 27, ‘China‘, 60000);  insert into scott.emp values (1, ‘baoxinjian1‘, ‘M‘, 27, ‘China‘, 70000);  insert into scott.emp values (1, ‘baoxinjian1‘, ‘M‘, 27, ‘China‘, 80000);  insert into scott.emp values (1, ‘baoxinjian1‘, ‘M‘, 27, ‘China‘, 90000);end;commit;

Step5. 匯出為dmp檔案，上傳至SIT

expdp sys/[email protected] TABLES=scott.emp dumpfile=empexpdp.dmp DIRECTORY=dpdata;

Step6. 查看匯出log內容

Step7. 將資料檔案上傳至SIT環境

Step8. 進行上傳

impdp sys/oracle DIRECTORY=dpdata DUMPFILE=empexpdp.dmp SCHEMAS=sys;

Step9. 查看匯入Log日誌內容

Step10. 查看資料庫表是否成功

 

參考： http://blog.chinaunix.net/uid-16844439-id-3213672.html

參考：http://www.2cto.com/database/201202/120126.html

參考：http://czmmiao.iteye.com/blog/2041703

PLSQL_資料泵Datapump匯入匯出資料IMPDP / EXPDP（概念）（Oracle資料匯入匯出工具）（轉）