oracle导出dmp表名模糊,【EXP/IMP】使用EXP /IMP工具“模糊”导出和导入

EXP/IMP这两个逻辑备份和恢复工具想必大家都已经达到熟练应用的境界，不过其中隐藏的“模糊”导出和“模糊”导入功能也许依然会使您眼前一亮。1.在全新的用户sec下创建四张表，同时给每张表中初始化一条数据sec@ora10g> select * from tab;no rows selectedsec@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                            TABLET_2                            TABLESEC_1SECOOLER                  TABLESEC_2SECOOLER                  TABLE2.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 2010Copyright (c) 1982, 2005, Oracle.  All rights reserved.Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit ProductionWith the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine optionsExport done in ZHS16GBK character set and UTF8 NCHAR character setserver 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 exportedExport 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 2010Copyright (c) 1982, 2005, Oracle.  All rights reserved.Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit ProductionWith the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine optionsExport done in ZHS16GBK character set and UTF8 NCHAR character setserver 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 exportedExport 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 2010Copyright (c) 1982, 2005, Oracle.  All rights reserved.Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit ProductionWith the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine optionsExport done in ZHS16GBK character set and UTF8 NCHAR character setserver 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 exportedExport terminated successfully without warnings.2)将备份中的以“T_”开头的表导入到secooler用户中C:\>imp secooler/secooler@conn_to_secDB file=sec.dmp tables=t_% fromuser=sec touser=secoolerImport: Release 10.2.0.3.0 - Production on Mon Mar 15 21:43:41 2010Copyright (c) 1982, 2005, Oracle.  All rights reserved.Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit ProductionWith the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine optionsExport file created by EXPORT:V10.02.01 via conventional pathWarning: the objects were exported by SEC, not by youimport done in ZHS16GBK character set and UTF8 NCHAR character setimport 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 importedImport 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 2010Copyright (c) 1982, 2005, Oracle.  All rights reserved.Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit ProductionWith the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine optionsExport done in AL32UTF8 character set and UTF8 NCHAR character setAbout to export specified tables via Conventional Path .... . exporting table                            T_1          1 rows exported. . exporting table                            T_2          1 rows exportedExport 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 2010Copyright (c) 1982, 2005, Oracle.  All rights reserved.Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit ProductionWith the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine optionsExport done in AL32UTF8 character set and UTF8 NCHAR character setAbout to export specified tables via Conventional Path .... . exporting table                  SEC_1SECOOLER          1 rows exported. . exporting table                  SEC_2SECOOLER          1 rows exportedExport 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 2010Copyright (c) 1982, 2005, Oracle.  All rights reserved.Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit ProductionWith the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine optionsExport done in AL32UTF8 character set and UTF8 NCHAR character setAbout 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 exportedExport terminated successfully without warnings.第二种方法：将带括号的参数写入到参数文件，使用parfile参数调用。ora10g@secDB /exp$ vi sec.paruserid=sec/secfile=sec.dmplog=sec.logtables=(%SEcooler,%1%)~~ora10g@secDB /exp$ exp parfile=sec.parExport: Release 10.2.0.3.0 - Production on Mon Mar 15 13:57:57 2010Copyright (c) 1982, 2005, Oracle.  All rights reserved.Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit ProductionWith the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine optionsExport done in AL32UTF8 character set and UTF8 NCHAR character setAbout 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 exportedExport 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=secoolerImport: Release 10.2.0.3.0 - Production on Mon Mar 15 13:59:50 2010Copyright (c) 1982, 2005, Oracle.  All rights reserved.Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit ProductionWith the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine optionsExport file created by EXPORT:V10.02.01 via conventional pathWarning: the objects were exported by SEC, not by youimport 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 importedImport terminated successfully without warnings.6.小贴士无论在Windows环境还是Linux环境，tables参数内容都不区分大小写1)验证Windows环境下执行效果C:\>exp sec/sec@conn_to_secDB file=sec.dmp tables=%SECoolerExport: Release 10.2.0.3.0 - Production on Mon Mar 15 21:39:58 2010Copyright (c) 1982, 2005, Oracle.  All rights reserved.Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit ProductionWith the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine optionsExport done in ZHS16GBK character set and UTF8 NCHAR character setserver 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 exportedExport 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 2010Copyright (c) 1982, 2005, Oracle.  All rights reserved.Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit ProductionWith the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine optionsExport done in AL32UTF8 character set and UTF8 NCHAR character setAbout to export specified tables via Conventional Path .... . exporting table                  SEC_1SECOOLER          1 rows exported. . exporting table                  SEC_2SECOOLER          1 rows exportedExport terminated successfully without warnings.7.小结使用EXP工具可以完成模糊导出，使用IMP命令亦可以完成模糊导入。这两个小技巧给我们日常备份和恢复带来非常大的裨益的同时，也提高了我们备份恢复的灵活性。分享在此，供参考。Good luck.secooler10.03.15-- The End --