物化视图上用触发器(用于同步)
作用：由数据源的主表与明细表在目标库进行合并
1，在数据源上建两表，一主，一明细 ,在目标库建引两表的合并表
-- Create table
create table TT_ACC_LEVYCODE主表
(
SESSION_ID NUMBER(10) not null,
LEVYCODE VARCHAR2(11),
KTZC_SESSION_ID VARCHAR2(7)
);
alter table TT_ACC_LEVYCODE
add constraint FKDD primary key (SESSION_ID)
using index
tablespace ZHENGGUAN
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
-- Create table明细表
create table TT_ACC_LEVYDETAILCODE
(
DEATID_ID VARCHAR2(10) not null,
SESSION_ID NUMBER(10),
DETAIL_NAME VARCHAR2(50)
)
tablespace ZHENGGUAN
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
-- Create/Recreate primary, unique and foreign key constraints
alter table TT_ACC_LEVYDETAILCODE
add constraint RFGDFGDF primary key (DEATID_ID)
using index
tablespace ZHENGGUAN
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
alter table TT_ACC_LEVYDETAILCODE
add constraint DDDEEEEE foreign key (SESSION_ID)
references TT_ACC_LEVYCODE (SESSION_ID);
-- Create table
create table TT_ACC_LEVYCODEINFO
(
SESSION_ID NUMBER(10) not null,
LEVYCODE VARCHAR2(11),
KTZC_SESSION_ID VARCHAR2(7),
DEATID_ID VARCHAR2(10) not null,
DETAIL_NAME VARCHAR2(50)
)
tablespace OWB_TG
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 80K
minextents 1
maxextents unlimited
);
-- Grant/Revoke object privileges
grant select, insert, update, delete on TT_ACC_LEVYCODEINFO to STAGING;--注：要把这些权限赋予中转用户
在数据源上建物化视图log
create materialized view log on tt_acc_levycode tablespace zhengguan_sn_log;
create materialized view log on tt_acc_levydetailcode tablespace zhengguan_sn_log;
-------------
2，在中转区建立两源表的物化视图
create materialized view tt_acc_levycode tablespace staging refresh fast as
select t.*
from [email protected] t;
create materialized view tt_acc_levydetailcode tablespace staging refresh fast as
select t.*
from [email protected] t;
--LNK27.REGRESS.RDBMS.DEV.US.ORACLE.COM 为中转区到数据源的dblink
3，在中转用户建立触发器
CREATE OR REPLACE TRIGGER WB_tr_acc_levydetailcode
after INSERT OR UPDATE
ON tt_acc_levydetailcode
FOR EACH ROW
DECLARE
v_SESSION_ID NUMBER(10) ;
v_LEVYCODE VARCHAR2(11);
v_KTZC_SESSION_ID VARCHAR2(7);
BEGIN
if INSERTING then
select t.session_id,t.levycode,t.ktzc_session_id
into v_session_id,v_levycode,v_ktzc_session_id
from tt_acc_levycode t
where t.session_id =:new.session_id;
insert into owb_tg.tt_acc_levycodeinfo values(v_SESSION_ID,v_LEVYCODE,v_KTZC_SESSION_ID,:new.DEATID_ID,:new.DETAIL_NAME);
end if;
if UPDATING then
update owb_tg.tt_acc_levycodeinfo t set t.detail_name=:new.detail_name
where t.deatid_id=:new.deatid_id;
end if;
END;
手工刷新视图
execute dbms_mview.refresh('tt_acc_levycode','f');
execute dbms_mview.refresh('tt_acc_levydetailcode','f');
这样执行可保证先后，若全写一起则并发执行
execute dbms_mview.refresh('tt_acc_levycode,tt_acc_levydetailcode','f');