SQL QUERY TO FIND THE NON SYS OBJECTS IN SYSTEM TABLESPACE.‘Block Cleanout Optim, Undo Segment Scan’ viol=1 err=12751 'ALTERSYSTEMKILLSESSION'||SID||','||SERIAL#||'IMMEDIATE 'ĪLTER SYSTEM KILL SESSION 1,15 IMMEDIATE ĪLTER SYSTEM KILL SESSION 10,1 IMMEDIATE ĪLTER SYSTEM KILL SESSION 11,1 IMMEDIATE ĪLTER SYSTEM KILL SESSION 12,1 IMMEDIATE ĪLTER SYSTEM KILL SESSION 13,1 IMMEDIATE ĪLTER SYSTEM KILL SESSION 14,1 IMMEDIATE ĪLTER SYSTEM KILL SESSION 16,1 IMMEDIATE ĪLTER SYSTEM KILL SESSION 17,1 IMMEDIATE ĪLTER SYSTEM KILL SESSION 18,1 IMMEDIATE ĪLTER SYSTEM KILL SESSION 19,1 IMMEDIATE ĪLTER SYSTEM KILL SESSION 20,1 IMMEDIATE ĪLTER SYSTEM KILL SESSION 21,1 IMMEDIATE ĪLTER SYSTEM KILL SESSION 22,1 IMMEDIATE ĪLTER SYSTEM KILL SESSION 23,1 IMMEDIATE ĪLTER SYSTEM KILL SESSION 24,1 IMMEDIATE ĪLTER SYSTEM KILL SESSION 25,1 IMMEDIATE ĪLTER SYSTEM KILL SESSION 26,1 IMMEDIATE ĪLTER SYSTEM KILL SESSION 27,1 IMMEDIATE ĪLTER SYSTEM KILL SESSION 28,1 IMMEDIATE ĪLTER SYSTEM KILL SESSION 30,643 IMMEDIATE ĪLTER SYSTEM KILL SESSION 31,1 IMMEDIATE ĪLTER SYSTEM KILL SESSION 32,1 IMMEDIATE ĪLTER SYSTEM KILL SESSION 34,15 IMMEDIATE ĪLTER SYSTEM KILL SESSION 36,9 IMMEDIATE ĪLTER SYSTEM KILL SESSION 37,1 IMMEDIATE ĪLTER SYSTEM KILL SESSION 39,1 IMMEDIATE ĪLTER SYSTEM KILL SESSION 40,1 IMMEDIATE ĪLTER SYSTEM KILL SESSION 41,1 IMMEDIATE ĪLTER SYSTEM KILL SESSION 46,1 IMMEDIATE ĪLTER SYSTEM KILL SESSION 47,1 IMMEDIATE ĪLTER SYSTEM KILL SESSION 48,5 IMMEDIATE ĪLTER SYSTEM KILL SESSION 51,1 IMMEDIATE ĪLTER SYSTEM KILL SESSION 56,5 IMMEDIATE ĪLTER SYSTEM KILL SESSION 58,125 IMMEDIATE ĪLTER SYSTEM KILL SESSION 59,9 IMMEDIATE ĪLTER SYSTEM KILL SESSION 62,491 IMMEDIATE Ĥ4 rows this help you in any urgent situation! SAMPLE 'ALTER SYSTEM KILL SESSION '||sid||','||serial#||' IMMEDIATE ' FROM v$session SELECT 'ALTER SYSTEM KILL SESSION '||sid||','||serial#||' IMMEDIATE ' FROM v$session WHERE CLIENT_INFO LIKE '% rman% SQL script to kill all the sessions which has blockings SELECT 'ALTER SYSTEM KILL SESSION '||sid||','||serial#||' IMMEDIATE ' FROM v$session WHERE BLOCKING_SESSION is NOT NULL
SQL script to kill all the sessions which run sql queries SELECT 'ALTER SYSTEM KILL SESSION '||sid||','||serial#||' IMMEDIATE ' FROM v$session WHERE SQL_ID is NOT NULL SQL script to kill all the sessions which run with user TEST and which are INACTIVE and run for more than 10000 seconds and logged on users from last 24 hours SELECT 'ALTER SYSTEM KILL SESSION '||sid||','||serial#||' IMMEDIATE ' FROM v$session WHERE USERNAME LIKE '% TEST%' AND STATUS=' INACTIVE' and LAST_CALL_ET > 10000 and LOGON_TIME > sysdate - 1 You can modify the script according to your scenario SELECT 'ALTER SYSTEM KILL SESSION '||sid||','||serial#||' IMMEDIATE ' FROM v$session WHERE USERNAME LIKE '% TEST%' AND STATUS=' INACTIVE' and LAST_CALL_ET > 10000 SQL script to kill all the sessions which run with user TEST and which are INACTIVE and run for more than 10000 seconds. SQL script to kill all the sessions which run with user TEST and which are INACTIVE SELECT 'ALTER SYSTEM KILL SESSION '||sid||','||serial#||' IMMEDIATE ' FROM v$session WHERE USERNAME LIKE '% TEST%' AND STATUS=' INACTIVE' SQL script to kill all the sessions run with username called TEST SELECT 'ALTER SYSTEM KILL SESSION '||sid||','||serial#||' IMMEDIATE ' FROM v$session WHERE USERNAME LIKE '% TEST%' SQL script to kill all the sessions from the database - Note: Use this script with caution as this will kill all the sessions from the database SELECT 'ALTER SYSTEM KILL SESSION '||sid||','||serial#||' IMMEDIATE ' FROM v$session You can use the below scripts to quickly act based on your scenario You are in situation to quickly kill multiple sessions which consume high resource on database which cause performance issues.