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

AWS Big Data Specialist. Oracle Certified Professional (OCP) for EBS R12, Oracle 10g and 11g. Co-author of "Expert Oracle Enterprise Manager 12c" book published by Apress. Awarded as Oracle ACE (in 2011) and Oracle ACE Director (in 2016) for the continuous contributions to the Oracle users community. Founding member, and vice president of Turkish Oracle User Group (TROUG). Presented at various international conferences including Oracle Open World.

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;

    ———— ——————————
    3692 raj\ramkumar.sharma

    SQL> cd
    SP2-0042: unknown command “cd” – rest of line ignored.
    SQL> exit
    Disconnected from Oracle Database 10g Enterprise Edition Release – 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.