Oracle DBA – A lifelong learning experience

Killing sessions across multiple instances

Posted by John Hallas on March 23, 2016

I am sure every DBA has used a kill session script before, although a lot probably use OEM as much now as individual scripts.

However I had not realised there was an option to add a 3rd parameter, the instance _id to the command

For example to kill any DBSNMP connections across all nodes of a RAC database

select 'alter system kill session ''' || sid || ', ' || serial# || ', @' || inst_id || ''';'

from gv$session where username = DBSNMP' order by logon_time desc;

alter system kill session ‘975, 57707, @2’;
alter system kill session ‘584, 2515, @1’;
alter system kill session ‘589, 4399, @2’;
alter system kill session ‘401, 2081, @1’;
alter system kill session ‘1168, 36153, @2’;
alter system kill session ‘1350, 21961, @1’;

This parameter is only available on RAC databases – which I suppose is quite obvious really.

There is also an option to use the IMMEDIATE keyword as well, this returns control straight back to the user without waiting for the command to complete, which can be quite useful when you have a few iterations of the command to run

alter system kill session '1350, 21961, @1' immediate;



4 Responses to “Killing sessions across multiple instances”

  1. fouedgray said

    Thanks for the share, I even discovered today there was another way to kill sessions :

    • John Hallas said

      Yes, I saw that blog and I am amazed that we have 2 almost identical posts in one day.
      Mine was instigated by an issue we had on an Exadata server where we had lots of blocking sessions and the OEM metrics were not getting collceted and we realised that we had many sessions onwed by DBSNP. We did run that script that I posted but in the end we stopped the cluster.
      I posted mine about 01:30 in the morning during a call-out I had.

  2. Arjun Kanna said

    my two cents. There is another parameter POST_TRANSACTION.

    If I execute command, “ALTER SYSTEM KILL SESSION ‘xxx,xxxx’ IMMEDIATE;”
    Specify IMMEDIATE to instruct Oracle Database to roll back ongoing transactions, release all session locks, recover the entire session state, and return control to you immediately.You wrote end immediately with no rollback of updates?.Its not so, it will rollback all uncommited updates.

    If I execute command “ALTER SYSTEM KILL SESSION ‘xxx,xxxx’ POST_TRANSACTION;”
    The POST_TRANSACTION setting allows ongoing transactions to complete before the session is disconnected. If the session has no ongoing transactions, then this clause has the same effect described for as KILL SESSION.

    • John Hallas said

      I didnt actually say there was no rollback of updates with KILL IMMEDIATE. In fact I didnt think it needed saying that performing a kill would cause uncommitted changes to rollback.
      However good spot re the POST_TRANSACTION option. To be honest in my experience if you are killing a session you are unlikley to want it to commit any data
      Thanks for commenting

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: