oracle資料泵

一、expdp使用簡介
1.1 expdp資料檔案儲存介紹
使用EXPDP工具時，其轉儲檔案只能被存放在DIRECTORY物件對應的OS目錄中，而不能直接指定轉儲檔案所在的OS目錄.因此使用EXPDP工具時，必須首先建立DIRECTORY物件.並且需要為資料庫使用者授予使用DIRECTORY物件許可權。
1.2 建DIRECTORY路徑
SQL> conn /as sysdba
SQL> CREATE OR REPLACE DIRECTORY dir_dump  AS '/u01/backup/';
SQL> GRANT read,write ON DIRECTORY dir_dump TO public;
二、匯出型別
2.1 匯出scott整個schema(預設匯出登陸賬號的schema)
$ expdp scott/tiger@db_esuite parfile=/orahome/expdp.par 
expdp.par內容: 
      DIRECTORY=dir_dump
      DUMPFILE=scott_full.dmp
      LOGFILE=scott_full.log
2.2 其他賬號登陸，在引數中指定schemas
$ expdp system/oracle@db_esuite parfile=/orahome/expdp.par
expdp.par內容: 
      DIRECTORY=dir_dump
      DUMPFILE=scott_full.dmp
      LOGFILE=scott_full.log
      SCHEMAS=SCOTT
2.3 匯出scott下的dept和emp表
$ expdp scott/tiger@db_esuite parfile=/orahome/expdp.par
expdp.par內容: 
      DIRECTORY=dir_dump
      DUMPFILE=scott.dmp
      LOGFILE=scott.log
      TABLES=DEPT，EMP
2.4 匯出scott下除emp之外的表
$ expdp scott/tiger@db_esuite parfile=/orahome/expdp.par
expdp.par內容: 
      DIRECTORY=dir_dump
      DUMPFILE=scott.dmp
      LOGFILE=scott.log
      EXCLUDE=TABLE:"='EMP'"
2.5 匯出scott下的儲存過程
$ expdp scott/tiger@db_esuite parfile=/orahome/expdp.par
expdp.par內容: 
    DIRECTORY=dir_dump
    DUMPFILE=scott.dmp
    LOGFILE=scott.log
    INCLUDE=PROCEDURE
2.6 匯出scott下以'E'開頭的表
$ expdp scott/tiger@db_esuite parfile=/orahome/expdp.par
expdp.par內容: 
      DIRECTORY=dir_dump
      DUMPFILE=scott.dmp
      LOGFILE=scott.log
      INCLUDE=TABLE:"LIKE 'E%'"   ---可以改成NOT LIKE，就匯出不以E開頭的表
2.7 帶QUERY匯出
$ expdp scott/tiger@db_esuite parfile=/orahome/expdp.par
expdp.par內容: 
    DIRECTORY=dir_dump
    DUMPFILE=scott.dmp
    LOGFILE=scott.log
    TABLES=EMP，DEPT
    QUERY=EMP:"where empno>=8000"
    QUERY=DEPT:"where deptno>=10 and deptno<=40"
