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. 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
SoftwareGreetings 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 ITIf 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 OriginalsYour 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
VirtualizationHi 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?
WindowsThis affects even the new version of Windows 10. One way to sway users to Windows 11? :)//www.bleepingcomputer.com/news/microsoft/microsoft-kb5021233-causes-blue-screens-with-0xc0000...