【Oracle数据库使用DBLINK创建快照视图实现数据定时同步】

最新推荐文章于 2023-11-25 10:50:22 发布

RosmaryYu

最新推荐文章于 2023-11-25 10:50:22 发布

阅读量803

点赞数

分类专栏： Oracle 文章标签： oracle sql dba 后端 java

本文链接：https://blog.csdn.net/qq_42563173/article/details/123597390

版权

Oracle 专栏收录该内容

3 篇文章 0 订阅

订阅专栏

前言

因为之前的项目在数据对接过程中使用到了相关技术，特地记录一下相关知识，以及对接后使用过程中出现的一些问题。

操作和说明

通过DBLINK技术建立起两个数据库之间的单向连接

首先对接公司创建好数据库用户并设置用户数据库的相关权限；
然后通过对接公司提供的数据库用户信息和链接地址来创建DBLINK连接；

-- 创建连接
--		相关数据说明：
--			TEST_LINK：创建的链接名称（自定义）
--			root：提供的数据库登录用户名
--			123456：提供的数据库登录密码
--			127.0.0.1：提供的数据库链接地址
--			1512：提供的数据库链接端口
--			TEST：提供的数据库服务名（一般是ORCL）
-- Drop existing database link 
drop database link TEST_LINK;
-- Create database link 
create database link TEST_LINK
connect to root identified by 123456
using '(DESCRIPTION =
   (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))
   (CONNECT_DATA =
     (SERVER = DEDICATED)
     (SERVICE_NAME = TEST)
   )
 )';

在PLSQL工具里执行完SQL后刷新Database links文件夹即可看到创建的链接了；

根据DBLINK连接创建Snapshot，生成相关View

根据对接公司授权给用户的相关数据库表，创建相关表的Snapshot并生成相关Materialzed view；

-- 创建快照设置增量更新，立即刷新快照，下一次刷新时间为当前时间加1小时，快照数据及格式来自TEST_LINK链接获取到的SYS_USER表
-- 相关数据和属性说明：
--		SYS_USER：生成的快照名
--		refresh：表示快照刷新方式（fast：增量更新，complete：完全更新）
--		start with sysdate：创建并立即更新（可自定义更新时间，也可以不要此属性，自己手动刷新）
--		next：设置快照下一次自动刷新的时间，添加此参数会生成DBMS_Job定时任务
--		SYS_USER@TEST_LINK：表示TEST_LINK的SYS_USER表
create snapshot SYS_USER
refresh fast  
start with sysdate
next (sysdate + 1/24)
as select * from SYS_USER@TEST_LINK;

在PLSQL工具里执行完SQL后刷新相关文件夹即可看到创建的视图和任务；
创建过程中可能会提示一些错误信息：

ORA-23413: 表 “xxx”.“xx” 不带实体化视图日志，此时就需要对接公司创建相关表的视图日志（可以根据rowid或primary key创建）：CREATE MATERIALIZED VIEW LOG ON SYS_USER WITH PRIMARY KEY;
遇到报错根据提示一步步解决即可，此处就不一一列举，因为每个人遇到的报错信息可能都有些不一样；

查看快照执行情况

--查看所有快照最后一次刷新时间
SELECT NAME,LAST_REFRESH FROM ALL_SNAPSHOT_REFRESH_TIMES;

--查看定时任务下次执行时间
select last_date,next_date,what from user_jobs order by next_date;

--查看定时任务 INTERVAL间隔 TOTAL_TIME所花时间
select last_date,next_date,INTERVAL,TOTAL_TIME,what from user_jobs;

--立即同步快照（对象点快照名称或者直接快照名称）
begin
  dbms_refresh.refresh('"SYSTEM"."SYS_USER"');
end;

问题

更新快照时物化视图被锁，导致更新快照失败；

-- 查询数据库相关锁
SELECT
	l.oracle_username,
	o.owner,
	o.object_name,
	o.object_type,
	s.sid,
	s.serial#,
	p.spid 
FROM
	v$locked_object l,
	dba_objects o,
	v$session s,
	v$process p 
WHERE
	l.object_id = o.object_id 
	AND l.session_id = s.sid 
	AND s.paddr = p.addr 
ORDER BY
	o.object_id;

可以使用plsql图形化界面杀掉会话：alter system kill session ‘sid,serial#’;
也可以在linux或windows系统中杀掉进程：

kill -9 spid （linux：上一步查出来的进程id）
orakill sid spid （windows：进程属于的实例名，不是sid）

查询导致锁表的SQL

-- &pid：是导致锁表的数据库进程id
SELECT
sql_text
FROM v$sqltext a
WHERE (a.hash_value, a.address) IN
( SELECT
DECODE( sql_hash_value, 0, prev_hash_value, sql_hash_value ),
DECODE(sql_hash_value, 0, prev_sql_addr, sql_address)
FROM v$session b
WHERE b.paddr =
(SELECT addr FROM v$process c WHERE c.spid = '&pid'))
ORDER BY piece ASC