在《【EXP/IMP】使用EXP /IMP工具“模糊”导出和导入》(http://space.itpub.net/519536/viewspace-629537)文章中提到EXP 与IMP工具对表的模糊导出与导入功能,EXPDP和IMPDP具有同样的功能。
简单演示,供参考。
注意通配符“_”含义。
1.环境 准备
1)创建6张表,注意表名的规律。
sec@ora10g> create table TEST_SEC as select * from all_objects;
sec@ora10g> create table TEST_SEC1 as select * from all_objects;
sec@ora10g> create table TEST_SEC2 as select * from all_objects;
sec@ora10g> create table T_SEC as select * from all_objects;
sec@ora10g> create table T_SEC1 as select * from all_objects;
sec@ora10g> create table T_SEC2 as select * from all_objects;
2)查看sec用户下所有的表名
sec@ora10g> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
TEST_SEC TABLE
TEST_SEC2 TABLE
TEST_SEC1 TABLE
T_SEC TABLE
T_SEC1 TABLE
T_SEC2 TABLE
6 rows selected.
2.思考一下使用下面的模糊导出后为什么sec用户下所有的表均被包含在内
ora10g@secDB /expdp$ expdp sec/sec directory=expdp_dir dumpfile=sec.dmp tables=T_S%
Export: Release 10.2.0.3.0 - 64bit Production on Saturday, 09 April, 2010 5:55:16
Copyright (c) 2003, 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
Starting "SEC"."SYS_EXPORT_TABLE_01": sec/******** directory=expdp_dir dumpfile=sec.dmp tables=T_S%
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 12 MB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "SEC"."TEST_SEC" 1.013 MB 11678 rows
. . exported "SEC"."TEST_SEC1" 1.013 MB 11678 rows
. . exported "SEC"."TEST_SEC2" 1.013 MB 11678 rows
. . exported "SEC"."T_SEC" 1.013 MB 11678 rows
. . exported "SEC"."T_SEC1" 1.013 MB 11678 rows
. . exported "SEC"."T_SEC2" 1.013 MB 11678 rows
Master table "SEC"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SEC.SYS_EXPORT_TABLE_01 is:
/expdp/sec.dmp
Job "SEC"."SYS_EXPORT_TABLE_01" successfully completed at 05:55:22
这里是一个“美丽的误会”,本意是想备份所有以“T_S”开头的表,但是我们创建的6张表均被囊括在内了。根本原因是此处的下划线“_”表示的是通配符!
3.仅备份以“TEST”开始的表
ora10g@secDB /expdp$ expdp sec/sec directory=expdp_dir dumpfile=sec.dmp tables=TEST%
Export: Release 10.2.0.3.0 - 64bit Production on Saturday, 09 April, 2010 5:55:51
Copyright (c) 2003, 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
Starting "SEC"."SYS_EXPORT_TABLE_01": sec/******** directory=expdp_dir dumpfile=sec.dmp tables=TEST%
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 6 MB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "SEC"."TEST_SEC" 1.013 MB 11678 rows
. . exported "SEC"."TEST_SEC1" 1.013 MB 11678 rows
. . exported "SEC"."TEST_SEC2" 1.013 MB 11678 rows
Master table "SEC"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SEC.SYS_EXPORT_TABLE_01 is:
/expdp/sec.dmp
Job "SEC"."SYS_EXPORT_TABLE_01" successfully completed at 05:55:56
OK,成功。
4.IMPDP的模糊导入功能
1)生成一份包含所有表的备份文件
ora10g@secDB /expdp$ expdp sec/sec directory=expdp_dir dumpfile=sec.dmp tables=%
2)清除sec用户下的所有表
drop table TEST_SEC purge;
drop table TEST_SEC1 purge;
drop table TEST_SEC2 purge;
drop table T_SEC purge;
drop table T_SEC1 purge;
drop table T_SEC2 purge;
3)模糊导入以“TEST”开头的表
ora10g@secDB /expdp$ impdp sec/sec directory=expdp_dir dumpfile=sec.dmp tables=TEST%
Import: Release 10.2.0.3.0 - 64bit Production on Saturday, 09 April, 2010 6:08:41
Copyright (c) 2003, 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
Master table "SEC"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "SEC"."SYS_IMPORT_TABLE_01": sec/******** directory=expdp_dir dumpfile=sec.dmp tables=TEST%
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "SEC"."TEST_SEC" 1.018 MB 11716 rows
. . imported "SEC"."TEST_SEC1" 1.018 MB 11717 rows
. . imported "SEC"."TEST_SEC2" 1.018 MB 11718 rows
Job "SEC"."SYS_IMPORT_TABLE_01" successfully completed at 06:08:44
4)验证导入的数据
sys@ora10g> conn sec/sec
Connected.
sec@ora10g> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
TEST_SEC TABLE
TEST_SEC1 TABLE
TEST_SEC2 TABLE
OK,已经实现按需导入。
5.小结
充分发掘既有工具的潜在功能是提高生命质量的前提保证。人类之所以伟大,就是因为我们会灵活使用各种优秀的工具:)
Good luck.
secooler
10.04.09
-- The End --
简单演示,供参考。
注意通配符“_”含义。
1.环境 准备
1)创建6张表,注意表名的规律。
sec@ora10g> create table TEST_SEC as select * from all_objects;
sec@ora10g> create table TEST_SEC1 as select * from all_objects;
sec@ora10g> create table TEST_SEC2 as select * from all_objects;
sec@ora10g> create table T_SEC as select * from all_objects;
sec@ora10g> create table T_SEC1 as select * from all_objects;
sec@ora10g> create table T_SEC2 as select * from all_objects;
2)查看sec用户下所有的表名
sec@ora10g> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
TEST_SEC TABLE
TEST_SEC2 TABLE
TEST_SEC1 TABLE
T_SEC TABLE
T_SEC1 TABLE
T_SEC2 TABLE
6 rows selected.
2.思考一下使用下面的模糊导出后为什么sec用户下所有的表均被包含在内
ora10g@secDB /expdp$ expdp sec/sec directory=expdp_dir dumpfile=sec.dmp tables=T_S%
Export: Release 10.2.0.3.0 - 64bit Production on Saturday, 09 April, 2010 5:55:16
Copyright (c) 2003, 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
Starting "SEC"."SYS_EXPORT_TABLE_01": sec/******** directory=expdp_dir dumpfile=sec.dmp tables=T_S%
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 12 MB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "SEC"."TEST_SEC" 1.013 MB 11678 rows
. . exported "SEC"."TEST_SEC1" 1.013 MB 11678 rows
. . exported "SEC"."TEST_SEC2" 1.013 MB 11678 rows
. . exported "SEC"."T_SEC" 1.013 MB 11678 rows
. . exported "SEC"."T_SEC1" 1.013 MB 11678 rows
. . exported "SEC"."T_SEC2" 1.013 MB 11678 rows
Master table "SEC"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SEC.SYS_EXPORT_TABLE_01 is:
/expdp/sec.dmp
Job "SEC"."SYS_EXPORT_TABLE_01" successfully completed at 05:55:22
这里是一个“美丽的误会”,本意是想备份所有以“T_S”开头的表,但是我们创建的6张表均被囊括在内了。根本原因是此处的下划线“_”表示的是通配符!
3.仅备份以“TEST”开始的表
ora10g@secDB /expdp$ expdp sec/sec directory=expdp_dir dumpfile=sec.dmp tables=TEST%
Export: Release 10.2.0.3.0 - 64bit Production on Saturday, 09 April, 2010 5:55:51
Copyright (c) 2003, 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
Starting "SEC"."SYS_EXPORT_TABLE_01": sec/******** directory=expdp_dir dumpfile=sec.dmp tables=TEST%
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 6 MB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "SEC"."TEST_SEC" 1.013 MB 11678 rows
. . exported "SEC"."TEST_SEC1" 1.013 MB 11678 rows
. . exported "SEC"."TEST_SEC2" 1.013 MB 11678 rows
Master table "SEC"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SEC.SYS_EXPORT_TABLE_01 is:
/expdp/sec.dmp
Job "SEC"."SYS_EXPORT_TABLE_01" successfully completed at 05:55:56
OK,成功。
4.IMPDP的模糊导入功能
1)生成一份包含所有表的备份文件
ora10g@secDB /expdp$ expdp sec/sec directory=expdp_dir dumpfile=sec.dmp tables=%
2)清除sec用户下的所有表
drop table TEST_SEC purge;
drop table TEST_SEC1 purge;
drop table TEST_SEC2 purge;
drop table T_SEC purge;
drop table T_SEC1 purge;
drop table T_SEC2 purge;
3)模糊导入以“TEST”开头的表
ora10g@secDB /expdp$ impdp sec/sec directory=expdp_dir dumpfile=sec.dmp tables=TEST%
Import: Release 10.2.0.3.0 - 64bit Production on Saturday, 09 April, 2010 6:08:41
Copyright (c) 2003, 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
Master table "SEC"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "SEC"."SYS_IMPORT_TABLE_01": sec/******** directory=expdp_dir dumpfile=sec.dmp tables=TEST%
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "SEC"."TEST_SEC" 1.018 MB 11716 rows
. . imported "SEC"."TEST_SEC1" 1.018 MB 11717 rows
. . imported "SEC"."TEST_SEC2" 1.018 MB 11718 rows
Job "SEC"."SYS_IMPORT_TABLE_01" successfully completed at 06:08:44
4)验证导入的数据
sys@ora10g> conn sec/sec
Connected.
sec@ora10g> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
TEST_SEC TABLE
TEST_SEC1 TABLE
TEST_SEC2 TABLE
OK,已经实现按需导入。
5.小结
充分发掘既有工具的潜在功能是提高生命质量的前提保证。人类之所以伟大,就是因为我们会灵活使用各种优秀的工具:)
Good luck.
secooler
10.04.09
-- The End --
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/519536/viewspace-631778/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/519536/viewspace-631778/