有两个远程数据库,分别为HDPI_DEV和dtcenter,这两个数据库的连接的tnsnames配置,分别如下:
HDPI_DEV =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.158.176.51)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = test)
)
)
DTCENTER =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = tcp)(HOST = 10.158.188.106)(PORT = 1521))
(LOAD_BALANCE = yes)
)
(CONNECT_DATA =
(SERVICE_NAME = dtcenter)
)
)
默认情况下这两个数据库之间的表是不能互相调用的,也就是我在我自己机器的PL/SQL远程连接到其中一个数据库HDPI_DEV
之后,默认是不能对数据库DTCENTER
中的表进行查询操作的,现在我通过PL/SQL远程连接数据库HDPI_DEV
之后,在数据库HDPI_DEV
上面创建一个DATABASE LINK,实现对数据库DTCENTER
中的表的访问。创建过程如下:
CREATE DATABASE LINK connect_to_dtcenter
CONNECT TO comm IDENTIFIED BY password
USING '(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = tcp)(HOST = 10.158.188.106)(PORT = 1521))
(LOAD_BALANCE = yes)
)
(CONNECT_DATA =
(SERVICE_NAME = dtcenter)
)
)';
其中,connect_to_dtcenter
为DATABASE LINK名称,comm
为数据库DTCENTER
的用户,password
为用户comm
对应的密码。
创建DATABASE LINK的前提是登录的用户具有创建DATABASE LINK的权限,而且创建的DATABASE LINK是单向的,在这里数据库HDPI_DEV
可以访问数据库DTCENTER
中的表,反过来不行。
使用示例:
SELECT SUM(A.POWER_GEN) FDL_DR, B.ENTITY_ID
FROM datacenter.GRIDPOWER_FCT@connect_to_dtcenter A , datacenter.HDPI_SET@connect_to_dtcenter B
WHERE A.SET_ID = B.SET_ID
AND A.CALENDAR_ID = '20101227'
GROUP BY B.ENTITY_ID
参考文章:
- https://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_5005.htm;
- http://www.cnblogs.com/zmlctt/p/3749029.html;
- http://www.cnblogs.com/sumsen/archive/2013/03/04/2943471.html;
最新评论
黑镜4k被和谐,哥
地址已经无效
谢谢,大神的分享
你们都能正常用吗?我怎么不能呀,没有几个能放