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進行舉報，并提供相關證據，一經查實，墨天輪將立刻刪除相關內容。
