ORACLE expdp导出/导入以XXX开头的所有表
场景需求：
需要将DB1 数据库中SYS开头的表，全部导入到DB2 数据库中。但是DB2 数据库已经存在部分SYS_开头的表，这部分数据不需要覆盖。
1. 环境检查
1.1 源端查看sys_开头的表数量
SQL> select COUNT(*) from dba_tables where table_name like 'SYS_%' and owner='SXC';
COUNT(*)
----------
501
1.2 目标端查看sys_开头的表数量
SQL> select COUNT(*) from dba_tables where table_name like 'SYS_%' and owner='SXC';
COUNT(*)
----------
37
目标端DB2 缺少很多SYS_开头的表，如果用常规方式，expdp 写上tables=t1,t2,t3… 方式，需要写很多，比较麻烦。
简便写法，可以使用expdp tables=xxx%的方式，我们需要导出SYS开头的表，只需要写上tables=SYS_%即可，这样就比较方便。
2. 源端导出
[oracle@DB1 backup]$ expdp \'/ AS SYSDBA\' directory=EXPDP_DIR2 dumpfile=sys_tables.dmp tables=SXC.SYS_% logfile=sys_tables_expdp-tab.log cluster=n Export: Release 11.2.0.4.0 - Production on Fri Jul 22 13:18:42 2022 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options Starting "SYS"."SYS_EXPORT_TABLE_01": "/******** AS SYSDBA" directory=EXPDP_DIR2 dumpfile=sys_tables.dmp tables=SXC.SYS_% logfile=sys_tables_expdp-tab.log cluster=n Estimate in progress using BLOCKS method... Processing object type TABLE_EXPORT/TABLE/TABLE_DATA Total estimation using BLOCKS method: 28.17 GB Processing object type TABLE_EXPORT/TABLE/TABLE Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT Processing object type TABLE_EXPORT/TABLE/COMMENT 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/CONSTRAINT/REF_CONSTRAINT Processing object type TABLE_EXPORT/TABLE/INDEX/BITMAP_INDEX/INDEX Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/BITMAP_INDEX/INDEX_STATISTICS Processing object type TABLE_EXPORT/TABLE/TRIGGER Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS . . exported "SXC"."SYS_SOUNDCHECK":"SYS_SOUNDCHECK_MAX"."SYS_SOUNDCHECK_MAX_P2" 11.90 GB 49350250 rows . . exported "SXC"."SYS_SOUNDCHECK":"SYS_SOUNDCHECK_MAX"."SYS_SOUNDCHECK_MAX_P5" 4.580 GB 19143212 rows . . exported "SXC"."SYS_SOUNDCHECK":"SYS_SOUNDCHECK_MAX"."SYS_SOUNDCHECK_MAX_P3" 2.986 GB 12173090 rows . . exported "SXC"."SYS_HT_SOUNDCHECK" 2.434 GB 10333476 rows . . exported "SXC"."SYS_SOUNDCHECK":"SYS_SOUNDCHECK_MAX"."SYS_SOUNDCHECK_MAX_P1" 1.120 GB 4851529 rows . . exported "SXC"."SYS_SOUNDCHECK":"SYS_SOUNDCHECK_MAX"."SYS_SOUNDCHECK_MAX_P6" 958.1 MB 4316826 rows . . exported "SXC"."SYS_SOUNDCHECK":"SYS_SOUNDCHECK_MAX"."SYS_SOUNDCHECK_MAX_P4" 65.45 MB 266633 rows . . exported "SXC"."SYS_BOM_LOCATION" 9.062 KB 12 rows . . exported "SXC"."SYS_PART" 2.497 MB 19831 rows . . exported "SXC"."SYS_BOM" 463.5 KB 6945 rows . . exported "SXC"."SYS_BOM_VERSION" 15.44 MB 208189 rows . . exported "SXC"."SYS_MACHINE_ALARM_CODE_DETAIL" 16.77 MB 212344 rows . . exported "SXC"."SYS_RC_ROUTE_MAP" 14.70 MB 929 rows . . exported "SXC"."SYS_TOOLING_SN" 8.593 KB 8 rows . . exported "SXC"."SYS_SOUNDCHECK":"SYS_SOUNDCHECK_P201908"."SYS_SOUNDCHECK_P201908_P5" 8.294 MB 34750 rows . . exported "SXC"."SYS_SOUNDCHECK":"SYS_SOUNDCHECK_P201908"."SYS_SOUNDCHECK_P201908_P6" 8.225 MB 34462 rows ............................ ............................ ............................ . . exported "SXC"."SYS_VOLUME" 0 KB 0 rows . . exported "SXC"."SYS_WAREHOUSE" 0 KB 0 rows . . exported "SXC"."SYS_WAREHOUSE_NOUSE" 0 KB 0 rows Master table "SYS"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded ****************************************************************************** Dump file set for SYS.SYS_EXPORT_TABLE_01 is: /home/oracle/backup/sys_tables.dmp Job "SYS"."SYS_EXPORT_TABLE_01" successfully completed at Fri Jul 22 13:20:54 2022 elapsed 0 00:02:11
3. 拷贝dmp文件
[oracle@DB1 backup]$ scp sys_tables.dmp oracle@10.7.xxx.xxx:/home/oracle/backup/
oracle@10.7.xxx.xxx's password:
sys_tables.dmp 100% 24GB 109.9MB/s 03:45
4. 目标端导入
[oracle@DB2:/home/oracle/backup]$ impdp \'/ AS SYSDBA\' directory=EXPDP_DIR dumpfile=sys_tables.dmp logfile=sys_tables_imppdp-tab.log cluster=n Import: Release 11.2.0.4.0 - Production on Fri Jul 22 13:26:41 2022 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. ;;; Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Master table "SYS"."SYS_IMPORT_FULL_01" successfully loaded/unloaded Starting "SYS"."SYS_IMPORT_FULL_01": "/******** AS SYSDBA" directory=EXPDP_DIR dumpfile=sys_tables.dmp logfile=sys_tables_imppdp-ta b.log cluster=n Processing object type TABLE_EXPORT/TABLE/TABLE ORA-39151: Table "SXC"."SYS_SOUNDCHECK" exists. All dependent metadata and data will be skipped due to table_exists_action of skip ORA-39151: Table "SXC"."SYS_DEFECT" exists. All dependent metadata and data will be skipped due to table_exists_action of skip ORA-39151: Table "SXC"."SYS_PROGRAM_FUN_NAME" exists. All dependent metadata and data will be skipped due to table_exists_action o f skip ORA-39151: Table "SXC"."SYS_ROUTE_DETAIL" exists. All dependent metadata and data will be skipped due to table_exists_action of sk ip ORA-39151: Table "SXC"."SYS_BOM_INFO" exists. All dependent metadata and data will be skipped due to table_exists_action of skip ORA-39151: Table "SXC"."SYS_MODEL" exists. All dependent metadata and data will be skipped due to table_exists_action of skip ORA-39151: Table "SXC"."SYS_MACHINE_STATUS" exists. All dependent metadata and data will be skipped due to table_exists_action of skip Processing object type TABLE_EXPORT/TABLE/TABLE_DATA . . imported "SXC"."SYS_BOM_LOCATION" 9.062 KB 12 rows . . imported "SXC"."SYS_BOM_VERSION" 15.44 MB 208189 rows . . imported "SXC"."SYS_RC_ROUTE_MAP" 14.70 MB 929 rows . . imported "SXC"."SYS_TOOLING_SN" 8.593 KB 8 rows . . imported "SXC"."SYS_MACHINE_ALARM_CODE_LOG" 5.703 MB 70731 rows . . imported "SXC"."SYS_HT_MODULE_PARAM" 5.797 MB 72693 rows . . imported "SXC"."SYS_FRAME_HOLD536_LOG" 3.079 MB 39521 rows . . imported "SXC"."SYS_HT_BOM" 2.135 MB 32952 rows . . imported "SXC"."SYS_FRAME_HOLD536" 1.404 MB 16724 rows . . imported "SXC"."SYS_TMP" 1.051 MB 52223 rows . . imported "SXC"."SYS_HT_CHECK_PARAM" 1.215 MB 19700 rows . . imported "SXC"."SYS_HT_ROUTE_DETAIL" 654.9 KB 13003 rows . . imported "SXC"."SYS_MODULE_PARAM" 690.1 KB 9715 rows . . imported "SXC"."SYS_HT_TERMINAL" 491.8 KB 5994 rows . . imported "SXC"."SYS_HT_PART" 475.4 KB 3455 rows . . imported "SXC"."SYS_HT_MATERIAL_CHECK" 325.9 KB 8603 rows . . imported "SXC"."SYS_HT_PROCESS" 358.6 KB 4025 rows . . imported "SXC"."SYS_CUST_REPORT" 166.2 KB 158 rows . . imported "SXC"."SYS_TAG_MODULE_DETAIL" 166.8 KB 2097 rows . . imported "SXC"."SYS_TERMINAL_CFG" 157.9 KB 2032 rows . . imported "SXC"."SYS_CUST_REPORT_COLUMN" 22.96 KB 237 rows . . imported "SXC"."SYS_CUST_REPORT_LINK" 13.54 KB 1 rows . . imported "SXC"."SYS_CUST_REPORT_PARAM" 70.52 KB 397 rows ............................ ............................ ............................ . . imported "SXC"."SYS_VOLUME" 0 KB 0 rows . . imported "SXC"."SYS_WAREHOUSE" 0 KB 0 rows . . imported "SXC"."SYS_WAREHOUSE_NOUSE" 0 KB 0 rows Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT Processing object type TABLE_EXPORT/TABLE/COMMENT 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/CONSTRAINT/REF_CONSTRAINT Processing object type TABLE_EXPORT/TABLE/INDEX/BITMAP_INDEX/INDEX Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/BITMAP_INDEX/INDEX_STATISTICS Processing object type TABLE_EXPORT/TABLE/TRIGGER Job "SYS"."SYS_IMPORT_FULL_01" completed with 443 error(s) at Fri Jul 22 13:27:47 2022 elapsed 0 00:01:05
因为DB2中已经存在部分表，所以导入过程中，会提示table_exists_action skip，默认为skip，跳过已存在的表，不会覆盖这部分表的数据 。
-- 共skip了37张表，和上面查询信息一致
[oracle@DB2:/home/oracle/backup]$ cat sys_tables_imppdp-tab.log |grep skip |wc -l
37
5. 目标端再次查看sys_开头的表数量
SQL> select COUNT(*) from dba_tables where table_name like 'SYS_%' and owner='SXC';
COUNT(*)
----------
501
现在DB2 中SYS_开头的表和DB1 数量一致。
测试EXP也支持TABLES=SXC.SYS_V% 这种写法。
[oracle@DB1:/home/oracle/backup]$ exp system/oracle file=/home/oracle/test.dmp TABLES=SXC.SYS_V%
Export: Release 11.2.0.4.0 - Production on Fri Jul 22 13:43:16 2022
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in AL32UTF8 character set and AL16UTF16 NCHAR character set
About to export specified tables via Conventional Path ...
Current user changed to SXC
. . exporting table SYS_VOLUME 0 rows exported
. . exporting table SYS_VENDOR_BK 0 rows exported
. . exporting table SYS_VENDOR_PART 0 rows exported
. . exporting table SYS_VENDOR 1243 rows exported
Export terminated successfully without warnings.
最后修改时间：2022-07-27 13:20:08
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
