expdp impdp Data Pump（資料泵）使用解析

賀子_DBA時代發表於2014-08-09

1準備工作
SYS@ENMOEDU> create or replace directory d_home as '/home/oracle';         #建立目錄
SYS@ENMOEDU> grant read,write on directory d_home to hr;                    #把剛才建立的目錄的讀寫許可權賦給hr
Grant succeeded.
2.匯出表中某些行
[oracle@ENMOEDU ~]$ expdp hr/hr directory=d_home dumpfile=liuwenhe.dmp  tables=employees query="'where employee_id=201'";                

Export: Release 11.2.0.3.0 - Production on Fri Aug 8 22:07:33 2014 

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. 

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production 
With the Partitioning, OLAP, Data Mining and Real Application Testing options 
Starting "HR"."SYS_EXPORT_TABLE_01": hr/******** directory=d_home dumpfile=liuwenhe.dmp tables=employees query='where employee_id=201' 
Estimate in progress using BLOCKS method... 
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA 
Total estimation using BLOCKS method: 64 KB 
Processing object type TABLE_EXPORT/TABLE/TABLE 
Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT 
Processing object type TABLE_EXPORT/TABLE/COMMENT 
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/TRIGGER 
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS 
. . exported "HR"."EMPLOYEES" 9.328 KB 1 rows 
Master table "HR"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded 
****************************************************************************** 
Dump file set for HR.SYS_EXPORT_TABLE_01 is: 
/home/oracle/liuwenhe.dmp 
Job "HR"."SYS_EXPORT_TABLE_01" successfully completed at 22:08:02
[oracle@ENMOEDU ~]$ ls
liuwenhe.dmp      export.log  #沒有指定生成log,那麼會生成個預設的log，下次會覆蓋；
 也可以： [oracle@ENMOEDU ~]$ vi liuwenhe 
userid=hr/hr 
directory=d_home 
dumpfile=liuwenhe 
logfile=liuwenhe.log 
tables=employees 
query='where employee_id=203'
[oracle@ENMOEDU ~]$ expdp parfile=liuwenhe       也可以成
2.匯出表；
[oracle@ENMOEDU ~]$ expdp hr/hr directory=d_home dumpfile=hezi.dmp tables=employees; 

Export: Release 11.2.0.3.0 - Production on Fri Aug 8 22:26:52 2014 

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. 

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production 
With the Partitioning, OLAP, Data Mining and Real Application Testing options 
Starting "HR"."SYS_EXPORT_TABLE_01": hr/******** directory=d_home dumpfile=hezi.dmp tables=employees 
Estimate in progress using BLOCKS method... 
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA 
Total estimation using BLOCKS method: 64 KB 
Processing object type TABLE_EXPORT/TABLE/TABLE 
Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT 
Processing object type TABLE_EXPORT/TABLE/COMMENT 
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/TRIGGER 
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS 
. . exported "HR"."EMPLOYEES" 16.80 KB 107 rows 
Master table "HR"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded 
****************************************************************************** 
Dump file set for HR.SYS_EXPORT_TABLE_01 is: 
/home/oracle/hezi.dmp 
Job "HR"."SYS_EXPORT_TABLE_01" successfully completed at 22:26:59
3.匯出schema             #可以理解為是使用者所建立的所有資料物件
[oracle@ENMOEDU ~]$ expdp hr/hr directory=d_home dumpfile=hr.dump schemas=hr; 

Export: Release 11.2.0.3.0 - Production on Fri Aug 8 22:29:46 2014 

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. 

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production 
With the Partitioning, OLAP, Data Mining and Real Application Testing options 
Starting "HR"."SYS_EXPORT_SCHEMA_01": hr/******** directory=d_home dumpfile=hr.dump schemas=hr 
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/GRANT/OWNER_GRANT/OBJECT_GRANT 
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/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/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" 5.867 KB 2 rows 
Master table "HR"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded 
****************************************************************************** 
Dump file set for HR.SYS_EXPORT_SCHEMA_01 is: 
/home/oracle/hr.dump 
Job "HR"."SYS_EXPORT_SCHEMA_01" successfully completed at 22:30:32 
4.注意要使用DBA角色的使用者匯出，否則只匯出本schema的物件。
[oracle@ENMOEDU ~]$ expdp system/oracle directory=d_home dumpfile=hezi1.dmp logfile=hezi1.log tablespaces=system 

Export: Release 11.2.0.3.0 - Production on Fri Aug 8 22:47:17 2014 

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. 

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production 
With the Partitioning, OLAP, Data Mining and Real Application Testing options 
Starting "SYSTEM"."SYS_EXPORT_TABLESPACE_01": system/******** directory=d_home dumpfile=hezi1.dmp logfile=hezi1.log tablespaces=system 
Estimate in progress using BLOCKS method... 
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA 
Total estimation using BLOCKS method: 320 KB 
Processing object type TABLE_EXPORT/TABLE/TABLE 
Processing object type TABLE_EXPORT/TABLE/PRE_TABLE_ACTION 
Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT 
Processing object type TABLE_EXPORT/TABLE/COMMENT 
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/TRIGGER 
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS 
Processing object type TABLE_EXPORT/TABLE/POST_TABLE_ACTION 
. . exported "SYSTEM"."REPCAT$_AUDIT_ATTRIBUTE" 6.328 KB 2 rows 
. . exported "SYSTEM"."REPCAT$_OBJECT_TYPES" 6.882 KB 28 rows 
. . exported "SYSTEM"."REPCAT$_RESOLUTION_METHOD" 5.835 KB 19 rows 
. . exported "SYSTEM"."REPCAT$_TEMPLATE_STATUS" 5.484 KB 3 rows 
. . exported "SYSTEM"."REPCAT$_TEMPLATE_TYPES" 6.289 KB 2 rows 
. . exported "OUTLN"."OL$" 0 KB 0 rows 
. . exported "SYSTEM"."REPCAT$_USER_AUTHORIZATIONS" 0 KB 0 rows 
. . exported "SYSTEM"."REPCAT$_USER_PARM_VALUES" 0 KB 0 rows 
. . exported "SYSTEM"."SQLPLUS_PRODUCT_PROFILE" 0 KB 0 rows 
Master table "SYSTEM"."SYS_EXPORT_TABLESPACE_01" successfully loaded/unloaded 
****************************************************************************** 
Dump file set for SYSTEM.SYS_EXPORT_TABLESPACE_01 is: 
/home/oracle/hezi1.dmp 
Job "SYSTEM"."SYS_EXPORT_TABLESPACE_01" successfully completed at 22:47:37 
5.匯入表   前提是有匯出的表       
[oracle@ENMOEDU ~]$ impdp system/oracle directory=d_home dumpfile=hezi.dmp remap_schema=hr:hr;          #remap_schema=第一個hr是這個表原來屬於者，第二個hr是倒進去後屬於者，

Import: Release 11.2.0.3.0 - Production on Fri Aug 8 23:13:52 2014 

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. 

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production 
With the Partitioning, OLAP, Data Mining and Real Application Testing options 
Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded 
Starting "SYSTEM"."SYS_IMPORT_FULL_01": system/******** directory=d_home dumpfile=hezi.dmp remap_schema=hr:hr 
Processing object type TABLE_EXPORT/TABLE/TABLE 
ORA-39151: Table "HR"."EMPLOYEES" exists. All dependent metadata and data will be skipped due to table_exists_action of skip 
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA 
Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT 
Processing object type TABLE_EXPORT/TABLE/COMMENT 
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/TRIGGER 
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS 
Job "SYSTEM"."SYS_IMPORT_FULL_01" completed with 1 error(s) at 23:13:54
6.匯入schema   
SYS@ENMOEDU> drop user hr cascade;       #先把hr的schema 刪掉
User dropped. 
SYS@ENMOEDU> create user hr identified by hr;        
User created. 
SYS@ENMOEDU> grant connect,resource to hr; 
Grant succeeded.
oracle@ENMOEDU ~]$ impdp system/oracle directory=d_home dumpfile=hr.dump schemas=hr remap_schema=hr:hr    #remap_schemas 這個引數可寫可不寫，它就是想指定後來匯入的使用者的名字，如果沒有指定，則會重新建立一個和原來匯出時的名字一樣的使用者，如果你有冒號後面的使用者，則它會報錯提示不能重建，

Import: Release 11.2.0.3.0 - Production on Sat Aug 9 11:53:12 2014 

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. 

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production 
With the Partitioning, OLAP, Data Mining and Real Application Testing options 
Master table "SYSTEM"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded 
Starting "SYSTEM"."SYS_IMPORT_SCHEMA_01": system/******** directory=d_home dumpfile=hr.dump schemas=hr remap_schema=hr:hr 
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 "HR"."COUNTRIES" 6.367 KB 25 rows 
. . imported "HR"."DEPARTMENTS" 7.007 KB 27 rows 
. . imported "HR"."EMPLOYEES" 16.80 KB 107 rows 
. . imported "HR"."JOBS" 6.992 KB 19 rows 
. . imported "HR"."JOB_HISTORY" 7.054 KB 10 rows 
. . imported "HR"."LOCATIONS" 8.273 KB 23 rows 
. . imported "HR"."REGIONS" 5.476 KB 4 rows 
. . imported "HR"."TEST" 5.867 KB 2 rows 
Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT 
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/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/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 "SYSTEM"."SYS_IMPORT_SCHEMA_01" successfully completed at 11:53:25
7.匯入表空間。
[oracle@ENMOEDU ~]$ impdp system/oracle directory=d_home dumpfile=hezi1.dmp tablespaces=system;    

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

相關文章