一:问题描述

有两个Oracle数据库,分别布置在不同的服务器上,系统均为windows2003;

这里暂且说成是一个主数据库和从数据库:

(1) 主数据库: oracle_A ;

(2) 从数据库: oracle_B ;

在oracle_A中有一个表table_A与oracle_B中的表table_B 结构相同 ;

我是处在oracle_B,oracle_A数据库分配给我有一个访问oracle_A表table_A的用户,该用户 只拥有查询的权限 ;

另外,需要 说明的一点 ,就是在oracle_B处,只需对table_B表进行查询的操作,不进行其他增删改的操作。

场景介绍完了, 我的问题 的是,如何在oracle_A中表table_A发生变化时,实时更新同步到oracle_B的table_B中?

我原来的处理方式:

通过建立远程连接DBLink+JOB定时任务+存储过程的方式,实现了定时同步 更新,但不能做到实时同步 。

二:采用同义词+DB_Link的方式结果步骤

之所以能够选择采用同义词的方式,处理这个问题。主要还是源于在问题描述中提到一个点,那就是我们只需要对同步后的表进行 查询 操作。这点是使用同义词方式的重要要素。

下面详细模拟一下整个实验测试的过程:

(1)首先在Oracle_A端创建一个对table_A只有查询功能的用户

  1. 创建用户
    sqlplus /nolog
    conn /as sysdba;
    create user username identified by password;

  2. 查看所有的用户列表
    用户创建完成后,查看用户是否创建成功
    select * from all_users;

  3. 授予权限
    为了能够保证能够登陆,必须赋予如下权限

    • 授予username用户创建session的权限,即登陆权限
      grant create session to username;

    • 授予username用户使用表空间的权限
      grant unlimited tablespace to username;

    • oracle对权限管理比较严谨,普通用户之间也是默认不能互相访问的,需要互相授权.

    • 如果scott用户要授权给username用户查看自己的test表的权限;
      sqlplus scott/tiget@localhost:1521/orcl

    • 授予username用户查看指定的权限
      grant select on test to username;

    • 撤销权限
      基本语法同grant,关键字为revoke;

(2)验证用户是否可以成功登录,并进行访问授权的表

使用sqlplus登录,并进行查询

sqlplus username/password@localhost:1521/orcl;
select * from scott.test;

注意:查询表时,务必带上用户名,说明是哪个用户下的表。

  1. 创建远程连接 db_link

    1
    create public database link db32 connect to tianzhi_test identified by "tianzhi_test" using '192.168.56.6:1521/ORCL'
  2. 测试远程连接是否成功

    1
    select * from tianzhi_smart.zh_item_news@db32;

(4)在Oracle_B端创建同义词

  1. 使用sqlplus登录自己的用户

    1
    sqlplus tianzhi_smart/tianzhi_smart@localhost:1521/orcl
  2. 创建同义词

    1
    create or replace public synonym TEST1130 for scott.TEST@db32;

3.查询测试

1
select * from TEST1130;

可以看到这与在Oracle_A源数据库中查到的table_A表中的数据一样.
注意事项:

当远程查询的数据库中包含BLOB字段时,会报出如下错误.

1
ORA-22992: 无法使用从远程表选择的 LOB 定位器

当出现这个错误的时候,那是因为跨库连接查询中的表中存在BLOB类型的字段,所以一定要注意,所有表中存在blob类型字段,

不能用 select * from 连接的表
不能将blob类型的字段出现在脚本中。
如果这些blob类型的字段一定要导过来,可以先建立临时表再插入本地表,方法如下.在pl/sql中执行

第一步 建临时表

1
2
create global temporary table foo ( X BLOB )
on commit delete rows;

第二步 插入本地表

1
insert into foo select blobcolumn from remoteTable@dl_remote ;