注：帶查詢的多表匯出，如果多表之間有外健關聯，需要注意查詢條件所篩選的資料是否符合這樣的外健約束，比如 EMP中有一欄位是 deptno，是關聯dept中的主鍵，如果"where empno>=8000"中得出的deptno=50的話，那你的dept的條件"where deptno>=10 and deptno<=40"就不包含deptno=50的資料，在匯入的時候就會報錯。
三、expdp選項
3.1 ATTACH
該選項用於在客戶會話與已存在匯出作業之間建立關聯。語法如下:
ATTACH=[schema_name.]job_name
schema_name用於指定方案名，job_name用於指定匯出作業名。如果使用ATTACH選項，在命令列除了連線字串和ATTACH選項外，不能指定任何其他選項，示例如下:
expdp scott/tiger ATTACH=scott.export_job
3.2 CONTENT
該選項用於指定要匯出的內容，預設值為ALL。語法如下:
CONTENT={ALL | DATA_ONLY | METADATA_ONLY}
當設定CONTENT為ALL 時，將匯出物件定義及其所有資料；為DATA_ONLY時，只匯出物件資料；為METADATA_ONLY時，只匯出物件定義，示例如下:
expdp scott/tiger DIRECTORY=dump DUMPFILE=a.dump CONTENT=METADATA_ONLY
3.3 DIRECTORY
指定轉儲檔案和日誌檔案所在的目錄。語法如下:
DIRECTORY=directory_object
directory_object用於指定目錄物件名稱。目錄物件是使用CREATE DIRECTORY語句建立的物件，而不是OS 目錄，示例如下:
expdp scott/tiger DIRECTORY=dump DUMPFILE=a.dump
建立目錄:
SQL> CREATE DIRECTORY dump as 'd:\dump';
查詢建立了那些子目錄:
SQL> SELECT * FROM dba_directories;
3.4 DUMPFILE
用於指定轉儲檔案的名稱，預設名稱為expdat.dmp。語法如下:
DUMPFILE=[directory_object:]file_name[，….]
directory_object用於指定目錄物件名，file_name用於指定轉儲檔名。需要注意，如果不指定directory_object，匯出工具會自動使用DIRECTORY選項指定的目錄物件，示例如下:
expdp scott/tiger DIRECTORY=dump1 DUMPFILE=dump2:a.dmp
3.5 ESTIMATE
指定估算被匯出表所佔用磁碟空間的方法。預設值是BLOCKS。語法如下:
EXTIMATE={BLOCKS | STATISTICS}
設定為BLOCKS時，oracle會按照目標物件所佔用的資料塊個數乘以資料塊尺寸估算物件佔用的空間，設定為STATISTICS時，根據最近統計值估算物件佔用空間，示例如下:
expdp scott/tiger TABLES=emp ESTIMATE=STATISTICS DIRECTORY=dump DUMPFILE=a.dump
一般情況下，當用預設值(blocks)時，日誌中估計的檔案大小會比實際expdp出來的檔案大，用statistics時會跟實際大小差不多。
3.6 EXTIMATE_ONLY
指定是否只估算匯出作業所佔用的磁碟空間，預設值為N。語法如下:
EXTIMATE_ONLY={Y | N}
設定為Y時，匯出作業只估算物件所佔用的磁碟空間，而不會執行匯出作業，為N時，不僅估算物件所佔用的磁碟空間，還會執行匯出操作，示例如下:
expdp scott/tiger ESTIMATE_ONLY=y NOLOGFILE=y
3.7 EXCLUDE
該選項用於指定執行操作時要排除的物件型別或相關物件。語法如下:
EXCLUDE=object_type[:name_clause][，….]
object_type用於指定要排除的物件型別，name_clause用於指定要排除的具體物件.EXCLUDE和INCLUDE不能同時使用，示例如下:
expdp scott/tiger DIRECTORY=dump DUMPFILE=a.dup EXCLUDE=VIEW
在EXPDP的幫助檔案中，可以看到存在EXCLUDE和INCLUDE引數，這兩個引數文件中介紹的命令格式存在問題，正確用法是: 
EXCLUDE=OBJECT_TYPE[:name_clause][，...]
INCLUDE=OBJECT_TYPE[:name_clause][，...]
示例：
expdp   schema=scott exclude=sequence，table:"in('EMP'，'DEPT')"
impdp   schema=scott include=function，package，procedure，table:"='EMP'"
有了這些還不夠，由於命令中包含了多個特殊字元，在不同的作業系統下需要通過轉義字元才能使上面的命令順利執行，如：EXCLUDE=TABLE:\"IN('BIGTALE')\"。
3.8 FILESIZE
指定匯出檔案的最大尺寸，預設為0(表示檔案尺寸沒有限制)。
3.9 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
3.10 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')"
3.11 FULL
指定資料庫模式匯出，預設為N。語法如下:
FULL={Y | N}
為Y時，標識執行資料庫匯出。
3.12 HELP
指定是否顯示EXPDP命令列選項的幫助資訊，預設為N。當設定為Y時，會顯示匯出選項的幫助資訊，示例如下: expdp help=y。
3.13 INCLUDE
指定匯出時要包含的物件型別及相關物件。語法如下:
INCLUDE=object_type[:name_clause][，… ]
示例如下:
expdp scott/tiger DIRECTORY=dump DUMPFILE=a.dmp INCLUDE=trigger
3.14 JOB_NAME
指定要匯出作業的名稱，預設為SYS_XXX。語法如下:
JOB_NAME=jobname_string
示例如下:
expdp scott/tiger DIRECTORY=dump DUMPFILE=a.dmp INCLUDE=trigger JOB_NAME=exp_trigger
後面想臨時停止expdp任務時可以按Ctrl+C組合鍵，退出當前互動模式，退出之後匯出操作不會停止，這不同於Oracle以前的EXP。以前的EXP，如果退出互動式模式，就會出錯終止匯出任務。在Oracle10g中，由於EXPDP是資料庫內部定義的任務，已經與客戶端無關，退出互動之後，會進入export的命令列模式，此時支援 status等檢視命令：
Export> status
如果想停止改任務，可以發出stop_job命令:
Export> stop_job
如果有命令列提示: "是否確實要停止此作業([Y]/N):" 或 "Are you sure you wish to stop this job ([yes]/no):"，回答應是yes或者no，回答是YES以後會退出當前的export介面。接下來可以通過命令列再次連線到這個任務:
expdp test/test@acf attach=expfull
通過start_job命令重新啟動匯出:
Export> start_job
Export> status
3.15 LOGFILE
指定匯出日誌檔案檔案的名稱，預設名稱為export.log。語法如下:
LOGFILE=[directory_object:]file_name
directory_object用於指定目錄物件名稱，file_name用於指定匯出日誌檔名。如果不指定directory_object，匯出作用會自動使用DIRECTORY的相應選項值，示例如下:
expdp scott/tiger DIRECTORY=dump DUMPFILE=a.dmp logfile=a.log
3.16 NETWORK_LINK
指定資料庫鏈名，如果要將遠端資料庫物件匯出到本地例程的轉儲檔案中，必須設定該選項。expdp中使用連線字串和network_link的區別:
expdp屬於服務端工具，而exp屬於客戶端工具，expdp生成的檔案預設是存放在服務端的，而exp生成的檔案是存放在客戶端。
expdp username/password@connect_string ，對於使用這種格式來說，directory使用源資料庫建立的，生成的檔案存放在服務端。如何將生成的檔案放在目標資料庫而不放在源資料庫？在expdp中使用network_link。 比如在本機expdp遠端伺服器的資料庫，先在本機建立到服務端的dblink，然後建立directory及授權，然後expdp。
a) 建立到服務端的dblink
SQL> conn aa/aacc
SQL> create database link … connect to bb identified by password using '…';
b) 建立directory
SQL> conn / as sysdba
SQL> create or replace directory dir as 'directory';
SQL> grant read,write on directory dir to bb;
c) 通過network_link匯出
expdp aa/aacc directory=dir network_link=link_name ...
3.17 NOLOGFILE
該選項用於指定禁止生成匯出日誌檔案，預設值為N。
3.18 PARALLEL
指定匯出操作的並行程式個數，預設值為1。該選項值要根據伺服器的CPU個數而定。
3.19 PARFILE
指定匯出引數檔案的名稱。語法如下:
PARFILE=[directory_path:]file_name
3.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=dump dumpfiel=a.dmp Tables=emp query='WHERE deptno=20'
3.21 SCHEMAS
該方案用於指定執行方案模式匯出，預設為當前使用者方案。
3.22 STATUS
指定顯示匯出作用程式的詳細狀態，預設值為0。
3.23 TABLES
指定表模式匯出。語法如下:
TABLES=[schema_name.]table_name[artition_name][，…]
schema_name用於指定方案名，table_name用於指定匯出的表名，partition_name用於指定要匯出的分割槽名。
3.24 TABLESPACES
指定要匯出表空間列表。
3.25 TRANSPORT_FULL_CHECK
該選項用於指定被搬移表空間和未搬移表空間關聯關係的檢查方式，預設為N。當設定為Y時，匯出作業會檢查表空間直接的完整關聯關係，如果表所在表空間或其索引所在的表空間只有一個表空間被搬移，將顯示錯誤資訊。當設定為N時，匯出作用只檢查單端依賴，如果搬移索引所在表空間，但未搬移表所在表空間，將顯示出錯資訊，如果搬移表所在表空間，未搬移索引所在表空間，則不會顯示錯誤資訊。
3.26 TRANSPORT_TABLESPACES
指定執行表空間模式匯出。
3.27 VERSION
指定被匯出物件的資料庫版本，預設值為COMPATIBLE。語法如下:
VERSION={COMPATIBLE | LATEST | version_string}
為COMPATIBLE時，會根據初始化引數COMPATIBLE生成物件後設資料；為LATEST時，會根據資料庫的實際版本生成物件後設資料；version_string用於指定資料庫版本字串。
四、匯入型別
4.1 匯入表
impdp scott/tiger DIRECTORY=dump_dir DUMPFILE=tab.dmp TABLES=dept,emp
impdp system/manager DIRECTORY=dump_dir DUMPFILE=tab.dmp TABLES=scott.dept,scott.emp REMAP_SCHEMA=SCOTT:SYSTEM
第一種方法表示將DEPT和EMP表匯入到SCOTT方案中，第二種方法表示將DEPT和EMP表匯入SYSTEM使用者下。注意，如果要將表匯入到其他方案中，必須指定REMAP_SCHEMA選項。
4.2 匯入方案
impdp scott/tiger DIRECTORY=dump_dir DUMPFILE=schema.dmp SCHEMAS=scott
impdp system/manager DIRECTORY=dump_dir DUMPFILE=schema.dmp SCHEMAS=scott REMAP_SCHEMA=scott:system
4.3 匯入表空間
impdp system/manager DIRECTORY=dump_dir DUMPFILE=tablespace.dmp TABLESPACES=user01
4.4 匯入資料庫
impdp system/manager DIRECTORY=dump_dir DUMPFILE=full.dmp FULL=y
4.5 通過DBLINK的方式直接從遠端資料庫匯入資料
4.5.1 建立Directory
SQL> connect u_test/xxx
SQL> !mkdir /u02/backup
SQL> create or replace directory dir_dpdump as '/u02/backup';
4.5.2 建立DBLINK
SQL> create database link ESUITE.NET connect to u_test identified by "xxx" using '…';
SQL> select count(*) from tab@ESUITE.NET;
4.5.3 利用IMPDP匯入資料
$ impdp 'u_test/"xxx"' SCHEMAS=(u_test) directory=dir_dpdump network_link="ESUITE.NET" logfile=impdp.log
$ impdp 'u_test/"xxx"' TABLES=LOG_RECORD_DETAIL_20090422 directory=dir_dpdump network_link="ESUITE.NET"
4.6 例項
SQL> conn /as sysdba
SQL> CREATE OR REPLACE DIRECTORY dir_dump  AS '/u01/backup/';
SQL> GRANT read，write ON DIRECTORY dir_dump TO public;
SQL> grant connect，resource to trial_wending identified by 'xxx';
SQL> CREATE TABLESPACE WENDING_LOG01 DATAFILE '/orahome/oradata/WENDING/wending_log01.dbf' SIZE 100M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED;
SQL> CREATE TABLESPACE WENDING_USER01 DATAFILE '/orahome/oradata/WENDING/wending_user01.dbf' SIZE 100M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED;
SQL> CREATE TABLESPACE WENDING_INDEX01 DATAFILE '/orahome/oradata/WENDING/wending_index01.dbf' SIZE 100M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED;
注意，如果匯入的使用者下的過程或同義詞用到了資料庫連線，先把資料庫連結給建上，否則匯入這些物件時會很慢。
impdp u_test/xxx directory=dir_dump  dumpfile=trial_wending_20081217.dp schemas=trial_wending     ---從全備份中提取
impdp cat/passwd directory=dir_dump dumpfile=cat.dmp schemas=cat logfile=impcatr.log   ---從自身匯出的方案中匯入
五、impdp選項
IMPDP命令列選項與EXPDP有很多相同的，如下：
5.1 REMAP_DATAFILE
該選項用於將源資料檔名轉變為目標資料檔名，在不同平臺之間搬移表空間時可能需要該選項：
REMAP_DATAFIEL=source_datafie:target_datafile
5.2 REMAP_SCHEMA
該選項用於將源方案的所有物件裝載到目標方案中：
REMAP_SCHEMA=source_schema:target_schema
5.3 REMAP_TABLESPACE
將源表空間的所有物件匯入到目標表空間中：
REMAP_TABLESPACE=source_tablespace:target_tablespace
5.4 REUSE_DATAFILES
該選項指定建立表空間時是否覆蓋已存在的資料檔案，預設為N：
REUSE_DATAFIELS={Y | N}
5.5 SKIP_UNUSABLE_INDEXES
指定匯入是是否跳過不可使用的索引，預設為N。
5.6 SQLFILE指定將匯入要指定的索引DDL操作寫入到SQL指令碼中.
SQLFILE=[directory_object:]file_name
impdp scott/tiger DIRECTORY=dump DUMPFILE=tab.dmp SQLFILE=a.sql
5.7 STREAMS_CONFIGURATION
指定是否匯入流後設資料(Stream Matadata)，預設值為Y。
5.8 TABLE_EXISTS_ACTION
該選項用於指定當表已經存在時匯入作業要執行的操作，預設為SKIP。
TABBLE_EXISTS_ACTION={SKIP | APPEND | TRUNCATE | REPLACE }
當設定該選項為SKIP時，匯入作業會跳過已存在表處理下一個物件; 當設定為APPEND時，會追加資料; 為TRUNCATE時，匯入作業會截斷表，然後為其追加新資料; 當設定為REPLACE時，匯入作業會刪除已存在表，重建表並追加資料。
注意：TRUNCATE選項不適用與簇表和NETWORK_LINK選項。
5.9 TRANSFORM
該選項用於指定是否修改建立物件的DDL語句：
TRANSFORM=transform_name:value[bject_type]
transform_name用於指定轉換名，其中SEGMENT_ATTRIBUTES用於標識段屬性(物理屬性/儲存屬性/表空間/日誌等資訊)，STORAGE用於標識段儲存屬性; VALUE用於指定是否包含段屬性或段儲存屬性; object_type用於指定物件型別。
impdp scott/tiger directory=dump dumpfile=tab.dmp transform=segment_attributes:n:table
5.10 TRANSPORT_DATAFILES
該選項用於指定搬移空間時要被匯入到目標資料庫的資料檔案。
TRANSPORT_DATAFILE=datafile_name
datafile_name用於指定被複制到目標資料庫的資料檔案.
impdp system/manager DIRECTORY=dump DUMPFILE=tts.dmp TRANSPORT_DATAFILES='/user01/data/tbs1.f'
六、EXPDP和IMPDP
Oracle Database 10g引入了最新的資料泵(Data Dump)技術，使DBA或開發人員可以將資料庫後設資料(物件定義)和資料快速移動到另一個oracle資料庫中。
6.1 資料泵匯出匯入(EXPDP和IMPDP)的作用
l  實現邏輯備份和邏輯恢復。
l  在資料庫使用者之間移動物件。
l  在資料庫之間移動物件。
l  實現表空間搬移。
6.2 資料泵匯出匯入與傳統匯出匯入的區別
在10g之前，傳統的匯出和匯入分別使用EXP工具和IMP工具，從10g開始，不僅保留了原有的EXP和IMP工具，還提供了資料泵匯出匯入工具EXPDP和IMPDP。使用EXPDP和IMPDP時應該注意的事項：
EXP和IMP是客戶段工具，既可以在客戶端使用，也可以在服務端使用。
EXPDP和IMPDP是服務端的工具，只能在ORACLE服務端使用，不能在客戶端使用。
IMP只適用於EXP匯出檔案，不適用於EXPDP匯出檔案；IMPDP只適用於EXPDP匯出檔案，而不適用於EXP匯出檔案。
七、備份指令碼
7.1 windows下檔名按日期格式
------------------------------------------------------------------------------------------------------------------
set y=%date:~0,4%
set m=%date:~5,2%
set d=%date:~8,2%
set dmpname=mis_%y%_%m%_%d%_1855.dmp
set logname=mis_%y%_%m%_%d%_1855.log
exp mis/database_wh888_com@dbserver1_192.168.1.16 statistics=none feedback=1000 file=h:\dbbak\%dmpname% log=h:\dbbak\logs\%dmpname%
------------------------------------------------------------------------------------------------------------------
注意：%前後的空格，空格會導致檔名分割成多個部分，導致exp失敗。
7.2 unix下檔名按日期格式
------------------------------------------------------------------------------------------------------------------
#/sbin/ksh
export ORACLE_BASE=/home/oracle/oracle
export ORACLE_HOME=/home/oracle/oracle/product/db_1
export ORACLE_SID=tmpdb
export PATH=$ORACLE_HOME/bin:/usr/sbin:$PATH
date=`date +"%Y%m%d%H%M%S"`
filename=$date.dmp
logname=$date.log
exp mis/database_wh888_com@dbserver1 statistics=none feedback=1000 file=/home/oracle/dbbackup/mis_$filename log=/home/oracle/dbbackup/mis_$logname
------------------------------------------------------------------------------------------------------------------

原文：http://www.itpub.net/thread-1618110-1-1.html

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29819001/viewspace-1485010/，如需轉載，請註明出處，否則將追究法律責任。