RE: RESOURCES ARE NOT FREED WHEN A SESSION DIES ABNORMALLY.
- From: "Powell, Mark D" <mark.powell@xxxxxxx>
- To: "freelist freelist" <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 1 Aug 2008 12:57:49 -0400
Closing the Windows screen did not terminate the Oracle session. The
fact you can see the session information in your queries after the
Windows application was closed is proof of this fact. The session is
still there and is probably waiting on a command from the client. You
will need to terminate the Oracle session background process via Oracle
using an ALTER SYSTEM KILL SESSION 'sid,serial#' command. It is not
difficult to write a script that looks for orphaned sessions and
terminates them.
-- Mark D Powell --
Phone (313) 592-5148
________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Asif Momen
Sent: Friday, August 01, 2008 12:31 PM
To: freelist freelist
Subject: RESOURCES ARE NOT FREED WHEN A SESSION DIES ABNORMALLY.
Hi all,
Here is the test case:
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Prod
PL/SQL Release 10.2.0.4.0 - Production
CORE 10.2.0.4.0 Production
TNS for 32-bit Windows: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production
Elapsed: 00:00:00.09
SQL> create table t(acc number, amt number);
Table created.
SQL>
SQL> insert into t values (123, 1000);
1 row created.
SQL>
SQL> commit;
Commit complete.
Session 1:
========
SQL> select sys_context('USERENV','SID') from
dual;
SYS_CONTEXT('USERENV','SID')
--------------------------------------------------------
28
SQL> select * from t where acc = 123 for update;
ACC AMT
---------- ----------
123 1000
SQL>
Session 2:
========
SQL> conn test/test
Connected.
SQL> select sys_context('USERENV','SID') from
dual;
SYS_CONTEXT('USERENV','SID')
-------------------------------------------------------------
30
SQL> select * from t where acc = 123 for update;
Session 2 hangs (wait for session 1 to either commit or rollback)
Session 3:
==========
SQL> select sid, username, event, status, last_call_et,
2 blocking_session, wait_time, seconds_in_wait, state
3 from v$session
4 where sid in (28, 30);
SID USERNAME EVENT STATUS LAST_CALL_ET BLOCKING_SESSION
WAIT_TIME SECONDS_I
---------- ------------------------------ ------------------------------
-------- ------------ -----
28 TEST SQL*Net message from client INACTIVE 108
0
30 TEST enq: TX - row lock contention ACTIVE 33 28 0
Elapsed: 00:00:00.01
If "Session 1" is abnormally terminated then "Session 2" keeps waiting
forever and following are the wait events:
At this point, abnormally terminate "Session 1" by closing the SQL*Plus
window.
SQL> /
SID USERNAME EVENT STATUS LAST_CALL_ET BLOCKING_SESSION WAIT_TIME
---------- ------------------------------ ------------------------------
--------
------------ -----
28 TEST SQL*Net message from client INACTIVE 147 0
30 TEST enq: TX - row lock contention ACTIVE
72 28 0
Elapsed: 00:00:00.01
SQL> /
SID USERNAME EVENT STATUS
LAST_CALL_ET BLOCKING_SESSION WAIT_TIME
---------- ------------------------------ ------------------------------
--------
------------ -----
28 TEST SQL*Net message from client INACTIVE 168 0
30 TEST enq: TX - row lock contention ACTIVE
93 28 0
SQL> /
SID USERNAME EVENT STATUS LAST_CALL_ET
BLOCKING_SESSION WAIT_TIME
---------- ------------------------------ ------------------------------
-------- ------------ -----
28 TEST SQL*Net message from client INACTIVE 258
0
30 TEST enq: TX - row lock contention ACTIVE 183 28 0
Elapsed: 00:00:00.01
Notice that the last_call_et keeps on ticking and session 2 is still
waiting to acquire
lock.
At this time, shouldn't PMON wake up, clean, and releases all the
resources occupied by "Session 1".
I tried toying with SQLNET.EXPIRE_TIME parameter on both database server
and the client-side:
with "sqlnet.expire_time=2"
SID_SER_USER PROGRAM EVENT STATUS LAST_CALL_ET
----------------------- -------------------------
------------------------------ -------- ----------
30 - 2362 - TEST sqlplusw.exe enq: TX - row lock contention
ACTIVE 246
28 - 1624 - TEST sqlplusw.exe SQL*Net message from client INACTIVE 252
you may notice that it has already crossed 4 minutes of idle time.
I have also tried the following:
Subject: Orphaned Processes when DCD is enabled on Windows
Doc ID: Note:462252.1 Type: PROBLEM
Last Revision Date: 21-APR-2008 Status: MODERATED
Yet, no success.
Any help in this regard would be appreciated.
Regards
Other related posts:
