绑定完请刷新页面
取消
刷新

分享好友

×
取消 复制
【EXP/IMP】使用EXP /IMP工具“模糊”导出和导入
2020-02-10 20:02:31
EXP/IMP这两个逻辑备份和恢复工具想必大家都已经达到熟练应用的境界,不过其中隐藏的“模糊”导出和“模糊”导入功能也许依然会使您眼前一亮。

1.在全新的用户sec下创建四张表,同时给每张表中初始化一条数据
sec@ora10g> select * from tab;

no rows selected

sec@ora10g> create table t_1 (x int);
sec@ora10g> insert into  t_1 values (1);
sec@ora10g> create table t_2 (x int);
sec@ora10g> insert into  t_2 values (2);
sec@ora10g> create table sec_1secooler (x int);
sec@ora10g> insert into  sec_1secooler values (3);
sec@ora10g> create table sec_2secooler (x int);
sec@ora10g> insert into  sec_2secooler values (4);
sec@ora10g> commit;

sec@ora10g> select * from tab;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
T_1                            TABLE
T_2                            TABLE
SEC_1SECOOLER                  TABLE
SEC_2SECOOLER                  TABLE

2.Windows环境下模糊导出示例
1)模糊导出以“T_”开头的表
C:\>exp sec/sec@conn_to_secDB file=sec.dmp tables=(t_%)

Export: Release 10.2.0.3.0 - Production on Mon Mar 15 21:25:51 2010

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


Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine options
Export done in ZHS16GBK character set and UTF8 NCHAR character set
server uses AL32UTF8 character set (possible charset conversion)

About to export specified tables via Conventional Path ...
. . exporting table                            T_1          1 rows exported
. . exporting table                            T_2          1 rows exported
Export terminated successfully without warnings.


2)模糊导出以“SECOOLER”结尾的表
C:\>exp sec/sec@conn_to_secDB file=sec.dmp tables=(%secooler)

Export: Release 10.2.0.3.0 - Production on Mon Mar 15 21:25:57 2010

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


Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine options
Export done in ZHS16GBK character set and UTF8 NCHAR character set
server uses AL32UTF8 character set (possible charset conversion)

About to export specified tables via Conventional Path ...
. . exporting table                  SEC_1SECOOLER          1 rows exported
. . exporting table                  SEC_2SECOOLER          1 rows exported
Export terminated successfully without warnings.


3.Windows环境下模糊导入示例
1)先生成一个包含所有表的备份(同样使用模糊匹配的方法)
C:\>exp sec/sec@conn_to_secDB file=sec.dmp tables=%

Export: Release 10.2.0.3.0 - Production on Mon Mar 15 21:41:49 2010

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


Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine options
Export done in ZHS16GBK character set and UTF8 NCHAR character set
server uses AL32UTF8 character set (possible charset conversion)

About to export specified tables via Conventional Path ...
. . exporting table                  SEC_2SECOOLER          1 rows exported
. . exporting table                  SEC_1SECOOLER          1 rows exported
. . exporting table                            T_2          1 rows exported
. . exporting table                            T_1          1 rows exported
Export terminated successfully without warnings.


2)将备份中的以“T_”开头的表导入到secooler用户中
C:\>imp secooler/secooler@conn_to_secDB file=sec.dmp tables=t_% fromuser=sec touser=secooler

Import: Release 10.2.0.3.0 - Production on Mon Mar 15 21:43:41 2010

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


Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine options

Export file created by EXPORT:V10.02.01 via conventional path

Warning: the objects were exported by SEC, not by you

import done in ZHS16GBK character set and UTF8 NCHAR character set
import server uses AL32UTF8 character set (possible charset conversion)
. importing SEC's objects into SECOOLER
. . importing table                          "T_2"          1 rows imported
. . importing table                          "T_1"          1 rows imported
Import terminated successfully without warnings.


OK,目的达到。

4.Linux环境下模糊导出示例
1)模糊导出以“T_”开头的表
ora10g@secDB /exp$ exp sec/sec file=sec.dmp log=sec.log tables=\(t%\)

Export: Release 10.2.0.3.0 - Production on Mon Mar 15 13:49:26 2010

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


Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine options
Export done in AL32UTF8 character set and UTF8 NCHAR character set

About to export specified tables via Conventional Path ...
. . exporting table                            T_1          1 rows exported
. . exporting table                            T_2          1 rows exported
Export terminated successfully without warnings.


2)模糊导出以“SECOOLER”结尾的表
ora10g@secDB /exp$ exp sec/sec file=sec.dmp log=sec.log tables=\(%secooler\)

Export: Release 10.2.0.3.0 - Production on Mon Mar 15 13:50:21 2010

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


Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine options
Export done in AL32UTF8 character set and UTF8 NCHAR character set

About to export specified tables via Conventional Path ...
. . exporting table                  SEC_1SECOOLER          1 rows exported
. . exporting table                  SEC_2SECOOLER          1 rows exported
Export terminated successfully without warnings.

