Home » RDBMS Server » Server Administration » DBLinks Inactive Sessions
DBLinks Inactive Sessions [message #407193] Tue, 09 June 2009 00:58 Go to next message
sk2304
Messages: 5
Registered: June 2009
Junior Member
Hi,
An oracle procedure in remote system is trying to connect to our database using DBlink and leaving lot many Inactive sessions behind, which is causing problem for us as the number of connections we provide is limited. Once it crosses the limit we need to bounce our database to clear off all the inactive sessions. Can anyone please suggest how can this be resolved? Any idea what could be the root cause behind this?
Re: DBLinks Inactive Sessions [message #407195 is a reply to message #407193] Tue, 09 June 2009 01:11 Go to previous messageGo to next message
Michel Cadot
Messages: 68722
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
alter session close database link ...

When you no more use a db link, close it.

Regards
Michel
Re: DBLinks Inactive Sessions [message #407213 is a reply to message #407195] Tue, 09 June 2009 02:26 Go to previous messageGo to next message
sk2304
Messages: 5
Registered: June 2009
Junior Member
Thanks for your input Michel.

Remote system has already implemented this, however still facing the problem. Could there be any other reason behind this?
Re: DBLinks Inactive Sessions [message #407216 is a reply to message #407213] Tue, 09 June 2009 02:31 Go to previous messageGo to next message
Michel Cadot
Messages: 68722
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
This is not the remote db that closes the session but the source one.

Regards
Michel
Re: DBLinks Inactive Sessions [message #407218 is a reply to message #407193] Tue, 09 June 2009 02:44 Go to previous messageGo to next message
sk2304
Messages: 5
Registered: June 2009
Junior Member
Actually remote system is accessing our database through DBlink. So in this case they are closing the sessions using alter session close dblink. The remote system is the source here and they are closing sessions.
Re: DBLinks Inactive Sessions [message #407224 is a reply to message #407218] Tue, 09 June 2009 03:32 Go to previous messageGo to next message
Michel Cadot
Messages: 68722
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Then add a profile with an idle time limit to the account target of the db link.

Regards
Michel
Re: DBLinks Inactive Sessions [message #407309 is a reply to message #407224] Tue, 09 June 2009 09:49 Go to previous messageGo to next message
sk2304
Messages: 5
Registered: June 2009
Junior Member
Hi Michel,
Thanks for your suggestion.

I am not gud in DB can you please help me in clearing some doubts on profile creation.
If we create a profile
create profile only30minutes_of_inactivity
limit idle_time 30;
alter user <user_name> profile only30minutes_of_inactivity;

Will it clear off the sessions. I was under impression, sessions will terminate using this setting but will not get removed from the database they drop to having a status of "sniped" and they would still count against the maximum session limit.
Re: DBLinks Inactive Sessions [message #407343 is a reply to message #407309] Tue, 09 June 2009 16:36 Go to previous messageGo to next message
babuknb
Messages: 1736
Registered: December 2005
Location: NJ
Senior Member


Once Reach 30 Min. You want disconnect that session?

Thanks
Re: DBLinks Inactive Sessions [message #407370 is a reply to message #407343] Tue, 09 June 2009 23:43 Go to previous messageGo to next message
sk2304
Messages: 5
Registered: June 2009
Junior Member
Hi,
Yes, I want that to be removed from v$session table.

Thanks.
Re: DBLinks Inactive Sessions [message #407400 is a reply to message #407370] Wed, 10 June 2009 01:44 Go to previous message
babuknb
Messages: 1736
Registered: December 2005
Location: NJ
Senior Member

As per Mr Mic; IDLE TIME should be work.. Did you check oracle document ??
Quote:


You can limit the idle time for a session. If the time between Oracle calls for a session reaches the idle time limit, the current transaction is rolled back, the session is aborted, and the resources of the session are returned to the system. The next call receives an error that indicates the user is no longer connected to the instance. This limit is set as a number of elapsed minutes.


Ref: http://stanford.edu/dept/itss/docs/oracle/10g/network.101/b10773/authoriz.htm#1009024

http://stanford.edu/dept/itss/docs/oracle/10g/server.101/b10739/dbrm.htm#ADMIN027

Thanks
Previous Topic: How can change SYS password and back if we have Windows Admin account
Next Topic: ORA-12714
Goto Forum:
  


Current Time: Tue Dec 10 03:03:12 CST 2024