oracle 导出以xxx开头的表 -m6米乐安卓版下载

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
米乐app官网下载 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
米乐app官网下载 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
米乐app官网下载 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.