1. 首页
  2. 文章中心
  3. 数据库

【EXP/IMP】使用EXP /IMP工具“模糊”导出和导入

63 阅读 0 评论
我是靠谱客的博主 大力世界，最近开发中收集的这篇文章主要介绍【EXP/IMP】使用EXP /IMP工具“模糊”导出和导入，觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

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 --

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/519536/viewspace-629537/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/519536/viewspace-629537/

最后

以上就是大力世界为你收集整理的【EXP/IMP】使用EXP /IMP工具“模糊”导出和导入的全部内容，希望文章能够帮你解决【EXP/IMP】使用EXP /IMP工具“模糊”导出和导入所遇到的程序开发问题。

如果觉得靠谱客网站的内容还不错，欢迎将靠谱客网站推荐给程序员好友。

本图文内容来源于网友提供，作为学习参考使用，或来自网络收集整理，版权属于原作者所有。
点赞(42)

相关文章

oracle分区表分区导出,Oracle 分区表 导出导入 迁移(转)
oracle分区表分区导出,Oracle 分区表 导出导入 迁移(转)
Ora-12899 字符集问题
Ora-12899 字符集问题
oracle从gbk导出后导入utf8,zhs16gbk的dmp导入到utf8字符的库中
oracle从gbk导出后导入utf8,zhs16gbk的dmp导入到utf8字符的库中
Exp Imp 导入导出常见问题解决方法
Exp Imp 导入导出常见问题解决方法
【EXP/IMP】使用EXP /IMP工具“模糊”导出和导入
【EXP/IMP】使用EXP /IMP工具“模糊”导出和导入
Oracle 导出和导入
Oracle 导出和导入
imp导入/exp导出，以及自己的实践参考一Exp/imp 导出导入工具的使用参考二：下面介绍的是导入导出的实例。我自己的实践：
imp导入/exp导出，以及自己的实践参考一Exp/imp 导出导入工具的使用参考二：下面介绍的是导入导出的实例。我自己的实践：
使用mysqldump 把GBK编码的数据库转换成UTF8存储
使用mysqldump 把GBK编码的数据库转换成UTF8存储

评论列表共有 0 条评论

立即
投稿
返回
顶部