oracle dblink造成遠端資料庫session過多，oracledblink

oracle dblink造成遠端資料庫session過多，oracledblink

   現場報網公司資料庫連不上，先檢查了下資料庫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 linkTO_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 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.