经常遇到这样的问题,业务系统表结构变更,没有通知到下游系统,导致下游系统出错,业务系统和下游系统之间使用merge增量同步数据的,有什么好的解决办法没有?
------解决方案--------------------
做merge之前先查询一下表结构有没有不同?
大概是:
select a.table_name,a.column_name,a.data_type,a.data_length,
b.table_name,b.column_name,b.data_type,b.data_length
from user_tab_columns a full join user_tab_columns@dblink_name b
on a.table_name=b.table_name and a.COLUMN_NAME=b.COLUMN_NAME
and a.data_type=b.data_type and a.DATA_LENGTH=b.DATA_LENGTH
where a.table_name='LOGMNRC_GTCS' and ( a.table_name is null or b.table_name is null)
------解决方案--------------------
写了一个触发器 只是不知道会不会影响效率
---ddl日志
--drop table sys_ddl_log;
create table sys_ddl_log (
c_ora_sysevent varchar2(100),
c_login_user varchar2(100),
c_dictionary_obj_type varchar2(100),
c_dictionary_obj_name varchar2(100),
c_dictionary_obj_owner varchar2(100),
c_oper_date date
);
--- 受保护的对象
drop table sys_protect_user_obj;
create table sys_protect_user_obj(
c_obj_owner varchar2(100),
c_obj_type varchar2(100),
c_obj_name varchar2(100)
);
INSERT INTO sys_protect_user_obj (c_obj_owner, c_obj_type ,c_obj_name )
select T.OWNER,T.OBJECT_TYPE,T.OBJECT_NAME from dba_objects t where t.owner in ('***','***','****','****') AND OBJECT_TYPE IN ('TABLE','VIEW','MATERIALIZED VIEW','PROCEDURE');
COMMIT;
---记录ddl的事件
create or replace trigger trig_log_afterddl
after ddl on database
declare
COU NUMBER;
begin
if ora_sysevent='DROP' then
select COUNT(*) into COU from sys_protect_user_obj where
c_obj_owner = ora_dict_obj_owner and
c_obj_name = ora_dict_obj_name and
c_obj_type=ora_dict_obj_type ;
if COU>0 then
raise_application_error(-20001,'该对象受到保护,禁止删除。请联系管理员!');
end if;
else
insert into sys_ddl_log(c_ora_sysevent ,c_login_user, c_dictionary_obj_type, c_dictionary_obj_name ,c_dictionary_obj_owner ,c_oper_date )
values(ora_sysevent , ora_login_user,ora_dict_obj_type, ora_dict_obj_name ,ora_dict_obj_owner,sysdate);
end if ;
end;