Oracle下dblink下的创建与使用

1.跨数据库节点创建dblink

访问库：
10.156.10.182
被访问库：
10.156.11.79（dg备库）---10.156.11.31/32/33（dg主库）
为了分离DG库的压力，访问库通过dblink从dg备库取数。


1:被访问库创建用户赋权：
create user query_zbx identified by 111;
grant connect to query_zbx;
grant select on ELITE.GD_HF_POL_ADD to query_zbx;

2：访问库上创建dblink
create public database link to_cc
connect to query_zbx identified by 111
using '(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.156.11.79)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = ccprdg)
)
)';
3：验证dblink：
SQL> create public database link to_cc
  2  connect to query_zbx identified by 111
  3  using '(DESCRIPTION =
  4  (ADDRESS_LIST =
  5  (ADDRESS = (PROTOCOL = TCP)(HOST = 10.156.11.79)(PORT = 1521))
  6  )
  7  (CONNECT_DATA =
  8  (SERVICE_NAME = ccprdg)
  9  )
 10  )';

Database link created.

SQL> select sysdate from dual@to_cc;

SYSDATE
---------
06-MAR-18




4：对比dg主库，将dblink配置修改成主库：
SQL> create public database link to_cc
  2  connect to query_zbx identified by 111
  3  using '(DESCRIPTION =
  4  (ADDRESS_LIST =
  5  (ADDRESS = (PROTOCOL = TCP)(HOST = 10.156.11.31)(PORT = 1521))
  6  (ADDRESS = (PROTOCOL = TCP)(HOST = 10.156.11.32)(PORT = 1521))
  7  (ADDRESS = (PROTOCOL = TCP)(HOST = 10.156.11.33)(PORT = 1521))
  8  )
  9  (CONNECT_DATA =
 10  (SERVICE_NAME = ccpr)
 11  )
 12  )';

Database link created.

SQL> select sysdate from dual@to_cc;

SYSDATE
---------
06-MAR-18

SQL> select count(*) from ELITE.GD_HF_POL_ADD@to_cc;

  COUNT(*)
----------
    130124

2.不跨节点的数据的的blink创建

*****************
创建用户test1、test2
*****************
SQL> create user test1 identified by test1;

User created.

SQL> create user test2 identified by test2;

User created.


*****************
用户赋权
*****************
SQL> grant connect,resource to test1;

Grant succeeded.

SQL> grant connect,resource to test2;

Grant succeeded.



*****************
test1创建dblink
*****************
grant create public database link to test1;

create public database link test1_test2 connect to test2 identified by test2 using 'dblink'; 

Database link created.


*****************
用户test2测试dblink
*****************
SQL> select sysdate from dual@test1_test2;

SYSDATE
------------------
28-NOV-18

*****************
用户test3测试dblink
*****************
SQL> create user test3 identified by test3;

User created.

SQL> grant connect,resource to test3;

Grant succeeded.

SQL> connect test3/test3
Connected.
SQL> select sysdate from dual@test1_test2;

SYSDATE
------------------
28-NOV-18