對於Oracle 資料庫之間的匯入匯出，可以使用Oracle提供的匯入匯出工具EXP/IMP來實現。EXP/IMP是Oracle早期提供的資料匯入匯出工具。在Oracle 10g 中，提供了高速匯入匯出資料泵IMPDP，EXPDP，本文主要講述EXPDP的用法。
關於IMPDP的用法，請參照：資料泵IMPDP 匯入工具的使用

一、資料泵的體系結構
資料泵是一個基於伺服器端的高速匯入匯出工具，透過dbms_datapump包來呼叫
提供expdp，impdp，以及基於Web頁面來實現匯入匯出
提供兩種資料方式：直接路徑、外部表
可以定製資料泵作業，以及從作業中分離和重新附加到作業
伺服器端的資料泵是直接訪問資料檔案與SGA，不必透過會話進行訪問
資料泵程式
對Unix系統而言，資料泵程式為expdp，impdp
對Windows系統而言，資料泵程式為expdp.exe，impdp.exe
啟動一個DataPump作業，至少會啟動下列兩個程式，一個Data Pump Master(DMnn)，一個或多個工作程式(DWnn)，主程式控制工作程式
如果多個DataPump作業同時執行，那麼每個作業都具有自己的DMnn程式以及自己的DWnn程式
如果設定了並行技術，則每個DWnn程式可以使用兩個或多個並行執行伺服器(名稱為Pnnn)
DataPump生成下列三種形式的檔案
SQL檔案：描述指定作業所包含物件的若干DDL語句
轉儲檔案：即包含資料和後設資料的檔案
日誌檔案：用於記錄匯出時的相關資訊
目錄
用於設定匯入匯出檔案所在或存放的位置 create directory dump_scott as /home/oracle/dump/scott';
可以透過dba_directories來檢視系統中已建立的目錄 select * from dba_directories;
對於建立的目錄必須授予使用者讀寫目錄的許可權 grant read,write on directory dump_scott to scott;

二、資料泵的優點
在Oracel 10g 中提供的資料泵，較之i時代的匯入匯出工具(imp,exp)，除了能實現imp/exp的功能之外，提供了更好的效能，下面是資料泵的優點
為資料及資料物件提供更細微級別的選擇性(使用exclude,include,content引數)
可以設定資料庫版本號(主要是用於相容老版本的資料庫系統)
並行執行
預估匯出作業所需要的磁碟空間(使用estimate_only引數)
支援分散式環境中透過資料庫連結實現匯入匯出
支援匯入時重新對映功能(即將物件匯入到新的目標資料檔案，架構，表空間等)
支援後設資料壓縮及資料取樣

三、資料泵程式介面及模式
資料泵匯入匯出介面如下
命令列介面
引數檔案
互動式命令列介面
資料庫控制檯

資料泵匯入匯出模式
整個資料庫
架構
表
表空間
傳輸表空間

四、匯出工具expdp

1. 它是作業系統下一個可執行的檔案存放目錄/ORACLE_HOME/bin
[oracle@oradb bin]$ ls -lh expdp
-rwxr-x--x 1 oracle oinstall 174K Sep 13 20:01 expdp

expdp匯出工具將資料庫中資料備份壓縮成一個二進位制系統檔案.可以在不同OS間遷移

expdb支援三種模式：
a. 表模式：匯出使用者所有表或者指定的表
b. 使用者模式：匯出使用者所有物件以及物件中的資料
c. 匯出表空間：匯出資料庫中特定的表空間
d. 整個資料庫：匯出資料庫中所有物件

