oracle 刷新组保证主从表的数据同步拷贝

续前一博文

--创建dblink的语法--场景，从远程机器数据库上取数据，写入到本地数据库中--删除drop public database link dblink_test;--1.本地服务器 创建create public database link dblink_test  --link名称，自定义connect to orclUser  --远程机器上的oracle 用户名identified by orclPwd --远程机器上的oracle 密码using 'ORCL_10G_192.168.7.15';  --本地服务器上建立的访问远程oracle的Net服务名  --（注意，如果装了oracle服务端和客户端，那么都要配置这个Net服务名，防止提示错误:ora-12154 无法解析指定的连接描述符）  --查询远程数据示例代码--发现，即使是SELECT 一个DBLINK，也会有COMMIT按钮，说明有可能锁住远程表。查找相关资料，大致意思是属于分布式查询，以事务形式提交，因此在查询完后尽量使用COMMIT或ROLLBACK来释放锁。切记select t.asd_id,t.asd_arrive_time from aei_sum_data@dblink_test t;commit;--注意，在使用dblink时一定要commit，这个与普通查询不同，要注意--注意，dblink模式下查询blob等大数据类型时会出错，要注意避免查询lob类型--select * from TbAsd@dblink_test t;--如果在使用dblink进行查询时因网络问题导致查询失败，那么oracle会返回ora-02068和ora-03113错误。--对比数据select bjb.asd_id, bjb.asd_arrive_time bjb_time,s.asd_arrive_time s_time ,s.asd_idfrom TbAsd@dblink_test bjbfull join TbAsd s on bjb.asd_arrive_time=s.asd_arrive_timewhere bjb.asd_id is null or s.asd_id is null order by bjb.asd_arrive_time;select count(*) from TbAsd@dblink_test;select count(*) from TbAsd;--查询远程数据select t.asd_id,t.asd_arrive_time,t.ai_no from TbAsd@dblink_test t order by t.asd_arrive_time desc;--########################请注意，这句代码是在远程执行###############################--2a.远程服务器创建快照，!!!!!!!!!!!注意此时要切换到远程服务器的用户登录create snapshot log on TbAsd;--2b.远程服务器创建快照，!!!!!!!!!!!注意此时要切换到远程服务器的用户登录create snapshot log on TbAdd;--3a.在本地数据库创建快照--耗时2秒drop snapshot sn_asd;/create snapshot sn_asd as select asd_id, asd_arrive_timefrom TbAsd@dblink_test t;--3b.在本地数据库创建快照--耗时2秒drop snapshot sn_add;/create snapshot sn_add as select asd_id, add_order, add_car_numberfrom TbAdd@dblink_test ;--4a.设置快照刷新时间--快速刷新模式  30秒后开始，然后每间隔30秒刷新一次,--oracle时间运算的基本单位是天，如果要得到秒，那么1s就是1/(1天*24小时*60分钟*60秒)alter snapshot sn_asd refresh fast;/ -- start with sysdate next sysdate+30/(1*24*60*60);--注意，这里未设置快照刷新时间，因此也不会自动生成job--4b.设置快照刷新时间--快速刷新模式  30秒后开始，然后每间隔30秒刷新一次,--oracle时间运算的基本单位是天，如果要得到秒，那么1s就是1/(1天*24小时*60分钟*60秒)alter snapshot sn_add refresh fast;/-- start with sysdate next sysdate+30/(1*24*60*60);--注意，这里未设置快照刷新时间，因此也不会自动生成job--新建一个刷新组  （当向刷新组内添加物化视图后此刷新组自动创建一个job来执行刷新）begin   DBMS_REFRESH.MAKE (       name => 'rep_refresh',       list => '',       next_date => SYSDATE,       interval => 'sysdate+30/(1*24*60*60)'   );   end;  /   --添加MV到一个刷新组  begin   DBMS_REFRESH.ADD(      name => 'refreshGrpTest',      list => 'sn_asd'      );  end;  /  --添加MV到一个刷新组  begin   DBMS_REFRESH.ADD(      name => 'refreshGrpTest',      list => 'sn_add'      );  end;  / --查询刷新组--只有在刷新组内添加物化视图后才能查到结果select * from dba_refresh_children;--删除刷新组begin dbms_refresh.destroy('refreshGrpTest'); end; /--注意，（当向刷新组内添加物化视图后此刷新组自动创建一个job来执行刷新，此时无需我们手动再添加job）--5a.本地针对快照创建触发器  (本触发器使用的前提是已创建刷新组并且刷新组中TbAsd和TbAdd是同一时刻刷新的)create or replace trigger TRI_SN_ASD  after insert on sn_asd  for each rowdeclare  sameCount number(5) default(0);begin    --注意，这里代码执行的前提是已创建刷新组并且刷新组中TbAsd和TbAdd是同一时刻刷新的    --根据快照中远程数据库记录id，查看本地数据库中是否有重复，若无重复则进行新增    select count(asd_id) into sameCount from TbAsd t where t.asd_id=:new.asd_id;    if(sameCount=0)     then          --先insert到主表      insert into TbAsd(asd_id, asd_arrive_time)          values( :new.asd_id,  :new.asd_arrive_time);            --再insert的从表      insert into TbAdd(asd_id, add_order, add_car_number)       select asd_id, add_order, add_car_number         from sn_add where asd_id=:new.asd_id;            end if;end;--A1-创建存储过程查看job是否broke为Y，若为Y，则开启create or replace procedure proc_check_sn_job_brokeasbegin  for curJob in(select * from user_jobs) loop      dbms_output.put_line(curJob.Job||'-'||instr(curJob.What,'proc_check_sn_job_broke'));      --查询job中已broken的，并且job不是自身的job(防止死循环)      if(curJob.Broken='Y' and instr(curJob.What,'proc_check_sn_job_broke')=0) then        begin        --启动job          dbms_job.broken(curJob.Job,false);        exception          WHEN OTHERS THEN               DBMS_OUTPUT.PUT_LINE(SQLCODE||'---'||SQLERRM);          rollback;        end;      end if ;  end loop;end;/--A2-创建job定期执行存储过程declare  jobNo number;begin  sys.dbms_job.submit(jobNo,                      'proc_check_sn_job_broke;',                      sysdate,                       'sysdate+1/(1*24*60)');  commit;end;/select * from dba_jobs_running;  -- 运行jobbegin   dbms_job.run(XXX);--XXX是jobID， 和select * from user_jobs; 中的job值对应，看what对应的过程end;  -- 删除一个jobbegin   dbms_job.remove(XXX);--XXX是jobID，和select * from user_jobs; 中的job值对应，看what对应的过程end;