ITPub博客
首页 博客 专栏 学院 社区 IT新媒体评选
发博

首页 > 数据库 > Oracle > expdp impdp Data Pump（数据泵）使用解析

expdp impdp Data Pump（数据泵）使用解析

原创 Oracle 作者：贺子_DBA时代 时间：2014-08-09 13:40:48 0 删除 编辑

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博客 ” ，链接：https://blog.itpub.net/29654823/viewspace-1248344/ ，如需转载，请注明出处，否则将追究法律责任。

0
0
上一篇： 由SQL语句执行过程触发对Oracle体系结构的思考
下一篇： 【12C考题精解】OCP 1z0-060 QUESTION 6: Recovery of a Tablespace in the PDB
请登录后发表评论 登录
全部评论
贺子_DBA时代
专家姓名：刘文贺
10年数据库管理员从业经历，熟悉Oracle、mysql、tidb、oceanbase、redis、 mongodb 、python等技术栈，持有11g ocm 、mysql 5.7 ocp 、tidb 5.0 ptcp、tidb 6.0 ptca、oceanbase obcp等

注册时间：2014-05-12

  • 博文量
    327
  • 访问量
    2152519

最新文章

支持我们 作者招募 用户协议 FAQ Contact Us 编辑准则

北京盛拓优讯信息技术有限公司. 版权所有  京ICP备16024965号-8 北京公安局海淀分局网监中心备案编号：11010802021510 niuxiaotong@pcpop.com 17352615567 未成年人举报专区

广播电视节目制作经营许可证(京) 字第1234号 中国互联网协会会员

违法和不良信息举报电话:18101376593