db link hang的解決方法

現象：在資料庫A 通過DB_LINK  對B資料庫操作，長時間不傳回，hang死在裡面

解決方法：在資料庫B上的sqlnet.ora檔案裡增加參數：

SQLNET.EXPIRE_TIME=10

10代表作0分鐘

擴充：

當設定了resource_limit=true 。通過idle_time限制session idle 時間。session idle超過設定時間，狀态為sniped (v$session).,然而OS下的process并不會釋放，當session(user process) 再次與server process 通訊，将關閉相應的server process.

sqlnet.expire_time 的原理不一樣，Oracle Server 發送包探測dead connection ，如果連接配接關閉，或者不再用，則關閉相應的server process.

以上兩者組合使用，減少server process,防止process超過init$ORACLE_SID極限值。

DCD: Dead Connection Detection ，可以用于檢測、标記僵死而沒有斷開會session，再由PMON進行清理，釋放資源。

開啟DCD，隻需要在服務端的sqlnet.ora檔案中添加SQLNET.EXPIRE_TIME參數，機關為分鐘：

E文原文：

sqlnet.expire_time actually works on a different principle and is used to detect dead connectionsas opposed to disconnecting(actually 'sniping') a session based on idle_time which the profile accomplishes.

Sqlnet.expire_time basically instructs the Server to send a probe packet every set minutes to the client , and if it finds a terminated connection or a connection that is no longer in use, causes the associated server process to terminate on the server.

A valid database connection that is idle will respond to the probe packet causing no action on the part of the Server , whereas the resource_limit will snipe the session when idle_time is exceeded.The 'sniped' session will get disconnected when the user(or the user process) tries to communicate with the server again.

But again,as you mentioned, expire_time works globally while idle_time profile works for that user. You can use both of them to make sure that the client not only gets sniped but also gets disconnected if the user process abnormally terminates.