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_id
from TbAsd@dblink_test bjb
full join TbAsd s on bjb.asd_arrive_time=s.asd_arrive_time
where 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_time
from TbAsd@dblink_test t;

--3b.在本地数据库创建快照--耗时2秒
drop snapshot sn_add;
/
create snapshot sn_add as 
select asd_id, add_order, add_car_number
from 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 row
declare
  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_broke
as
begin
  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;
 
 
-- 运行job
begin
   dbms_job.run(XXX);--XXX是jobID， 和select * from user_jobs; 中的job值对应，看what对应的过程
end; 
 
-- 删除一个job
begin
   dbms_job.remove(XXX);--XXX是jobID，和select * from user_jobs; 中的job值对应，看what对应的过程
end;