oracle怎么tblink_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