How do I kill multiple sessions?

How to kill multiple sessions simultaneously in Oracle Database?

Query to generate SQL statements when inactive/active sessions are very high and we need to kill lot of them

When any schema inside Oracle Database is having multiple sessions and most of them are inactive then you can use the below mentioned query to generate session kill statements for you for that particular schema with the SID and SERIAL details. Check this,

SQL> select 'alter system kill session ''' || sid || ',' || serial# || ''' immediate;' from V$session where username = 'SCOTT' and last_call_et> 60 and status = 'ACTIVE';

'ALTERSYSTEMKILLSESSION'''||SID||','||SERIAL#||'''IMMEDIATE;' 
------------------------------------------------------------------------------------------------------------------------
alter system kill session '20,19313' immediate;                                                                                     
alter system kill session '25,57183' immediate;
alter system kill session '58,36475' immediate;

Skip to content

In my last blog I talked about how to kill a session in SQL Server. In this blog I will talk about how to kill multiple sessions in SQL Server.

The blog just explains a way to kill multiple sessions as and when required it doesn’t promotes it as a best practice. Don’t run this on production server unless you know what you are doing.

The below query generates the kill statements on the fly for the multiple session which are to be killed.

-- How to kill multiple sessions
SELECT 'KILL ' + CAST(session_id as varchar(100)) AS Sessions_to_kill
FROM sys.dm_exec_requests where session_id in (54,57,58)
GO

Now, we have list of sessions we want to kill as shown below.

 

 

Another way is to execute the KILL query as a dynamic query instead of getting the list. However, it’s better to check the sessions again before killing them.

The next step is to copy and paste the list in a new query window and execute the KILL command.

And remember; don’t try this on production server 🙂

 
Like us on FaceBook  |  Join the fastest growing SQL Server group on FaceBook

Post navigation

Read these next...

• 

  Internet Cafe Software

  Software

  Greetings All,Weare setting up a sction of a Community Center as an internet cafe. I have a feeling that we are not going to find 1 software to deal with both aspects of the project but I know this is the community that would have the right solution(s).We...

• 

  Nerd Journey # 199 - Confessions of an Atta Boy Junkie with Nathan Bennett (2/2)

  Best Practices & General IT

  If you're someone who has suffered from impostor syndrome, this might be the episode for you.  We continue Nathan Bennett's story with more detail on how he got addicted to external validation (or became an "atta boy junkie" as I like to say).  We also di...

• 

  Snap! -- Space Power, Intelligent Toilet, Transparent Solar Panels, Mouse Fraud

  Spiceworks Originals

  Your daily dose of tech news, in brief. Welcome to the Snap! Flashback: Back on December 23, 1947, Bardeen and Brattain Demonstrate the Transistor (Read more HERE.) Bonus Flashback: Back on December 23, 1968, First Men to Orbit the Moon (...

• 

  Hardware Recommendations For Virtual Server Setup

  Virtualization

  Hi All,Looking for hardware advice and recommendations please for a virtual server setup.We have 16 users who will need to access a Sage 200 database running on SQL Server Standard 2019. Of these 16 users, only 10 will need to access basic file shares...

• 

  A way to promote Windows 11?

  Windows

  This affects even the new version of Windows 10. One way to sway users to Windows 11? :)https://www.bleepingcomputer.com/news/microsoft/microsoft-kb5021233-causes-blue-screens-with-0xc0000...

How do I kill multiple sessions in SQL?

How do I kill all sessions in SQL Developer?

How to give kill session privileges in Oracle?

How do you kill a session on a website?