Oracle dblink造成远程数据库session过多

现场报网公司数据库连不上,先检查了下数据库processes=1500,session=2200.我觉得很大啊,这个数据库没有几个人用。

查看v$session中的session最多是哪个machine发起的,发现是省公司的数据库发起的session,找开发梳理了下业务,省公司同步dblink操作网公司表,且是通过weblogic的连接池。

哦,有点明白了,是dblink引起的,weblogic连接池是一直存在的,所以在网公司端session是不释放的,如果省公司把应用都停掉,那在网公司端的session都会释放。要验证想法,做个试验:

目标:数据库A上建dblink,修改数据库B上的表。

环境准备:

1.在数据库A上建dblink

create public database link TO_B

connect to TEST_DB identified by  TEST_DB

using '(DESCRIPTION =(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = 10.10.15.150)(PORT = 1521)))(CONNECT_DATA =(SID = orcl)))';

2.在数据库B上建表

create table TEST(  ID NUMBER );

insert into test values(1);

开始测试:

1.在数据库B上select count(1) from v$session;

2.在数据库A上update test@TO_B set id=1;

commit;

3.在数据库B上select count(1) from v$session; 可以看到涨了一个

对比测试:

1.在数据库B上select count(1) from v$session;

2.在数据库A上update test@TO_B set id=1;

commit;

alter session close database link TO_B;

3.在数据库B上select count(1) from v$session; 可以看到没有变化

总结: 出现这种问题,归根结底是操作dblink不规范,用了之后没有关闭。

附录为官方文档:

32 Managing a Distributed Database Closing Database Links

If you access a database link in a session, then the link remains open until you close the session. A link is open in the sense that a process is active on each of the remote databases accessed through the link. This situation has the following consequences:

If 20 users open sessions and access the same public link in a local database, then 20 database link connections are open.

If 20 users open sessions and each user accesses a private link, then 20 database link connections are open.

If one user starts a session and accesses 20 different links, then 20 database link connections are open.

After you close a session, the links that were active in the session are automatically closed. You may have occasion to close the link manually. For example, close links when:

The network connection established by a link is used infrequently in an application.

The user session must be terminated.

To close a link, issue the following statement, where linkname refers to the name of the link:

ALTER SESSION CLOSE DATABASE LINK linkname;

Note that this statement only closes the links that are active in your current session. 

内容版权声明:除非注明,否则皆为本站原创文章。

转载注明出处:https://www.heiqu.com/697456e65e9cca3d452405eea10ee3f4.html