oracle資料庫中expdp和impdp匯出匯入

expdp和impdp是oracle從10g開始新增加的匯出、匯入功能。名稱叫資料泵。

expdp：Data Pump Export
impdp：Data Pump Import

一、expdp、impdp和exp、imp的區別

expdp、impdp：
1、可以匯入匯出單個或多個資料庫、使用者(schema)、資料表空間、表。
2、強大的資料過濾功能。
3、速度快。
4、不支援XMLType資料。

exp、imp：
1、支援XMLType資料。
2、不支援FLOAT和DOUBLE資料類型。
3、功能和資料泵類似，更推薦使用資料泵除非是XMLType資料。

二、匯出匯入方法

小實驗：將hr使用者資料匯出，建立使用者hr2，再匯入建立使用者。

1、建立目錄對象
目錄是資料庫物件，它是一個在物理主機檔案系統上的目錄的別名。

mkdir /tmp/expdata
chmod 777 /tmp/expdata

同時要注意oracle使用者對匯出目錄要有讀寫權限。

SQL> create or replace directory dmpdir as '/tmp/expdata';

2、授予許可權

SQL> grant read,write on directory dmpdir to hr;

3、匯出

SQL> expdp hr/hr schemas=hr directory=dmpdir dumpfile=expdp.dmp logfile=expdp.log job_name=my_job;

Starting "HR"."MY_JOB":  hr/******** schemas=hr directory=dmpdir dumpfile=expdp.dmp logfile=expdp.log job_name=my_job
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 512 KB
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
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/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
Processing object type SCHEMA_EXPORT/VIEW/VIEW
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "HR"."COUNTRIES"                            6.367 KB      25 rows
. . exported "HR"."DEPARTMENTS"                          7.007 KB      27 rows
. . exported "HR"."EMPLOYEES"                            16.80 KB     107 rows
. . exported "HR"."JOBS"                                 6.992 KB      19 rows
. . exported "HR"."JOB_HISTORY"                          7.054 KB      10 rows
. . exported "HR"."LOCATIONS"                            8.273 KB      23 rows
. . exported "HR"."REGIONS"                              5.476 KB       4 rows
. . exported "HR"."TEST_T"                               6.796 KB      10 rows
Master table "HR"."MY_JOB" successfully loaded/unloaded
******************************************************************************
Dump file set for HR.MY_JOB is:
  /tmp/expdata/expdp.dmp
Job "HR"."MY_JOB" successfully completed at 09:07:28

4、建立使用者hr2

-- USER SQL
CREATE USER hr2 IDENTIFIED BY hr2
DEFAULT TABLESPACE "USERS"
TEMPORARY TABLESPACE "TEMP";

-- QUOTAS
ALTER USER hr2 QUOTA UNLIMITED ON USERS;

-- ROLES
GRANT "CONNECT" TO hr2 ;
GRANT "RESOURCE" TO hr2 ;

-- SYSTEM PRIVILEGES
GRANT CREATE ANY INDEX TO hr2 ;
GRANT CREATE VIEW TO hr2 ;
GRANT CREATE SESSION TO hr2 ;

5、匯入

SQL> grant read,write on directory dmpdir to hr2;
SQL> impdp hr2/hr2 schemas=hr remap_schema=hr:hr2 directory=dmpdir dumpfile=expdp.dmp logfile=impdp.log job_name=my_jod2;

Master table "HR2"."MY_JOD2" successfully loaded/unloaded
Starting "HR2"."MY_JOD2":  hr2/******** schemas=hr remap_schema=hr:hr2 directory=dmpdir dumpfile=expdp.dmp logfile=impdp.log job_name=my_jod2
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "HR2"."COUNTRIES"                           6.367 KB      25 rows
. . imported "HR2"."DEPARTMENTS"                         7.007 KB      27 rows
. . imported "HR2"."EMPLOYEES"                           16.80 KB     107 rows
. . imported "HR2"."JOBS"                                6.992 KB      19 rows
. . imported "HR2"."JOB_HISTORY"                         7.054 KB      10 rows
. . imported "HR2"."LOCATIONS"                           8.273 KB      23 rows
. . imported "HR2"."REGIONS"                             5.476 KB       4 rows
. . imported "HR2"."TEST_T"                              6.796 KB      10 rows
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/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
Processing object type SCHEMA_EXPORT/VIEW/VIEW
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "HR2"."MY_JOD2" successfully completed at 09:24:39

remap_schema表示從使用者hr匯入到使用者hr2。