Re: RESOURCES ARE NOT FREED WHEN A SESSION DIES ABNORMALLY.

ALTER SYSTEM KILL SESSION will mark the session as killed, but it doesn't always kill the OS process (depending on what the problem was). On Unix and its variants you can remove the process with kill -9, but on Windows the process is actually a thread inside the oracle.exe process. Thus the existance of orakill:

SQL> select p.spid from v$process p, v$session s

     where p.addr = s.paddr
       and s.sid = <session to kill>;

C:\> orakill <ORACLE_SID> <spid>

Note: While it has usually worked for me, even orakill may not be able to take out a seriously hung thread.

On Fri, Aug 1, 2008 at 12:10 PM, Sweetser, Joe <JSweetser_at_icat.com> wrote:

> It's been a while since I've worked on Windows but I think there is an
> Oracle-supplied utility called orakill that can help with this task
> providing the database itself is on Windows.
>
> -joe
>
> ------------------------------
> *From:* oracle-l-bounce_at_freelists.org [mailto:
> oracle-l-bounce_at_freelists.org] *On Behalf Of *Powell, Mark D
> *Sent:* Friday, August 01, 2008 10:58 AM
> *To:* freelist freelist
> *Subject:* RE: RESOURCES ARE NOT FREED WHEN A SESSION DIES ABNORMALLY.
>
> 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_at_freelists.org [mailto:
> oracle-l-bounce_at_freelists.org] *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
>
> Confidentiality Note: This message contains information that may be
> confidential and/or privileged. If you are not the intended recipient, you
> should not use, copy, disclose, distribute or take any action based on this
> message. If you have received this message in error, please advise the
> sender immediately by reply email and delete this message. Although ICAT
> Holdings, LLC, Underwriters at Lloyd's, Syndicate 4242, scans e-mail and
> attachments for viruses, it does not guarantee that either are virus-free
> and accepts no liability for any damage sustained as a result of viruses.
> Thank you.
>

-- 
Jason Heinrich

--
http://www.freelists.org/webpage/oracle-l