使用expdp-? 可以檢視expdp命令的用法並啟動互動程式，也可使用expdp -help來檢視更詳細的幫助資訊
[oracle@oradb bin]$ expdp -?
abort_step Undocumented feature
access_method Data Access Method - default is Automatic
attach Attach to existing job - no default)''
compression Content to export: default is METADATA_ONLY
content Content to export: default is ALL
directory Default directory specification
dumpfile dumpfile names: format is (file1,...) default is expdat.dmp
encryption_password Encryption key to be used
estimate Calculate size estimate: default is BLOCKS
estimate_only Only estimate the length of the job: default is N
exclude Export exclude option: no default
filesize file size: the size of export dump files
flashback_time database time to be used for flashback export: no default
flashback_scn system change number to be used for flashback export: no default
full indicates a full mode export
include export include option: no default
ip_address IP Address for PLSQL debugger
help help: display description on export parameters, default is N
job_name Job Name: no default)''
keep_master keep_master: Retain job table upon completion
log_entry logentry
logfile log export messages to specified file
metrics Enable/disable object metrics reporting
mp_enable Enable/disable multi-processing for current session
network_link Network mode export
nologfile No export log file created
package_load Specify how to load PL/SQL objects
parallel Degree of Parallelism: default is 1
parallel_threshold Degree of DML Parallelism
parfile parameter file: name of file that contains parameter specifications
query query used to select a subset of rows for a table
sample Specify percentage of data to be sampled
schemas schemas to export: format is '(schema1, .., schemaN)'
silent silent: display information, default is NONE
status Interval between status updates
tables Tables to export: format is '(table1, table2, ..., tableN)'
tablespaces tablespaces to transport/recover: format is '(ts1,..., tsN)'
trace Trace option: enable sql_trace and timed_stat, default is 0
transport_full_check TTS perform. test for objects in recovery set: default is N
transport_tablespaces Transportable tablespace option: default is N
tts_closure_check Enable/disable transportable containment check: def is Y
userid user/password to connect to oracle: no default
version Job version: Compatible is the default

Export: Release 10.2.0.1.0 - Production on Monday, 20 September, 2010 14:22:56

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

Username:

2. 匯出工具expdp非互動式命令列方式的例子
a.基於表模式的匯出
SQL> create directory dump_scott as '/home/oracle/dump/scott';

Directory created.

SQL> select * from dba_directories;

OWNER DIRECTORY_NAME DIRECTORY_PATH
------------------------------ ------------------------------ --------------------------------------------------
SYS DUMP_SCOTT /home/oracle/dump/scott

SQL> grant read,write on directory dump_scott to scott;

Grant succeeded.

SQL> !
[oracle@oradb /]$ mkdir /home/oracle/dump
[oracle@oradb /]$ mkdir /home/oracle/dump/scott
[oracle@oradb ~]$ expdp scott/tiger directory=dump_scott dumpfile=dumptab.dmp /
> logfile=scott.log tables=dept,emp

Export: Release 10.2.0.1.0 - Production on Monday, 20 September, 2010 14:55:23

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

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Starting "SCOTT"."SYS_EXPORT_TABLE_01": scott/********/ directory=dump_scott dumpfile=dumptab.dmp logfile=scott.logtables=dept,emp
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 128 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "SCOTT"."DEPT" 5.656 KB 4 rows
. . exported "SCOTT"."EMP" 7.820 KB 14 rows
Master table "SCOTT"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SCOTT.SYS_EXPORT_TABLE_01 is:
/home/oracle/dump/scott/dumptab.dmp
Job "SCOTT"."SYS_EXPORT_TABLE_01" successfully completed at 14:55:56

--後臺中DMnn,DWnn程式為啟動DataPump是產生的程式
[oracle@oradb /]$ ps -ef | grep ora_d
oracle 3445 1 0 14:19 ? 00:00:00 ora_dbw0_orcl
oracle 3461 1 0 14:19 ? 00:00:00 ora_d000_orcl

[oracle@oradb ~]$ ls -lh /home/oracle/dump/scott
total 132K
-rw-r----- 1 oracle oinstall 124K Sep 20 14:55 dumptab.dmp
-rw-r--r-- 1 oracle oinstall 1.4K Sep 20 14:55 scott.log

b. 基於使用者模式匯出
[oracle@oradb /]$ expdp scott/tiger directory=dump_scott dumpfile=dumpscott.dmp schemas=scott

Export: Release 10.2.0.1.0 - Production on Monday, 20 September, 2010 15:08:55

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

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Starting "SCOTT"."SYS_EXPORT_SCHEMA_01": scott/********/ directory=dump_scott dumpfile=dumpscott.dmp schemas=scott
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 192 KB
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "SCOTT"."DEPT" 5.656 KB 4 rows
. . exported "SCOTT"."EMP" 7.820 KB 14 rows
. . exported "SCOTT"."SALGRADE" 5.585 KB 5 rows
. . exported "SCOTT"."BONUS" 0 KB 0 rows
Master table "SCOTT"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SCOTT.SYS_EXPORT_SCHEMA_01 is:
/home/oracle/dump/scott/dumpscott.dmp
Job "SCOTT"."SYS_EXPORT_SCHEMA_01" successfully completed at 15:09:23

