Kill long-running query in Oracle Database 19c

To kill the long-running session, we must verify it before killing it, below are the steps to verify and kill.

1. Query V$SESSION supplying the username in CAPS for the session you want to terminate:

SELECT SID, SERIAL#, STATUS, SERVER
FROM V$SESSION
WHERE USERNAME ='USERNAME';

2. Verify the SQL query which you want to terminate

SELECT SES.SID, SES.SERIAL# SER#, SES.PROCESS OS_ID, SES.STATUS, SQL.SQL_FULLTEXT
FROM V$SESSION SES, V$SQL SQL, V$PROCESS PRC
WHERE
   SES.SQL_ID=SQL.SQL_ID AND
   SES.SQL_HASH_VALUE=SQL.HASH_VALUE AND 
   SES.PADDR=PRC.ADDR AND
   SES.SID=&Enter_SID;  

3. Use the below query to kill the session once you're sure about the SID and SERIAL and have received consent from the application team.

ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;

"The IMMEDIATE clause does not affect the work performed by the command, but it returns control back to the current session immediately, rather than waiting for confirmation of the kill. "

You may also read further by clicking on the link mentioned below:

www.support.dbagenesis.com

Sometimes you might need to kill a session inside the Oracle database. Before you kill, make sure you have permission from the application team to proceed. Syntax Below is the syntax to kill a session inside Oracle database ALTER SYSTEM KILL SESSION 'SID,Serial#'; Example ALTER SYSTEM KILL SESSION '38,6453';