你好,游客 登录 注册 搜索
背景:
阅读新闻

Oracle 数据泵 content=data_only

[日期:2015-07-08] 来源:Linux社区  作者:Linux [字体: ]

下面看一个试验 ,验证的是只导出数据后,可以恢复,就算是表结构已经变化了,他也能把相应的列恢复,

1)SQL> desc liuwenhe.liuwenhe;

 Name                                      Null?    Type

 ----------------------------------------- -------- ----------------------------

 X                                        NOT NULL NUMBER(38)

 Y                                                  NUMBER(38)
2)SQL> select * from liuwenhe.liuwenhe;

        X          Y

---------- ----------

        1
        3
        3
        3
        2
        4
        4
        5
        5
        8
        6
        9
 
6 rows selected.
3)[Oracle@rac1 expdp]$ expdp system/manager123 directory=bak dumpfile=hhhf.dmp tables=liuwenhe.liuwenhe;

Export: Release 11.2.0.3.0 - Production on Mon Jul 6 11:52:56 2015

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 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_TABLE_03":  system/******** directory=bak dumpfile=hhhf.dmp tables=liuwenhe.liuwenhe
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/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/STATISTICS/TABLE_STATISTICS
. . exported "LIUWENHE"."LIUWENHE"                      5.492 KB      6 rows
Master table "SYSTEM"."SYS_EXPORT_TABLE_03" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TABLE_03 is:
  /backup/expdp/hhhf.dmp
Job "SYSTEM"."SYS_EXPORT_TABLE_03" successfully completed at 11:53:10
4)SQL> alter table liuwenhe.liuwenhe  drop column y;
 
Table altered.
5)SQL> truncate table liuwenhe.liuwenhe;
 
Table truncated.
6)[oracle@rac1 expdp]$ impdp system/manager123 directory=bak dumpfile=hhhf.dmp tables=liuwenhe.liuwenhe  content=data_only;
 
Import: Release 11.2.0.3.0 - Production on Mon Jul 6 11:55:07 2015

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 - 64bit Production

With the Partitioning, Real Application Clusters, OLAP, Data Mining

and Real Application Testing options

Master table "SYSTEM"."SYS_IMPORT_TABLE_04" successfully loaded/unloaded

Starting "SYSTEM"."SYS_IMPORT_TABLE_04":  system/******** directory=bak dumpfile=hhhf.dmp tables=liuwenhe.liuwenhe content=data_only

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

. . imported "LIUWENHE"."LIUWENHE"                      5.492 KB      6 rows

Job "SYSTEM"."SYS_IMPORT_TABLE_04" successfully completed at 11:55:13

7)SQL> select * from liuwenhe.liuwenhe;

        X

----------

        1

        2

        3

        4

        5

        6

6 rows selected.

总结:Oracle数据泵,逻辑备份和恢复工具,他直接在逻辑从面操作,不能想象成单纯的insert , 而且只导出数据,然后恢复数据时候,可以不用写remap_tablespace这个参数 ,tables=liuwenhe.liuwenhe  这里要是没有写前缀liuwenhe,而写成tables=liuwenhe,系统就认为是system用户下的liuwenhe表,而出错。

更多Oracle相关信息见Oracle 专题页面 http://www.linuxidc.com/topicnews.aspx?tid=12

本文永久更新链接地址http://www.linuxidc.com/Linux/2015-07/119581.htm

linux
相关资讯       Oracle数据泵 
本文评论   查看全部评论 (0)
表情: 表情 姓名: 字数

       

评论声明
  • 尊重网上道德,遵守中华人民共和国的各项有关法律法规
  • 承担一切因您的行为而直接或间接导致的民事或刑事法律责任
  • 本站管理人员有权保留或删除其管辖留言中的任意内容
  • 本站有权在网站内转载或引用您的评论
  • 参与本评论即表明您已经阅读并接受上述条款