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个
比照检测:
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 DatabaseClosing 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 linkn
相关文章
- 3条评论
- 酒奴棕眸2022-06-01 04:14:37
- ion数最多是哪家machine进行的,发觉是省企业的数据库查询进行的session,找开发设计整理了下业务流程,省企业同歩dblink实际操作网企业表,且是根据weblogic的连接
- 囤梦做啡2022-06-01 11:44:17
- et id=1;commit;alter session close database link TO_B;3.在数据库查询B上select count(1) from v$session; 可以看沒有转变小结: 出現这类难题,说到底是实际操作dblink不标准,用了以后沒有关掉。附
- 丑味歆笙2022-06-01 12:25:52
- 中的session数最多是哪家machine进行的,发觉是省企业的数据库查询进行的session,找开发设计整理了下业务流程,省企业同歩dblink实际操作网企业表,且是根