On Windows for each instance all oracle sessions are thread based and are part of one Oracle process (oracle.exe), so killing oracle.exe would crash your entire instance. Therefore using the utility orakill.exe one can kill user’s session. Using the following SQL one can find the thread id of the oracle process.
SQL> SELECT a.username, a.osuser, b.spid ThreadID, a.sid, a.serial# FROM v$session a, v$process b WHERE a.paddr = b.addr AND a.username IS NOT NULL;
USERNAME OSUSER ThreadID PROGRAM
————— —————————— ——— ——————————
SYS oracle 2990 rman (TNS V1-V3)
SCOTT mary 1384
So using orakill kill session mary’s session.
c:> orakill TESTDB 1384
Currently I’m working on some external system that uses Oracle 10g as its DBMS. When user logged in to this system and their process went too long they just close the application. But it turns out that sometimes the Oracle session just stays there.
When I checked in V$Session, these session’s status is inactive. I believe these “hang” session would consume resources, so I decided to kill these sessions.
But some other session which actually still “alive” also has the same status.
Is there a way to tell the difference between the “alive” session from the ones that already closed?
I have seen cases when the session status in inactive but query is still running and user is still connected. May be try the following suggestion by http://www.dba-oracle.com/t_connect_time_idle_expire_timeout.htm setting sqlnet.expire_time it may answer what you are looking for.