c.基於表空間匯出
[oracle@oradb /]$ expdp scott/tiger directory=dump_scott dumpfile=users1.dmp,user2.dmp /
> compression tablespaces=users

Export: Release 10.2.0.1.0 - Production on Monday, 20 September, 2010 15:17:35

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

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Starting "SCOTT"."SYS_EXPORT_TABLESPACE_01": scott/********/ directory=dump_scott dumpfile=users1.dmp,user2.dmp compression tablespaces=users
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 192 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/COMMENT
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "SCOTT"."DEPT" 5.656 KB 4 rows
. . exported "SCOTT"."EMP" 7.820 KB 14 rows
. . exported "SCOTT"."SALGRADE" 5.585 KB 5 rows
. . exported "SCOTT"."BONUS" 0 KB 0 rows
Master table "SCOTT"."SYS_EXPORT_TABLESPACE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SCOTT.SYS_EXPORT_TABLESPACE_01 is:
/home/oracle/dump/scott/users1.dmp
Job "SCOTT"."SYS_EXPORT_TABLESPACE_01" successfully completed at 15:17:51

[oracle@oradb /]$ ls -lh /home/oracle/dump/scott
total 524K
-rw-r----- 1 oracle oinstall 4.0K Sep 20 15:17 compression.dmp
-rw-r----- 1 oracle oinstall 224K Sep 20 15:09 dumpscott.dmp
-rw-r----- 1 oracle oinstall 124K Sep 20 14:55 dumptab.dmp
-rw-r--r-- 1 oracle oinstall 1.6K Sep 20 15:17 export.log
-rw-r--r-- 1 oracle oinstall 1.4K Sep 20 14:55 scott.log
-rw-r----- 1 oracle oinstall 4.0K Sep 20 15:17 user2.dmp
-rw-r----- 1 oracle oinstall 148K Sep 20 15:17 users1.dmp

d. 匯出整個資料庫，且使用並行匯出方式
[oracle@oradb /]$ expdp scott/tiger directory=dump_scott dumpfile=full20_%u.dmp parallel=4 full=y

Export: Release 10.2.0.1.0 - Production on Monday, 20 September, 2010 15:24:02

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

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
ORA-31631: privileges are required
ORA-39161: Full database jobs require privileges

[oracle@oradb /]$ sqlplus /nolog

SQL*Plus: Release 10.2.0.1.0 - Production on Mon Sep 20 15:24:16 2010

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

SQL> conn /as sysdba
Connected.
Grant succeeded.

SQL> !
[oracle@oradb /]$ expdp scott/tiger directory=dump_scott dumpfile=full20_%u.dmp parallel=6 full=y
--中間過程省略
[oracle@oradb dump]$ ls -lh ./scott/fu*
-rw-r----- 1 oracle oinstall 19M Sep 20 15:36 ./scott/full20_01.dmp
-rw-r----- 1 oracle oinstall 22M Sep 20 15:34 ./scott/full20_02.dmp
-rw-r----- 1 oracle oinstall 18M Sep 20 15:36 ./scott/full20_03.dmp
-rw-r----- 1 oracle oinstall 15M Sep 20 15:36 ./scott/full20_04.dmp
-rw-r----- 1 oracle oinstall 5.4M Sep 20 15:36 ./scott/full20_05.dmp
-rw-r----- 1 oracle oinstall 196K Sep 20 15:33 ./scott/full20_06.dmp

[oracle@oradb dump]$ ps -ef | grep ora_d
oracle 3445 1 0 14:19 ? 00:00:01 ora_dbw0_orcl
oracle 3461 1 0 14:19 ? 00:00:00 ora_d000_orcl
oracle 23443 1 4 15:32 ? 00:00:01 ora_dm00_orcl
oracle 23494 1 23 15:32 ? 00:00:08 ora_dw01_orcl
oracle 23673 1 11 15:33 ? 00:00:02 ora_dw02_orcl
oracle 23675 1 16 15:33 ? 00:00:03 ora_dw03_orcl
oracle 23677 1 8 15:33 ? 00:00:01 ora_dw04_orcl
oracle 23679 1 5 15:33 ? 00:00:00 ora_dw05_orcl
oracle 23681 1 2 15:33 ? 00:00:00 ora_dw06_orcl
oracle 23696 2416 0 15:33 pts/1 00:00:00 grep ora_d

五、資料泵的監控
1.查詢dba_directories獲得所建立的目錄
2.可以查詢dba_datapump_jobs來檢視資料泵作業的執行情況，也可以利用ATTACH重新連線上還在進行的JOB
每個datapump可以透過job_name 引數來指定作業名稱，如未指定，則系統使用預設的作業名稱,如下面的檢視中為SYS_EXPORT_FULL_01
透過v$session_longops也可以檢視長時間執行的datapump job的具體內容

SQL> select owner_name owr,job_name jbn,operation ope,job_mode jbm,state,degree,
2 attached_sessions atts,datapump_sessions dats
3 from dba_datapump_jobs;

OWR JBN OPE JBM STATE DEGREE ATTS DATS
---------- -------------------- -------------------- --------------- ---------- ---------- ---------- ----------
SCOTT SYS_EXPORT_FULL_01 EXPORT FULL COMPLETING 2 1 2

SQL> select sid, serial#,session_type
2 from v$session s, dba_datapump_sessions d
3 where s.saddr = d.saddr;

SID SERIAL# SESSION_TYPE
---------- ---------- --------------
143 10 DBMS_DATAPUMP
149 37 MASTER
132 3 WORKER
136 3 WORKER
135 4 WORKER
141 5 WORKER
128 2 WORKER
142 4 WORKER

3.監控資料泵的邏輯備份程度
SELECT sid, serial#, context, sofar, totalwork,
ROUND(sofar/totalwork*100,2) "%_COMPLETE"
FROM v$session_longops
WHERE opname LIKE '%EXP%'
AND totalwork != 0
AND sofar <> totalwork;

SID SERIAL# CONTEXT SOFAR TOTALWORK %_COMPLETE
130 33 0 54 70 77.14

六、expdp的常用引數
1.content: 該選項用於指定要匯出的內容.預設值為ALL
CONTENT={ALL | DATA_ONLY | METADATA_ONLY}

expdp scott/tiger schemas=scott content=all
expdp scott/tiger tables=emp content=data_only directory=dump_scott dumpfile=empdata.dmp(只匯出物件資料)
expdp scott/tiger tables=emp content=metadata_only directory=dump_scott dumpfile=empmd.dmp(只有定義資訊)

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

expdp scott/tiger directory=dump_scott dumpfile=dba_object.dmp tablespaces=users estimate=statistics
expdp scott/tiger directory=dump_scott dumpfile=dba_object.dmp tablespaces=users estimate=blocks

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

expdp scott/tiger schemas=scott estimate_only=y
設定為Y時,匯出作用只估算物件所佔用的磁碟空間,而不會執行匯出作業,
為N時,不僅估算物件所佔用的磁碟空間,還會執行匯出操作.

4.exclude:該選項用於指定執行操作時釋放要排除物件型別或相關物件
exclude=view
exclude=package
exclude=index:"like 'EMP%'
object_type用於指定要排除的物件型別,name_expr用於指定要排除的具體物件.exclude和include不能同時使用
expdp scott/tiger schemas=scott exclude=view dumpfile=a9.dmp

include = object_type[:"name_expr"]
5.filesize:指定匯出檔案的最大尺寸,預設為,(表示檔案尺寸沒有限制)

6.flashback_scn: 前提閃回功能開啟
expdp scott/tiger tables=emp dumpfile=e2.dmp flashback_scn=4284715
如果閃回的時間點該物件結構發生變化，將報錯（比如該物件沒有建立或者ddl操作)

7.flashback_time：指定匯出特定時間點的表資料
expdp scott/tiger DIRECTORY=dump DUMPFILE=a.dmp FLASHBACK_TIME="TO_TIMESTAMP(’-08-2004 14:35:00’,’DD-MM-YYYY HH24:MI:SS’)"

windows下：
C:/>expdp scott/tiger DIRECTORY=dump_dir DUMPFILE=a.dmp
flashback_time=/"TO_TIMESTAMP('06-04-2010 11:24:26', 'DD-MM-YYYY HH24:MI:SS')/"

8.query匯出查詢得到的結果集
query=scott.emp :"where deptno = 30 and sal > 3500"

9.sample 使用該引數進行對匯出的資料進行取樣
sample="scott"."emp":20
expdp scott/tiger directory=dump_scott dumpfile=sam.dmp sample=30

10.dumpfile 指定匯出時的檔名
dumpfile=scott_tb.dmp
dumpfile=scott_tb_%u.dmp %u 用於擴充套件匯出的檔名，固定長度為個字元，從開始遞增，使用並行匯出時建議指定該引數

ORACLE中的EXPDP總結

相關文章