oracle创建snapshot,创建snapshot步骤及详细注解

快照名称：sn_表名

快照触发器名称：tr_表名

主库运行：

drop snapshot log on TABLENAME;

create snapshot log on TABLENAME;

备库运行：

-------------------

drop snapshot sn_TABLENAME;

create snapshot sn_TABLENAME

refresh fast Start with sysdate next sysdate with primary key

as select * from TABLENAME@from_primary;

create or replace trigger tr_TABLENAME

after insert or update or delete on sn_TABLENAME

for each row

begin

if deleting then

delete from TABLENAME t where t.SERV_ID=:old.SERV_ID and

t.CHAR_ID=:old.CHAR_ID;

end if;

if inserting then

insert into TABLENAME(SERV_ID ,CHAR_ID ,VALUE ,STATE

,MOD_USER_ID,MOD_DATE)

values(:new.SERV_ID ,:new.CHAR_ID ,:new.VALUE ,:new.STATE

,:new.MOD_USER_ID,:new.MOD_DATE);

end if;

if updating then

update TABLENAME set

VALUE = :new.VALUE ,

STATE = :new.STATE ,

MOD_USER_ID = :new.MOD_USER_ID,

MOD_DATE = :new.MOD_DATE

where SERV_ID=:old.SERV_ID and CHAR_ID=:old.CHAR_ID;

end if;

end;

/

注解：如果有主键，则snapshot按照主键来同步数据create snapshot log on TABLENAME;

如果没有主键则按照rowid来同步数据create snapshot log on TABLENAME with rowid;

备库有主键建snapshot:

create snapshot sn_TABLENAME

refresh fast Start with sysdate next sysdate with primary key

as select * from TABLENAME@from_primary;

备库没有主键建snapshot:

create snapshot sn_TABLENAME

refresh fast Start with sysdate next sysdate with rowid

as select * from TABLENAME@from_primary;