Oracle DBlink

来源：互联网发布：mac退出后台程序编辑：程序博客网时间：2024/05/18 01:26

在创建oracle dblink之前先做他们的网络监听，使得两个服务器能相互连接。

source：192.168.1.24

client： 192.168.1.91

第一，网络配置：

source:

tnsnames.ora

91 =
   (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.91)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = orcl)
    )
)

conn scott/tiger@91

client:

tnsnames.ora

24 =
   (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.24)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = orcl)
    )
)

conn scott/tiger@24 相互验证

第二，查看global_name

select * from global_name 或者 show parameter global_name 看global_name的值

如果值是true的话改成false

alter system set global_name=false;

第三查看远程数据库是还不是支持高级复制功能

select * from v$option where parameter='Advanced replication'

第四创建dblink

create public database link service connect scott identified by tiger using '24' ; service是dblink的名字 24是连接串的名字

第五验证

select 'insert into ' ||tname|| ' as select * from ' ||tname|| '@service' From tab where tname not like 'BIN%'