续前一博文
--创建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;