Terminating Oracle Sessions at the Operating System Level

Sometimes, “ALTER SYSTEM KILL SESSION” will not terminate a session, it will just mark it as “killed”. In this case you can kill the process or thread of that session at the operation system level.

To kill the session at the operation system level, we need to identify the process ID of the session. Following query will give us the process/thread ID (SPID) of a session we know SID:

To kill the session on the Windows operating system, just use “orakill” utility (comes with Oracle):

To kill the session on the Unix operatin system, you can use “kill” command:

If after a few minutes the process hasn’t stopped, you can force the session to terminate by using “-9” parameter:

While killing a process, make sure that you are not killing background processes of Oracle such as DBWR, LGWR, SMON, PMON. If you kill one of them, Oracle will crash or become unstable.

Please share
  •  
  •  
  •  
  •  
  •  
  •  

Gokhan Atil is a database administrator who has hands-on experience with both RDBMS and noSQL databases, and strong background on software development. He is certified as Oracle Certified Professional (OCP) and is awarded as Oracle ACE (in 2011) and Oracle ACE Director (in 2016) for his continuous contributions to the Oracle users community.

1 Comment

  1. ramkumar sharma

    note able to kill toad service from server
    that is marked for kill but after some automatically start

    SQL> alter system kill session ‘132,11682’;
    alter system kill session ‘132,11682’
    *
    ERROR at line 1:
    ORA-00031: session marked for kill

    SQL> alter system kill session ‘132,11682’ immediate;
    alter system kill session ‘132,11682’ immediate
    *
    ERROR at line 1:
    ORA-00031: session marked for kill

    SQL> SELECT p.spid, s.osuser, s.program FROM v$process p, v$session s WHERE p.addr = s.paddr and s.sid = 107;

    SPID OSUSER
    ———— ——————————
    PROGRAM
    —————————————————————-
    3692 raj\ramkumar.sharma
    Toad.exe

    SQL> cd
    SP2-0042: unknown command “cd” – rest of line ignored.
    SQL> exit
    Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 – Production
    With the Partitioning, OLAP and Data Mining options

    C:\Users\Administrator>orakill orcl 3692

    Kill of thread id 3692 in instance orcl successfully signalled.

    C:\Users\Administrator>orakill orcl 3900

    Kill of thread id 3900 in instance orcl successfully signalled.

    but after some it will auto start
    please reply
    ramkumar sharma

Leave Comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.