转载 11g之后版本的oracle之间的数据比对工具
一、概述
Oracle11g中引入了一个新的PL/SQL包DBMS_COMPARISON，可以比较不同数据库下或者schame下的对象/schema/数据，并且可以根据规则将不同的数据进行同步。这在进行数据的分布时将十分的有用，例如你的数据复制过程中出现问题，导致源数据和目标数据出现不一致，则可以借用该特性进行处理。如果你的系统中没有安装该过程，可以通过以下脚本手动安装：
$ORACLE_HOME/rdbms/admin/dbmscmp.sql
可以利用DBMS_COMPARISON来执行比较的对象有：
* 表
* 基于单个表的视图
* 物化视图
* 以上三种对象的同义词
二、具体操作举例
需比较的140张表：
DBMS_COMPARISON在实际中的应用_2
这140张表有118张全量表，可以利用DBMS_COMPARISON包进行比较。剩余的22张增量表只能进行手工比较。
--查看140张表--TB_UNKNOWCODE表只在ETL库里有，不用处理。所以总计需处理139张表。
select table_name from dba_tables where
owner='SUPER' AND
table_name in('DM_BBLX','DM_BBZL','DM_BZ','DM_BZH','DM_BZSSX','DM_CFJDLX','DM_CHK_GSSYSZSL','DM_CHK_SMHY','DM_CHK_YSJC','DM_CHK_YSKM','DM_CHK_YSZSL','DM_CWZL','DM_CZBUCFS','DM_CZPQ','DM_DBLINK','DM_DDXS','DM_DEXISHU','DM_DEXM','DM_DE_TZYY','DM_DLJG','DM_DQXS','DM_DXDC_DCXM','DM_DZLB','DM_DZLX','DM_FLCLYJ','DM_FPKC_CZLX','DM_FPPZ_BBZL','DM_FPPZ_YSC','DM_FPZL','DM_FP_BGFS','DM_FP_HXLB','DM_FP_JXLX','DM_FP_SHJG','DM_FP_YJLB','DM_FY_FYSX','DM_FY_SJ','DM_FY_SQZT','DM_FZ_AJLY','DM_FZ_BLSS','DM_FZ_CFJD','DM_FZ_CFJY','DM_FZ_FLYJ','DM_FZ_KXSZ','DM_FZ_WFXZ','DM_FZ_WSJC','DM_FZ_WZXW','DM_FZ_ZXZT','DM_GLFW_JMSC','DM_GSDJ','DM_GSJG','DM_GZSZDYT','DM_HTPZZL','DM_HYDM','DM_JDWH','DM_JMXM','DM_JMXZ','DM_JSYJ','DM_JYFS','DM_KHXX','DM_KHYH','DM_KJHSLX','DM_KPLX','DM_KPZFYY','DM_KT_DZLX','DM_KZSZT','DM_LSGX','DM_MODEL','DM_NSQX','DM_NSRLX','DM_NSRZT','DM_PZCCLX','DM_PZCPMC','DM_PZDLDM','DM_PZJBDW','DM_PZJBRY','DM_PZLX','DM_PZMC','DM_PZXHYY','DM_PZZL','DM_PZZY','DM_PZ_SJLX','DM_PZ_YPDWFL','DM_QSLX','DM_QSZYLX','DM_QYGM','DM_RCJC_JCLX','DM_RCJC_JCZT','DM_RKXZ','DM_SBFS','DM_SBWXZL','DM_SBXJQX','DM_SBXJQXVSNSQX','DM_SHUIZHONG','DM_SKGK','DM_SKLY','DM_SM','DM_SPZT','DM_SQRLX','DM_SWBM','DM_SWDJBLX','DM_SWJG','DM_SWXZXW','DM_SXF_YHDZCZY','DM_SZDM_PIPE','DM_SZLKX','DM_SZPQ','DM_TDQDFS','DM_TDQSCSXS','DM_TTXZ','DM_TZXZXWYY','DM_TZZGJ','DM_WSDM','DM_WSZL','DM_WZLX','DM_XMGL_KBGX','DM_XSJC','DM_XZXK_XM','DM_YBSKJBB','DM_YHZJLX','DM_YJFS','DM_YSJC','DM_YSJCFP','DM_YSKM_PIPE','DM_ZCLX','DM_ZDGHYT','DM_ZDH','DM_ZDLB','DM_ZGBM','DM_ZJFS','DM_ZJLX','DM_ZJZLZL','DM_ZJZT','DM_ZSDW','DM_ZSFS','DM_ZSFS_NEW','DM_ZXYY','DM_ZYDM','DM_ZZKM','DM_ZZLB')
--其中118张全量表（全在193张分类里）
select table_name from dba_tables where
owner='SUPER' AND
table_name in('DM_BBLX','DM_BBZL','DM_BZ','DM_BZH','DM_BZSSX','DM_CFJDLX','DM_CHK_GSSYSZSL','DM_CHK_SMHY','DM_CHK_YSJC','DM_CHK_YSKM','DM_CHK_YSZSL','DM_CWZL','DM_CZBUCFS','DM_CZPQ','DM_DBLINK','DM_DDXS','DM_DEXISHU','DM_DEXM','DM_DE_TZYY','DM_DLJG','DM_DQXS','DM_DXDC_DCXM','DM_DZLB','DM_DZLX','DM_FLCLYJ','DM_FPKC_CZLX','DM_FPPZ_BBZL','DM_FPPZ_YSC','DM_FPZL','DM_FP_BGFS','DM_FP_HXLB','DM_FP_JXLX','DM_FP_SHJG','DM_FP_YJLB','DM_FY_FYSX','DM_FY_SJ','DM_FY_SQZT','DM_FZ_AJLY','DM_FZ_BLSS','DM_FZ_CFJD','DM_FZ_CFJY','DM_FZ_FLYJ','DM_FZ_KXSZ','DM_FZ_WFXZ','DM_FZ_WSJC','DM_FZ_WZXW','DM_FZ_ZXZT','DM_GLFW_JMSC','DM_GSDJ','DM_GSJG','DM_GZSZDYT','DM_HTPZZL','DM_HYDM','DM_JDWH','DM_JMXM','DM_JMXZ','DM_JSYJ','DM_JYFS','DM_KHXX','DM_KHYH','DM_KJHSLX','DM_KPLX','DM_KPZFYY','DM_KT_DZLX','DM_KZSZT','DM_LSGX','DM_MODEL','DM_NSQX','DM_NSRLX','DM_NSRZT','DM_PZCCLX','DM_PZCPMC','DM_PZDLDM','DM_PZJBDW','DM_PZJBRY','DM_PZLX','DM_PZMC','DM_PZXHYY','DM_PZZL','DM_PZZY','DM_PZ_SJLX','DM_PZ_YPDWFL','DM_QSLX','DM_QSZYLX','DM_QYGM','DM_RCJC_JCLX','DM_RCJC_JCZT','DM_RKXZ','DM_SBFS','DM_SBWXZL','DM_SBXJQX','DM_SBXJQXVSNSQX','DM_SHUIZHONG','DM_SKGK','DM_SKLY','DM_SM','DM_SPZT','DM_SQRLX','DM_SWBM','DM_SWDJBLX','DM_SWJG','DM_SWXZXW','DM_SXF_YHDZCZY','DM_SZDM_PIPE','DM_SZLKX','DM_SZPQ','DM_TDQDFS','DM_TDQSCSXS','DM_TTXZ','DM_TZXZXWYY','DM_TZZGJ','DM_WSDM','DM_WSZL','DM_WZLX','DM_XMGL_KBGX','DM_XSJC','DM_XZXK_XM','DM_YBSKJBB','DM_YHZJLX','DM_YJFS','DM_YSJC','DM_YSJCFP','DM_YSKM_PIPE','DM_ZCLX','DM_ZDGHYT','DM_ZDH','DM_ZDLB','DM_ZGBM','DM_ZJFS','DM_ZJLX','DM_ZJZLZL','DM_ZJZT','DM_ZSDW','DM_ZSFS','DM_ZSFS_NEW','DM_ZXYY','DM_ZYDM','DM_ZZKM','DM_ZZLB')
and table_name in ('CWREPORT_NAME','CWREPORT_ROW_COL_TITLE','DM_BBLX','DM_BBZL','DM_BZ','DM_BZH','DM_BZSSX','DM_CCSJMSZM','DM_CFJDLX','DM_CHK_GSSYSZSL','DM_CHK_SMHY','DM_CHK_YSKM','DM_CHK_YSZSL','DM_CWZL','DM_CZBUCFS','DM_DBLINK','DM_DE_TZYY','DM_DQXS','DM_DXDC_DCXM','DM_DZLB','DM_DZLX','DM_FLCLYJ','DM_FPKC_CZLX','DM_FPPZ_BBZL','DM_FPZL','DM_FP_BGFS','DM_FP_HXLB','DM_FP_JXLX','DM_FP_SHJG','DM_FP_YJLB','DM_FY_FYSX','DM_FY_SJ','DM_FY_SQZT','DM_FZ_AJLY','DM_FZ_BLSS','DM_FZ_CFJD','DM_FZ_CFJY','DM_FZ_FLYJ','DM_FZ_KXSZ','DM_FZ_WFXZ','DM_FZ_WSJC','DM_FZ_WZXW','DM_FZ_ZXZT','DM_GZSZDYT','DM_HTPZZL','DM_HYDM','DM_JMXM','DM_JMXZ','DM_JSYJ','DM_JYFS','DM_JZHTXM','DM_KJHSLX','DM_KPLX','DM_KPZFYY','DM_KT_DZLX','DM_KZSZT','DM_LSGX','DM_MODEL','DM_NSQX','DM_NSRLX','DM_NSRZT','DM_PZCCLX','DM_PZCPMC','DM_PZDLDM','DM_PZLX','DM_PZXHYY','DM_PZZL','DM_PZZY','DM_PZ_SJLX','DM_QSLX','DM_QSZYLX','DM_QYGM','DM_RCJC_JCLX','DM_RCJC_JCZT','DM_RKXZ','DM_SBFS','DM_SBWXZL','DM_SBXJQX','DM_SBXJQXVSNSQX','DM_SHUIZHONG','DM_SKLY','DM_SM','DM_SPZT','DM_SQRLX','DM_SWDJBLX','DM_SWJG','DM_SWXZXW','DM_SXF_YHDZCZY','DM_SZDM_PIPE','DM_SZLKX','DM_TDQDFS','DM_TDQSCSXS','DM_TTXZ','DM_TZXZXWYY','DM_TZZGJ','DM_WSDM','DM_WSZL','DM_WZLX','DM_XMGL_KBGX','DM_XSJC','DM_XZXK_XM','DM_YBSKJBB','DM_YHZJLX','DM_YJFS','DM_YSJC','DM_YSJCFP','DM_YSKM_PIPE','DM_ZCLX','DM_ZDGHYT','DM_ZDH','DM_ZDLB','DM_ZGBM','DM_ZGZL_KHXM','DM_ZJFS','DM_ZJLX','DM_ZJZLZL','DM_ZJZT','DM_ZSFS','DM_ZSFS_NEW','DM_ZXQY','DM_ZXQYBZ','DM_ZXYY','DM_ZYDM','DM_ZZKM','DM_ZZLB','FP_BB','FP_LSLW_PM','FZ_CXDM','FZ_CXDYDM','GLFW_BZDMDJB','GLFW_DQJFF','GLFW_GHKHYH','GLFW_KBGXM','GLFW_WS_WSWH','JC_CX_FLFG','JC_DM_AJSLYJ','JC_DM_AJZLLB','JC_DM_BGQX','JC_DM_JBXS','JC_DM_JCHJ','JC_DM_LX','JC_DM_LY','JC_DM_SLCX','JC_DM_WFCY','JC_DM_WFWZLX','JC_DM_WFWZSD','JC_DM_WSLX','JC_DM_ZT','JC_WH_ZB','JC_WSSP_DWO','KT_BGK_ZJYT','KT_ZSK_KXLB','KT_ZSK_ZJXS','REPORT_CHECK_OUT','REPORT_COUNT_SET','REPORT_HZCS','REPORT_NAME','REPORT_ROW_COL_TITLE','REPORT_SQL','SJ_DW_INFO','SJ_FWXM_LINK','SJ_FWXM_SJXKZ','SJ_HOLIDAY_SET','TABLE_OPER_POLICY','WH_DMCLASS','WH_DMGL','XT_GDFWXM_SET','XT_JC_JCWS','XT_JC_JTSX','XT_SJ_DOCCODE','XT_SJ_FWXM','XT_SJ_GWSZ','XT_SJ_JMXMFSZL','XT_SJ_JTSX','XT_SJ_LZHJ','XT_SJ_SLSXTZNR','XT_SJ_WINDOW','XT_SJ_WSSXX','XT_SJ_ZZFP','YHS_DM_YWDL','YHS_DM_YWXL','YHS_DW_CHILD','YHS_FORM_DW','YHS_FORM_WORDSET','YHS_FROM_SQLSET','ZF_DM_YHJB','ZF_XT_BSSX','ZF_XT_XMDY','ZF_XT_XMSZ','ZF_XT_ZBDY','ZF_XT_ZBSZ','ZHCX_SQL','ZHCX_TABLE')
--21张增量表
select table_name from dba_tables where
owner='SUPER' AND
table_name in('DM_BBLX','DM_BBZL','DM_BZ','DM_BZH','DM_BZSSX','DM_CFJDLX','DM_CHK_GSSYSZSL','DM_CHK_SMHY','DM_CHK_YSJC','DM_CHK_YSKM','DM_CHK_YSZSL','DM_CWZL','DM_CZBUCFS','DM_CZPQ','DM_DBLINK','DM_DDXS','DM_DEXISHU','DM_DEXM','DM_DE_TZYY','DM_DLJG','DM_DQXS','DM_DXDC_DCXM','DM_DZLB','DM_DZLX','DM_FLCLYJ','DM_FPKC_CZLX','DM_FPPZ_BBZL','DM_FPPZ_YSC','DM_FPZL','DM_FP_BGFS','DM_FP_HXLB','DM_FP_JXLX','DM_FP_SHJG','DM_FP_YJLB','DM_FY_FYSX','DM_FY_SJ','DM_FY_SQZT','DM_FZ_AJLY','DM_FZ_BLSS','DM_FZ_CFJD','DM_FZ_CFJY','DM_FZ_FLYJ','DM_FZ_KXSZ','DM_FZ_WFXZ','DM_FZ_WSJC','DM_FZ_WZXW','DM_FZ_ZXZT','DM_GLFW_JMSC','DM_GSDJ','DM_GSJG','DM_GZSZDYT','DM_HTPZZL','DM_HYDM','DM_JDWH','DM_JMXM','DM_JMXZ','DM_JSYJ','DM_JYFS','DM_KHXX','DM_KHYH','DM_KJHSLX','DM_KPLX','DM_KPZFYY','DM_KT_DZLX','DM_KZSZT','DM_LSGX','DM_MODEL','DM_NSQX','DM_NSRLX','DM_NSRZT','DM_PZCCLX','DM_PZCPMC','DM_PZDLDM','DM_PZJBDW','DM_PZJBRY','DM_PZLX','DM_PZMC','DM_PZXHYY','DM_PZZL','DM_PZZY','DM_PZ_SJLX','DM_PZ_YPDWFL','DM_QSLX','DM_QSZYLX','DM_QYGM','DM_RCJC_JCLX','DM_RCJC_JCZT','DM_RKXZ','DM_SBFS','DM_SBWXZL','DM_SBXJQX','DM_SBXJQXVSNSQX','DM_SHUIZHONG','DM_SKGK','DM_SKLY','DM_SM','DM_SPZT','DM_SQRLX','DM_SWBM','DM_SWDJBLX','DM_SWJG','DM_SWXZXW','DM_SXF_YHDZCZY','DM_SZDM_PIPE','DM_SZLKX','DM_SZPQ','DM_TDQDFS','DM_TDQSCSXS','DM_TTXZ','DM_TZXZXWYY','DM_TZZGJ','DM_WSDM','DM_WSZL','DM_WZLX','DM_XMGL_KBGX','DM_XSJC','DM_XZXK_XM','DM_YBSKJBB','DM_YHZJLX','DM_YJFS','DM_YSJC','DM_YSJCFP','DM_YSKM_PIPE','DM_ZCLX','DM_ZDGHYT','DM_ZDH','DM_ZDLB','DM_ZGBM','DM_ZJFS','DM_ZJLX','DM_ZJZLZL','DM_ZJZT','DM_ZSDW','DM_ZSFS','DM_ZSFS_NEW','DM_ZXYY','DM_ZYDM','DM_ZZKM','DM_ZZLB')
and table_name not in ('CWREPORT_NAME','CWREPORT_ROW_COL_TITLE','DM_BBLX','DM_BBZL','DM_BZ','DM_BZH','DM_BZSSX','DM_CCSJMSZM','DM_CFJDLX','DM_CHK_GSSYSZSL','DM_CHK_SMHY','DM_CHK_YSKM','DM_CHK_YSZSL','DM_CWZL','DM_CZBUCFS','DM_DBLINK','DM_DE_TZYY','DM_DQXS','DM_DXDC_DCXM','DM_DZLB','DM_DZLX','DM_FLCLYJ','DM_FPKC_CZLX','DM_FPPZ_BBZL','DM_FPZL','DM_FP_BGFS','DM_FP_HXLB','DM_FP_JXLX','DM_FP_SHJG','DM_FP_YJLB','DM_FY_FYSX','DM_FY_SJ','DM_FY_SQZT','DM_FZ_AJLY','DM_FZ_BLSS','DM_FZ_CFJD','DM_FZ_CFJY','DM_FZ_FLYJ','DM_FZ_KXSZ','DM_FZ_WFXZ','DM_FZ_WSJC','DM_FZ_WZXW','DM_FZ_ZXZT','DM_GZSZDYT','DM_HTPZZL','DM_HYDM','DM_JMXM','DM_JMXZ','DM_JSYJ','DM_JYFS','DM_JZHTXM','DM_KJHSLX','DM_KPLX','DM_KPZFYY','DM_KT_DZLX','DM_KZSZT','DM_LSGX','DM_MODEL','DM_NSQX','DM_NSRLX','DM_NSRZT','DM_PZCCLX','DM_PZCPMC','DM_PZDLDM','DM_PZLX','DM_PZXHYY','DM_PZZL','DM_PZZY','DM_PZ_SJLX','DM_QSLX','DM_QSZYLX','DM_QYGM','DM_RCJC_JCLX','DM_RCJC_JCZT','DM_RKXZ','DM_SBFS','DM_SBWXZL','DM_SBXJQX','DM_SBXJQXVSNSQX','DM_SHUIZHONG','DM_SKLY','DM_SM','DM_SPZT','DM_SQRLX','DM_SWDJBLX','DM_SWJG','DM_SWXZXW','DM_SXF_YHDZCZY','DM_SZDM_PIPE','DM_SZLKX','DM_TDQDFS','DM_TDQSCSXS','DM_TTXZ','DM_TZXZXWYY','DM_TZZGJ','DM_WSDM','DM_WSZL','DM_WZLX','DM_XMGL_KBGX','DM_XSJC','DM_XZXK_XM','DM_YBSKJBB','DM_YHZJLX','DM_YJFS','DM_YSJC','DM_YSJCFP','DM_YSKM_PIPE','DM_ZCLX','DM_ZDGHYT','DM_ZDH','DM_ZDLB','DM_ZGBM','DM_ZGZL_KHXM','DM_ZJFS','DM_ZJLX','DM_ZJZLZL','DM_ZJZT','DM_ZSFS','DM_ZSFS_NEW','DM_ZXQY','DM_ZXQYBZ','DM_ZXYY','DM_ZYDM','DM_ZZKM','DM_ZZLB','FP_BB','FP_LSLW_PM','FZ_CXDM','FZ_CXDYDM','GLFW_BZDMDJB','GLFW_DQJFF','GLFW_GHKHYH','GLFW_KBGXM','GLFW_WS_WSWH','JC_CX_FLFG','JC_DM_AJSLYJ','JC_DM_AJZLLB','JC_DM_BGQX','JC_DM_JBXS','JC_DM_JCHJ','JC_DM_LX','JC_DM_LY','JC_DM_SLCX','JC_DM_WFCY','JC_DM_WFWZLX','JC_DM_WFWZSD','JC_DM_WSLX','JC_DM_ZT','JC_WH_ZB','JC_WSSP_DWO','KT_BGK_ZJYT','KT_ZSK_KXLB','KT_ZSK_ZJXS','REPORT_CHECK_OUT','REPORT_COUNT_SET','REPORT_HZCS','REPORT_NAME','REPORT_ROW_COL_TITLE','REPORT_SQL','SJ_DW_INFO','SJ_FWXM_LINK','SJ_FWXM_SJXKZ','SJ_HOLIDAY_SET','TABLE_OPER_POLICY','WH_DMCLASS','WH_DMGL','XT_GDFWXM_SET','XT_JC_JCWS','XT_JC_JTSX','XT_SJ_DOCCODE','XT_SJ_FWXM','XT_SJ_GWSZ','XT_SJ_JMXMFSZL','XT_SJ_JTSX','XT_SJ_LZHJ','XT_SJ_SLSXTZNR','XT_SJ_WINDOW','XT_SJ_WSSXX','XT_SJ_ZZFP','YHS_DM_YWDL','YHS_DM_YWXL','YHS_DW_CHILD','YHS_FORM_DW','YHS_FORM_WORDSET','YHS_FROM_SQLSET','ZF_DM_YHJB','ZF_XT_BSSX','ZF_XT_XMDY','ZF_XT_XMSZ','ZF_XT_ZBDY','ZF_XT_ZBSZ','ZHCX_SQL','ZHCX_TABLE')
二、步骤：
--118张表处理步骤:
1.查看表上是否建有比较任务
select * from dba_comparison where object_name in (select table_name from dba_tables where
owner='SUPER' AND
table_name in('DM_BBLX','DM_BBZL','DM_BZ','DM_BZH','DM_BZSSX','DM_CFJDLX','DM_CHK_GSSYSZSL','DM_CHK_SMHY','DM_CHK_YSJC','DM_CHK_YSKM','DM_CHK_YSZSL','DM_CWZL','DM_CZBUCFS','DM_CZPQ','DM_DBLINK','DM_DDXS','DM_DEXISHU','DM_DEXM','DM_DE_TZYY','DM_DLJG','DM_DQXS','DM_DXDC_DCXM','DM_DZLB','DM_DZLX','DM_FLCLYJ','DM_FPKC_CZLX','DM_FPPZ_BBZL','DM_FPPZ_YSC','DM_FPZL','DM_FP_BGFS','DM_FP_HXLB','DM_FP_JXLX','DM_FP_SHJG','DM_FP_YJLB','DM_FY_FYSX','DM_FY_SJ','DM_FY_SQZT','DM_FZ_AJLY','DM_FZ_BLSS','DM_FZ_CFJD','DM_FZ_CFJY','DM_FZ_FLYJ','DM_FZ_KXSZ','DM_FZ_WFXZ','DM_FZ_WSJC','DM_FZ_WZXW','DM_FZ_ZXZT','DM_GLFW_JMSC','DM_GSDJ','DM_GSJG','DM_GZSZDYT','DM_HTPZZL','DM_HYDM','DM_JDWH','DM_JMXM','DM_JMXZ','DM_JSYJ','DM_JYFS','DM_KHXX','DM_KHYH','DM_KJHSLX','DM_KPLX','DM_KPZFYY','DM_KT_DZLX','DM_KZSZT','DM_LSGX','DM_MODEL','DM_NSQX','DM_NSRLX','DM_NSRZT','DM_PZCCLX','DM_PZCPMC','DM_PZDLDM','DM_PZJBDW','DM_PZJBRY','DM_PZLX','DM_PZMC','DM_PZXHYY','DM_PZZL','DM_PZZY','DM_PZ_SJLX','DM_PZ_YPDWFL','DM_QSLX','DM_QSZYLX','DM_QYGM','DM_RCJC_JCLX','DM_RCJC_JCZT','DM_RKXZ','DM_SBFS','DM_SBWXZL','DM_SBXJQX','DM_SBXJQXVSNSQX','DM_SHUIZHONG','DM_SKGK','DM_SKLY','DM_SM','DM_SPZT','DM_SQRLX','DM_SWBM','DM_SWDJBLX','DM_SWJG','DM_SWXZXW','DM_SXF_YHDZCZY','DM_SZDM_PIPE','DM_SZLKX','DM_SZPQ','DM_TDQDFS','DM_TDQSCSXS','DM_TTXZ','DM_TZXZXWYY','DM_TZZGJ','DM_WSDM','DM_WSZL','DM_WZLX','DM_XMGL_KBGX','DM_XSJC','DM_XZXK_XM','DM_YBSKJBB','DM_YHZJLX','DM_YJFS','DM_YSJC','DM_YSJCFP','DM_YSKM_PIPE','DM_ZCLX','DM_ZDGHYT','DM_ZDH','DM_ZDLB','DM_ZGBM','DM_ZJFS','DM_ZJLX','DM_ZJZLZL','DM_ZJZT','DM_ZSDW','DM_ZSFS','DM_ZSFS_NEW','DM_ZXYY','DM_ZYDM','DM_ZZKM','DM_ZZLB')
and table_name in ('CWREPORT_NAME','CWREPORT_ROW_COL_TITLE','DM_BBLX','DM_BBZL','DM_BZ','DM_BZH','DM_BZSSX','DM_CCSJMSZM','DM_CFJDLX','DM_CHK_GSSYSZSL','DM_CHK_SMHY','DM_CHK_YSKM','DM_CHK_YSZSL','DM_CWZL','DM_CZBUCFS','DM_DBLINK','DM_DE_TZYY','DM_DQXS','DM_DXDC_DCXM','DM_DZLB','DM_DZLX','DM_FLCLYJ','DM_FPKC_CZLX','DM_FPPZ_BBZL','DM_FPZL','DM_FP_BGFS','DM_FP_HXLB','DM_FP_JXLX','DM_FP_SHJG','DM_FP_YJLB','DM_FY_FYSX','DM_FY_SJ','DM_FY_SQZT','DM_FZ_AJLY','DM_FZ_BLSS','DM_FZ_CFJD','DM_FZ_CFJY','DM_FZ_FLYJ','DM_FZ_KXSZ','DM_FZ_WFXZ','DM_FZ_WSJC','DM_FZ_WZXW','DM_FZ_ZXZT','DM_GZSZDYT','DM_HTPZZL','DM_HYDM','DM_JMXM','DM_JMXZ','DM_JSYJ','DM_JYFS','DM_JZHTXM','DM_KJHSLX','DM_KPLX','DM_KPZFYY','DM_KT_DZLX','DM_KZSZT','DM_LSGX','DM_MODEL','DM_NSQX','DM_NSRLX','DM_NSRZT','DM_PZCCLX','DM_PZCPMC','DM_PZDLDM','DM_PZLX','DM_PZXHYY','DM_PZZL','DM_PZZY','DM_PZ_SJLX','DM_QSLX','DM_QSZYLX','DM_QYGM','DM_RCJC_JCLX','DM_RCJC_JCZT','DM_RKXZ','DM_SBFS','DM_SBWXZL','DM_SBXJQX','DM_SBXJQXVSNSQX','DM_SHUIZHONG','DM_SKLY','DM_SM','DM_SPZT','DM_SQRLX','DM_SWDJBLX','DM_SWJG','DM_SWXZXW','DM_SXF_YHDZCZY','DM_SZDM_PIPE','DM_SZLKX','DM_TDQDFS','DM_TDQSCSXS','DM_TTXZ','DM_TZXZXWYY','DM_TZZGJ','DM_WSDM','DM_WSZL','DM_WZLX','DM_XMGL_KBGX','DM_XSJC','DM_XZXK_XM','DM_YBSKJBB','DM_YHZJLX','DM_YJFS','DM_YSJC','DM_YSJCFP','DM_YSKM_PIPE','DM_ZCLX','DM_ZDGHYT','DM_ZDH','DM_ZDLB','DM_ZGBM','DM_ZGZL_KHXM','DM_ZJFS','DM_ZJLX','DM_ZJZLZL','DM_ZJZT','DM_ZSFS','DM_ZSFS_NEW','DM_ZXQY','DM_ZXQYBZ','DM_ZXYY','DM_ZYDM','DM_ZZKM','DM_ZZLB','FP_BB','FP_LSLW_PM','FZ_CXDM','FZ_CXDYDM','GLFW_BZDMDJB','GLFW_DQJFF','GLFW_GHKHYH','GLFW_KBGXM','GLFW_WS_WSWH','JC_CX_FLFG','JC_DM_AJSLYJ','JC_DM_AJZLLB','JC_DM_BGQX','JC_DM_JBXS','JC_DM_JCHJ','JC_DM_LX','JC_DM_LY','JC_DM_SLCX','JC_DM_WFCY','JC_DM_WFWZLX','JC_DM_WFWZSD','JC_DM_WSLX','JC_DM_ZT','JC_WH_ZB','JC_WSSP_DWO','KT_BGK_ZJYT','KT_ZSK_KXLB','KT_ZSK_ZJXS','REPORT_CHECK_OUT','REPORT_COUNT_SET','REPORT_HZCS','REPORT_NAME','REPORT_ROW_COL_TITLE','REPORT_SQL','SJ_DW_INFO','SJ_FWXM_LINK','SJ_FWXM_SJXKZ','SJ_HOLIDAY_SET','TABLE_OPER_POLICY','WH_DMCLASS','WH_DMGL','XT_GDFWXM_SET','XT_JC_JCWS','XT_JC_JTSX','XT_SJ_DOCCODE','XT_SJ_FWXM','XT_SJ_GWSZ','XT_SJ_JMXMFSZL','XT_SJ_JTSX','XT_SJ_LZHJ','XT_SJ_SLSXTZNR','XT_SJ_WINDOW','XT_SJ_WSSXX','XT_SJ_ZZFP','YHS_DM_YWDL','YHS_DM_YWXL','YHS_DW_CHILD','YHS_FORM_DW','YHS_FORM_WORDSET','YHS_FROM_SQLSET','ZF_DM_YHJB','ZF_XT_BSSX','ZF_XT_XMDY','ZF_XT_XMSZ','ZF_XT_ZBDY','ZF_XT_ZBSZ','ZHCX_SQL','ZHCX_TABLE')
);
--没有建立比较任务
2.批量建立比较任务
--在SQL窗口执行，然后将结果在command窗口执行。
SELECT 'EXEC DBMS_COMPARISON.CREATE_COMPARISON(COMPARISON_NAME=>' || '''COM_' || TABLE_NAME || '_MXDB'',SCHEMA_NAME=>' || '''SUPER'''
|| ',OBJECT_NAME=>' || '''' || TABLE_NAME || ''''
|| ',DBLINK_NAME=>' || '''GZDSETL.GZDS.GOV.CN'''
|| ',REMOTE_SCHEMA_NAME=>' || '''SUPER'''
|| ',REMOTE_OBJECT_NAME=>' || '''' || TABLE_NAME|| '''' ||');' FROM
(SELECT OWNER,TABLE_NAME FROM ALL_TABLES WHERE TABLE_NAME in('DM_BBLX','DM_BBZL','DM_BZ','DM_BZH','DM_BZSSX','DM_CFJDLX','DM_CHK_GSSYSZSL','DM_CHK_SMHY','DM_CHK_YSJC','DM_CHK_YSKM','DM_CHK_YSZSL','DM_CWZL','DM_CZBUCFS','DM_CZPQ','DM_DBLINK','DM_DDXS','DM_DEXISHU','DM_DEXM','DM_DE_TZYY','DM_DLJG','DM_DQXS','DM_DXDC_DCXM','DM_DZLB','DM_DZLX','DM_FLCLYJ','DM_FPKC_CZLX','DM_FPPZ_BBZL','DM_FPPZ_YSC','DM_FPZL','DM_FP_BGFS','DM_FP_HXLB','DM_FP_JXLX','DM_FP_SHJG','DM_FP_YJLB','DM_FY_FYSX','DM_FY_SJ','DM_FY_SQZT','DM_FZ_AJLY','DM_FZ_BLSS','DM_FZ_CFJD','DM_FZ_CFJY','DM_FZ_FLYJ','DM_FZ_KXSZ','DM_FZ_WFXZ','DM_FZ_WSJC','DM_FZ_WZXW','DM_FZ_ZXZT','DM_GLFW_JMSC','DM_GSDJ','DM_GSJG','DM_GZSZDYT','DM_HTPZZL','DM_HYDM','DM_JDWH','DM_JMXM','DM_JMXZ','DM_JSYJ','DM_JYFS','DM_KHXX','DM_KHYH','DM_KJHSLX','DM_KPLX','DM_KPZFYY','DM_KT_DZLX','DM_KZSZT','DM_LSGX','DM_MODEL','DM_NSQX','DM_NSRLX','DM_NSRZT','DM_PZCCLX','DM_PZCPMC','DM_PZDLDM','DM_PZJBDW','DM_PZJBRY','DM_PZLX','DM_PZMC','DM_PZXHYY','DM_PZZL','DM_PZZY','DM_PZ_SJLX','DM_PZ_YPDWFL','DM_QSLX','DM_QSZYLX','DM_QYGM','DM_RCJC_JCLX','DM_RCJC_JCZT','DM_RKXZ','DM_SBFS','DM_SBWXZL','DM_SBXJQX','DM_SBXJQXVSNSQX','DM_SHUIZHONG','DM_SKGK','DM_SKLY','DM_SM','DM_SPZT','DM_SQRLX','DM_SWBM','DM_SWDJBLX','DM_SWJG','DM_SWXZXW','DM_SXF_YHDZCZY','DM_SZDM_PIPE','DM_SZLKX','DM_SZPQ','DM_TDQDFS','DM_TDQSCSXS','DM_TTXZ','DM_TZXZXWYY','DM_TZZGJ','DM_WSDM','DM_WSZL','DM_WZLX','DM_XMGL_KBGX','DM_XSJC','DM_XZXK_XM','DM_YBSKJBB','DM_YHZJLX','DM_YJFS','DM_YSJC','DM_YSJCFP','DM_YSKM_PIPE','DM_ZCLX','DM_ZDGHYT','DM_ZDH','DM_ZDLB','DM_ZGBM','DM_ZJFS','DM_ZJLX','DM_ZJZLZL','DM_ZJZT','DM_ZSDW','DM_ZSFS','DM_ZSFS_NEW','DM_ZXYY','DM_ZYDM','DM_ZZKM','DM_ZZLB')
and TABLE_NAME in ('CWREPORT_NAME','CWREPORT_ROW_COL_TITLE','DM_BBLX','DM_BBZL','DM_BZ','DM_BZH','DM_BZSSX','DM_CCSJMSZM','DM_CFJDLX','DM_CHK_GSSYSZSL','DM_CHK_SMHY','DM_CHK_YSKM','DM_CHK_YSZSL','DM_CWZL','DM_CZBUCFS','DM_DBLINK','DM_DE_TZYY','DM_DQXS','DM_DXDC_DCXM','DM_DZLB','DM_DZLX','DM_FLCLYJ','DM_FPKC_CZLX','DM_FPPZ_BBZL','DM_FPZL','DM_FP_BGFS','DM_FP_HXLB','DM_FP_JXLX','DM_FP_SHJG','DM_FP_YJLB','DM_FY_FYSX','DM_FY_SJ','DM_FY_SQZT','DM_FZ_AJLY','DM_FZ_BLSS','DM_FZ_CFJD','DM_FZ_CFJY','DM_FZ_FLYJ','DM_FZ_KXSZ','DM_FZ_WFXZ','DM_FZ_WSJC','DM_FZ_WZXW','DM_FZ_ZXZT','DM_GZSZDYT','DM_HTPZZL','DM_HYDM','DM_JMXM','DM_JMXZ','DM_JSYJ','DM_JYFS','DM_JZHTXM','DM_KJHSLX','DM_KPLX','DM_KPZFYY','DM_KT_DZLX','DM_KZSZT','DM_LSGX','DM_MODEL','DM_NSQX','DM_NSRLX','DM_NSRZT','DM_PZCCLX','DM_PZCPMC','DM_PZDLDM','DM_PZLX','DM_PZXHYY','DM_PZZL','DM_PZZY','DM_PZ_SJLX','DM_QSLX','DM_QSZYLX','DM_QYGM','DM_RCJC_JCLX','DM_RCJC_JCZT','DM_RKXZ','DM_SBFS','DM_SBWXZL','DM_SBXJQX','DM_SBXJQXVSNSQX','DM_SHUIZHONG','DM_SKLY','DM_SM','DM_SPZT','DM_SQRLX','DM_SWDJBLX','DM_SWJG','DM_SWXZXW','DM_SXF_YHDZCZY','DM_SZDM_PIPE','DM_SZLKX','DM_TDQDFS','DM_TDQSCSXS','DM_TTXZ','DM_TZXZXWYY','DM_TZZGJ','DM_WSDM','DM_WSZL','DM_WZLX','DM_XMGL_KBGX','DM_XSJC','DM_XZXK_XM','DM_YBSKJBB','DM_YHZJLX','DM_YJFS','DM_YSJC','DM_YSJCFP','DM_YSKM_PIPE','DM_ZCLX','DM_ZDGHYT','DM_ZDH','DM_ZDLB','DM_ZGBM','DM_ZGZL_KHXM','DM_ZJFS','DM_ZJLX','DM_ZJZLZL','DM_ZJZT','DM_ZSFS','DM_ZSFS_NEW','DM_ZXQY','DM_ZXQYBZ','DM_ZXYY','DM_ZYDM','DM_ZZKM','DM_ZZLB','FP_BB','FP_LSLW_PM','FZ_CXDM','FZ_CXDYDM','GLFW_BZDMDJB','GLFW_DQJFF','GLFW_GHKHYH','GLFW_KBGXM','GLFW_WS_WSWH','JC_CX_FLFG','JC_DM_AJSLYJ','JC_DM_AJZLLB','JC_DM_BGQX','JC_DM_JBXS','JC_DM_JCHJ','JC_DM_LX','JC_DM_LY','JC_DM_SLCX','JC_DM_WFCY','JC_DM_WFWZLX','JC_DM_WFWZSD','JC_DM_WSLX','JC_DM_ZT','JC_WH_ZB','JC_WSSP_DWO','KT_BGK_ZJYT','KT_ZSK_KXLB','KT_ZSK_ZJXS','REPORT_CHECK_OUT','REPORT_COUNT_SET','REPORT_HZCS','REPORT_NAME','REPORT_ROW_COL_TITLE','REPORT_SQL','SJ_DW_INFO','SJ_FWXM_LINK','SJ_FWXM_SJXKZ','SJ_HOLIDAY_SET','TABLE_OPER_POLICY','WH_DMCLASS','WH_DMGL','XT_GDFWXM_SET','XT_JC_JCWS','XT_JC_JTSX','XT_SJ_DOCCODE','XT_SJ_FWXM','XT_SJ_GWSZ','XT_SJ_JMXMFSZL','XT_SJ_JTSX','XT_SJ_LZHJ','XT_SJ_SLSXTZNR','XT_SJ_WINDOW','XT_SJ_WSSXX','XT_SJ_ZZFP','YHS_DM_YWDL','YHS_DM_YWXL','YHS_DW_CHILD','YHS_FORM_DW','YHS_FORM_WORDSET','YHS_FROM_SQLSET','ZF_DM_YHJB','ZF_XT_BSSX','ZF_XT_XMDY','ZF_XT_XMSZ','ZF_XT_ZBDY','ZF_XT_ZBSZ','ZHCX_SQL','ZHCX_TABLE')
AND OWNER IN ('SUPER'))
--执行结果发现7张表没有符合要求的索引，手工比较。
--7张表如下：
DM_SQRLX（相同），DM_SBXJQXVSNSQX（相同），DM_FLCLYJ（相同），DM_CZBUCFS（相同），DM_BZ（相同），DM_BBZL（相同），DM_BBLX（相同）。
--三张表ETL与MXDB中表结构不一致。无法使用DBMS_COMPARE进行比较，不进行处理。
--3张表如下：
DM_ZDGHYT，DM_YSJCFP，DM_SZDM_PIPE。
3.将108（118-7-3=108）张表加入ta_compare_tables。
insert into ta_compare_tables select table_name ,'20131101_108' from dba_tables where
owner='SUPER' AND
table_name in('DM_BZH','DM_BZSSX','DM_CFJDLX','DM_CHK_GSSYSZSL','DM_CHK_SMHY','DM_CHK_YSJC','DM_CHK_YSKM','DM_CHK_YSZSL','DM_CWZL','DM_CZPQ','DM_DBLINK','DM_DDXS','DM_DEXISHU','DM_DEXM','DM_DE_TZYY','DM_DLJG','DM_DQXS','DM_DXDC_DCXM','DM_DZLB','DM_DZLX','DM_FPKC_CZLX','DM_FPPZ_BBZL','DM_FPPZ_YSC','DM_FPZL','DM_FP_BGFS','DM_FP_HXLB','DM_FP_JXLX','DM_FP_SHJG','DM_FP_YJLB','DM_FY_FYSX','DM_FY_SJ','DM_FY_SQZT','DM_FZ_AJLY','DM_FZ_BLSS','DM_FZ_CFJD','DM_FZ_CFJY','DM_FZ_FLYJ','DM_FZ_KXSZ','DM_FZ_WFXZ','DM_FZ_WSJC','DM_FZ_WZXW','DM_FZ_ZXZT','DM_GLFW_JMSC','DM_GSDJ','DM_GSJG','DM_GZSZDYT','DM_HTPZZL','DM_HYDM','DM_JDWH','DM_JMXM','DM_JMXZ','DM_JSYJ','DM_JYFS','DM_KHXX','DM_KHYH','DM_KJHSLX','DM_KPLX','DM_KPZFYY','DM_KT_DZLX','DM_KZSZT','DM_LSGX','DM_MODEL','DM_NSQX','DM_NSRLX','DM_NSRZT','DM_PZCCLX','DM_PZCPMC','DM_PZDLDM','DM_PZJBDW','DM_PZJBRY','DM_PZLX','DM_PZMC','DM_PZXHYY','DM_PZZL','DM_PZZY','DM_PZ_SJLX','DM_PZ_YPDWFL','DM_QSLX','DM_QSZYLX','DM_QYGM','DM_RCJC_JCLX','DM_RCJC_JCZT','DM_RKXZ','DM_SBFS','DM_SBWXZL','DM_SBXJQX','DM_SHUIZHONG','DM_SKGK','DM_SKLY','DM_SM','DM_SPZT','DM_SWBM','DM_SWDJBLX','DM_SWJG','DM_SWXZXW','DM_SXF_YHDZCZY','DM_SZLKX','DM_SZPQ','DM_TDQDFS','DM_TDQSCSXS','DM_TTXZ','DM_TZXZXWYY','DM_TZZGJ','DM_WSDM','DM_WSZL','DM_WZLX','DM_XMGL_KBGX','DM_XSJC','DM_XZXK_XM','DM_YBSKJBB','DM_YHZJLX','DM_YJFS','DM_YSJC','DM_YSKM_PIPE','DM_ZCLX','DM_ZDH','DM_ZDLB','DM_ZGBM','DM_ZJFS','DM_ZJLX','DM_ZJZLZL','DM_ZJZT','DM_ZSDW','DM_ZSFS','DM_ZSFS_NEW','DM_ZXYY','DM_ZYDM','DM_ZZKM','DM_ZZLB')
and table_name in ('CWREPORT_NAME','CWREPORT_ROW_COL_TITLE','DM_BZH','DM_BZSSX','DM_CCSJMSZM','DM_CFJDLX','DM_CHK_GSSYSZSL','DM_CHK_SMHY','DM_CHK_YSKM','DM_CHK_YSZSL','DM_CWZL','DM_DBLINK','DM_DE_TZYY','DM_DQXS','DM_DXDC_DCXM','DM_DZLB','DM_DZLX','DM_FPKC_CZLX','DM_FPPZ_BBZL','DM_FPZL','DM_FP_BGFS','DM_FP_HXLB','DM_FP_JXLX','DM_FP_SHJG','DM_FP_YJLB','DM_FY_FYSX','DM_FY_SJ','DM_FY_SQZT','DM_FZ_AJLY','DM_FZ_BLSS','DM_FZ_CFJD','DM_FZ_CFJY','DM_FZ_FLYJ','DM_FZ_KXSZ','DM_FZ_WFXZ','DM_FZ_WSJC','DM_FZ_WZXW','DM_FZ_ZXZT','DM_GZSZDYT','DM_HTPZZL','DM_HYDM','DM_JMXM','DM_JMXZ','DM_JSYJ','DM_JYFS','DM_JZHTXM','DM_KJHSLX','DM_KPLX','DM_KPZFYY','DM_KT_DZLX','DM_KZSZT','DM_LSGX','DM_MODEL','DM_NSQX','DM_NSRLX','DM_NSRZT','DM_PZCCLX','DM_PZCPMC','DM_PZDLDM','DM_PZLX','DM_PZXHYY','DM_PZZL','DM_PZZY','DM_PZ_SJLX','DM_QSLX','DM_QSZYLX','DM_QYGM','DM_RCJC_JCLX','DM_RCJC_JCZT','DM_RKXZ','DM_SBFS','DM_SBWXZL','DM_SBXJQX','DM_SHUIZHONG','DM_SKLY','DM_SM','DM_SPZT','DM_SWDJBLX','DM_SWJG','DM_SWXZXW','DM_SXF_YHDZCZY','DM_SZLKX','DM_TDQDFS','DM_TDQSCSXS','DM_TTXZ','DM_TZXZXWYY','DM_TZZGJ','DM_WSDM','DM_WSZL','DM_WZLX','DM_XMGL_KBGX','DM_XSJC','DM_XZXK_XM','DM_YBSKJBB','DM_YHZJLX','DM_YJFS','DM_YSJC','DM_YSKM_PIPE','DM_ZCLX','DM_ZDH','DM_ZDLB','DM_ZGBM','DM_ZGZL_KHXM','DM_ZJFS','DM_ZJLX','DM_ZJZLZL','DM_ZJZT','DM_ZSFS','DM_ZSFS_NEW','DM_ZXQY','DM_ZXQYBZ','DM_ZXYY','DM_ZYDM','DM_ZZKM','DM_ZZLB','FP_BB','FP_LSLW_PM','FZ_CXDM','FZ_CXDYDM','GLFW_BZDMDJB','GLFW_DQJFF','GLFW_GHKHYH','GLFW_KBGXM','GLFW_WS_WSWH','JC_CX_FLFG','JC_DM_AJSLYJ','JC_DM_AJZLLB','JC_DM_BGQX','JC_DM_JBXS','JC_DM_JCHJ','JC_DM_LX','JC_DM_LY','JC_DM_SLCX','JC_DM_WFCY','JC_DM_WFWZLX','JC_DM_WFWZSD','JC_DM_WSLX','JC_DM_ZT','JC_WH_ZB','JC_WSSP_DWO','KT_BGK_ZJYT','KT_ZSK_KXLB','KT_ZSK_ZJXS','REPORT_CHECK_OUT','REPORT_COUNT_SET','REPORT_HZCS','REPORT_NAME','REPORT_ROW_COL_TITLE','REPORT_SQL','SJ_DW_INFO','SJ_FWXM_LINK','SJ_FWXM_SJXKZ','SJ_HOLIDAY_SET','TABLE_OPER_POLICY','WH_DMCLASS','WH_DMGL','XT_GDFWXM_SET','XT_JC_JCWS','XT_JC_JTSX','XT_SJ_DOCCODE','XT_SJ_FWXM','XT_SJ_GWSZ','XT_SJ_JMXMFSZL','XT_SJ_JTSX','XT_SJ_LZHJ','XT_SJ_SLSXTZNR','XT_SJ_WINDOW','XT_SJ_WSSXX','XT_SJ_ZZFP','YHS_DM_YWDL','YHS_DM_YWXL','YHS_DW_CHILD','YHS_FORM_DW','YHS_FORM_WORDSET','YHS_FROM_SQLSET','ZF_DM_YHJB','ZF_XT_BSSX','ZF_XT_XMDY','ZF_XT_XMSZ','ZF_XT_ZBDY','ZF_XT_ZBSZ','ZHCX_SQL','ZHCX_TABLE')
--检查一下
select * from ta_compare_tables where table_owner = '20131101_108';
4.利用MANUAL_COMPARE过程比较（这里因业务仅需比较即可）。
在test窗口运行该过程，参数为：1,0,0,0，'20131101_108'。
5.在日志表中查看比较结果。
select * from ta_compare_log where COMPARE_OWNER = '20131101_108';
--21张增量表处理：
--手工比较
select * from 增量表名;
select * from 增量表名@GZDSETL.GZDS.GOV.CN;
后记：
利用DBMS_COMPARISON包比较118张表效率是非常快的，几秒钟就OK了。对于字段不同（名字不同，数量不同）的全量表，DBMS_COMPARISON也能进行处理。但是对于增量表（带条件）的对比，DBMS_COMPARISON包暂时无能为力或是本人暂时未找到解决方案。
转载：http://blog.sina.com.cn/s/blog_62defbef0101ob7x.html
评论已关闭