在使用job刷新数据时,由于trigger阻止了删除动作,导致mlog@_china的数据没有被自动删除.越积累越多.
请问有什么方法可以自动清理?
create table china
(
ids number primary key,
names varchar2(20)
)
create table f_china
(
ids number primary key,
names varchar2(20)
)
创建MV_log
CREATE MATERIALIZED VIEW LOG ON china
TABLESPACE CONT NOCACHE LOGGING NOPARALLEL WITH PRIMARY KEY;
创建mv
CREATE MATERIALIZED VIEW f_china
ON PREBUILT TABLE WITH REDUCED PRECISION
REFRESH FAST ON DEMAND
WITH PRIMARY KEY
FOR UPDATE
AS
SELECT * FROM CHINA;
创建job
BEGIN
DBMS_SCHEDULER.CREATE_JOB(job_name => ’JOB_’ || table_name,
schedule_name => schedule_name,
job_type => ’PLSQL_BLOCK’,
job_action => ’BEGIN DBMS_SNAPSHOT.REFRESH(LIST => ”’ ||
table_name || ”’); END;’);
DBMS_SCHEDULER.ENABLE(name => ’JOB_’ || table_name);
exception
when others then
Dbms_Output.put_line(SQLCODE || SQLERRM);
execption:=SQLCODE || SQLERRM;
RAISE_APPLICATION_ERROR(-20007,execption);
END;
创建tirgger
create or replace trigger t_mlog_CHINA
before insert
on mlog$_CHINA
for each row
begin
if :new.dmltype$$ = ’D’
then
:new.snaptime$$ := ”;
end if;
end t_mlog_CHINA;
如果修改了主键,mlog$_china就会把一笔资料记成两笔,导致job刷新后变成了2笔资料.例如:
insert into china values(1,’a');
update china set ids=2 where names=’a';
1 4000-1-1 I N FE
2 4000-1-1 I N FF
1 D O 00
JOB刷新后在f_china表中就会插入
1,’a’
2,’a’
两笔数据.
请问怎么避免这种情况的发生.