在Linux环境中导出数据如果使用的是一行命令(包含括号),需要注意括号的转义处理。
怎么规避这个括号的转义呢?
第一种方法:
不使用括号,效果是一样的
ora10g@secDB /exp$ exp sec/sec file=sec.dmp log=sec.log tables=%secooler,%1%

Export: Release 10.2.0.3.0 - Production on Mon Mar 15 13:55:51 2010

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


Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine options
Export done in AL32UTF8 character set and UTF8 NCHAR character set

About to export specified tables via Conventional Path ...
. . exporting table                  SEC_1SECOOLER          1 rows exported
. . exporting table                  SEC_2SECOOLER          1 rows exported
. . exporting table                            T_1          1 rows exported
Export terminated successfully without warnings.

第二种方法:
将带括号的参数写入到参数文件,使用parfile参数调用。
ora10g@secDB /exp$ vi sec.par
userid=sec/sec
file=sec.dmp
log=sec.log
tables=(%SEcooler,%1%)
~
~

ora10g@secDB /exp$ exp parfile=sec.par

Export: Release 10.2.0.3.0 - Production on Mon Mar 15 13:57:57 2010

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


Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine options
Export done in AL32UTF8 character set and UTF8 NCHAR character set

About to export specified tables via Conventional Path ...
. . exporting table                  SEC_1SECOOLER          1 rows exported
. . exporting table                  SEC_2SECOOLER          1 rows exported
. . exporting table                            T_1          1 rows exported
Export terminated successfully without warnings.


5.Linux环境下模糊导入示例
将sec.dmp中的包含关键字“1”的表导入到secooler用户。
ora10g@secDB /exp$ imp secooler/secooler file=sec.dmp ignore=y tables=%1% fromuser=sec touser=secooler

Import: Release 10.2.0.3.0 - Production on Mon Mar 15 13:59:50 2010

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


Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine options

Export file created by EXPORT:V10.02.01 via conventional path

Warning: the objects were exported by SEC, not by you

import done in AL32UTF8 character set and UTF8 NCHAR character set
. importing SEC's objects into SECOOLER
. . importing table                "SEC_1SECOOLER"          1 rows imported
. . importing table                          "T_1"          1 rows imported
Import terminated successfully without warnings.


6.小贴士
无论在Windows环境还是Linux环境,tables参数内容都不区分大小写
1)验证Windows环境下执行效果
C:\>exp sec/sec@conn_to_secDB file=sec.dmp tables=%SECooler

Export: Release 10.2.0.3.0 - Production on Mon Mar 15 21:39:58 2010

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


Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine options
Export done in ZHS16GBK character set and UTF8 NCHAR character set
server uses AL32UTF8 character set (possible charset conversion)

About to export specified tables via Conventional Path ...
. . exporting table                  SEC_1SECOOLER          1 rows exported
. . exporting table                  SEC_2SECOOLER          1 rows exported
Export terminated successfully without warnings.


2)验证Linux环境下执行效果
ora10g@secDB /exp$ exp sec/sec file=sec.dmp log=sec.log tables=\(%SECooler\)

Export: Release 10.2.0.3.0 - Production on Mon Mar 15 13:50:33 2010

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


Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine options
Export done in AL32UTF8 character set and UTF8 NCHAR character set

About to export specified tables via Conventional Path ...
. . exporting table                  SEC_1SECOOLER          1 rows exported
. . exporting table                  SEC_2SECOOLER          1 rows exported
Export terminated successfully without warnings.

7.小结
使用EXP工具可以完成模糊导出,使用IMP命令亦可以完成模糊导入。这两个小技巧给我们日常备份和恢复带来非常大的裨益的同时,也提高了我们备份恢复的灵活性。
分享在此,供参考。

Good luck.

secooler
10.03.15

-- The End --

分享好友

分享这个小栈给你的朋友们,一起进步吧。

OCM联盟
创建时间:2019-12-27 14:04:54
OCM联盟(OCMU – Oracle Certified Master Union)是一群有着共同理想,共同志向的DBA的家。 ⚠️该小栈仅限ocm成员入驻!审核制! Oracle Certified Master (OCM) -Oracle认证大师,是Oracle认证的最高级别,是对数据库从业人员的技术、知识和操作技能的最高级别的认可。Oracle OCM是解决最困难的技术难题和最复杂的系统故障的最佳Oracle专家人选,也是IT行业衡量IT专家和经理人的最高专业程度及经验的基准。
展开
订阅须知

• 所有用户可根据关注领域订阅专区或所有专区

• 付费订阅:虚拟交易,一经交易不退款;若特殊情况,可3日内客服咨询

• 专区发布评论属默认订阅所评论专区(除付费小栈外)

栈主、嘉宾

查看更多
  • 侯圣文@secooler
    栈主

小栈成员

查看更多
  • gaokeke123
  • ?
  • 山中老狐狸
  • 飘絮絮絮丶
戳我,来吐槽~