Skip to Main Content
  • Questions

  • Session still Active after exiting client

Breadcrumb

May 4th

Question and Answer

Tom Kyte

Thanks for the question, Srini.

Asked: January 04, 2010 - 11:54 pm UTC

Last updated: March 14, 2013 - 8:27 am UTC

Version: 9.2.0.5

Viewed 10K+ times! This question is

You Asked

Hi Tom,

Wish you a happy new year!

A theoretical problem that is eating up my mind over the past few days. Your advise would be helpful.

A DBA from our project informed me that a session from my PC got connected on day 1 and is running still in ACTIVE status for two weeks. This has resulted in a lock on the dictionary tables owing to which we have got a performance problem. On day 14, the session was killed at the database to get rid of the problem. (as stated by the DBA)

When I checked the client tool status in my PC, (PL SQL developer in this case), it was not running and I cannot find any relevant process as well in the task manager. We connect to the database over a VPN connection, which terminates itself after 24 hours.

In this case,

How can the session remain ACTIVE in Oracle and holding a lock? When there is an abnormal (VPN exit, process killed) exit / normal termination (proper exit), the session should not hold any locks, isn't? Is the DBA trying to cheat me here? The DBA has provided a session ID, name, logon time, SQL Hash value but says he holds no information on the SQL / PL SQL executed by the session, which resulted in a lock. Please throw some light on this.

Thanks as Always.


and Tom said...

It is deceptively easy to do.

TCP/IP doesn't interrupt things by default, by design. When a connection goes away, the client and/or server do not immediately get notified. So, if your client connects to the database and does nothing - and you unplug your client (blue screen it, kill it, pull out the network cable, crash the computer, whatever) the odds are the session will stay in the database. The server will not know that it will never receive a message from you. We have dead client detection for that if it becomes an issue:

http://docs.oracle.com/cd/B12037_01/network.101/b10776/sqlnet.htm#sthref476

As for the active session, that is really easy. You open a connection, you submit a request over this connection like "lock table T". Table t is locked by your transaction in your session. You then submit a block of code like:

begin
loop
dbms_lock.sleep(5);
end loop;
end;
/

your session will be active for as long as that code is running - the client process is blocked on a socket read waiting for the server to send back a result - a response (which of course will never come). The server isn't touching the network at all right now - it is active with your code. So, if your client 'dies' right now - the block of code will continue to run, and run, and run - and since it never commits - it'll just hang in there and run and of course any locks you have will remain in place.


The dba isn't trying to cheat you. If they were capturing statspack reporting information - they should be able to turn that hash into a sql string, the historical sql statements would be captured in the statspack tables.

Rating

  (4 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Active session

Srini Sr, January 05, 2010 - 5:05 pm UTC

Hi Tom,

For an ACTIVE session, isn't an abnormal termination dangerous? (based on your example, when a lock is held)

The value of the parameter EXPIRE_TIME is set to 6 for our database. In this case, it will investigate and kill only INACTIVE sessions, is this correct?

In my case, is there a possible way to detect this lock? (as the session was in ACTIVE status)

Thanks as Always.



Tom Kyte
January 06, 2010 - 7:48 am UTC

define dangerous.

if the client HADN'T gone away, you would be in the same exact situation? Now what???

If you want to limit the amount of time any single session may remain active, use a resource profile (cpu per call, time limits, whatever).

Resource profiles

Srini, January 06, 2010 - 6:02 pm UTC

Hi Tom,

If the client hadn't gone away, then it would be possible to break the operation from the client PC.

In this case, the session is active at the database, and there is no hint of the connection at the client PC.

I will read more on the resource profiles. The problem in our case is, the database is accessed by a single user ID, both by the application (three tier) and users (client tools).

Thanks again.


Tom Kyte
January 11, 2010 - 7:59 am UTC

... and there is no hint of
the connection at the client PC. ...

and we have no idea that is the case, tcp/ip doesn't do it for us.


...

When EXIT from sqlplus, how long it take to clean up?

Anand, March 25, 2010 - 8:12 am UTC

When issuing EXIT command from sqlplus, how much time Oracle will take to clean up the connection?

Need to understand because developer needs to open 3 sessions in one process. And that process will be called 20 /30 times every 30 minutes.

Thank you. Your responses are very helpful.
Anand
Tom Kyte
March 26, 2010 - 1:41 pm UTC

it is immediate, when you exit, sqplus will pretty much a) commit, b) terminate the connection (note in 11gr2 sqlplus might rollback instead of commit, you choose that via a setting in sqlplus)


if a developer is writing an application that uses sqlplus as a "subroutine", that developer has almost certainly done something wrong, I cannot imagine "forking" sqlplus like that as part of my day to day application execution - how truly 'fragile' that would be - pretty much zero ability to handle and deal with errors.

Jaroslav Tachovsky, March 14, 2013 - 4:02 am UTC

Hello Tom,
on you first reply (link to documentation) is recommendattion to set SQLNET.EXPIRE_TIME in [ORACLE_HOME]/network/admin/sqlnet.ora file. My question is - should it be set on client or server side ?

Tom Kyte
March 14, 2013 - 8:27 am UTC

<quote>

This parameter is primarily intended for the database server, which typically handles multiple connections at any one time.

</quote>

server side.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

DBMS_LOCK

More on PL/SQL routine DBMS_LOCK here