How To Find Particular Database Session ID From the SQLID ( ORACLE 19c)

ISSUE:

In this Blog we are going to learn about How To Find a Particular Database Session ID From the SQLID.
One of my customer informed me I have query running since 10 hours and He
already confirmed with Business user that this query session is safe to be
terminated.

Here we will not discuss about invetigating if that is really applicable to
be terminated

Instead this is a just to share the method on:

GOAL: How to find particular Database session ID from the SQLID we could
see from Oracle 19c Enterprise Manager Database Express console

Note that we could not see the session id from same console.

SOLUTION:

1. select * from V$SESSION where SQL_ID = ‘7upkztdrajyay’

better to see the detailed row data returned from above select statement.

then check the sid in below sql which will generate kill statement to be run
from database superuser account ( sysdba )

2. select ‘alter system kill session ‘||””||
   vs.sid||’,’||vs.serial#||””|| ‘ immediate;’ from v$session vs, v$process
   vp where vs.paddr = vp.addr and vs.sid in ();

OR merge both queries for one shot result

select ‘alter system kill session ‘||”|| vs.sid ||’,’ || vs.serial# || ” || ‘ immediate;’ from v$session vs, v$process vp where vs.paddr=vp.addr and vs.sid in (select sid from v$session where sql_id =’7upkztdrajyay’);

sqlplus / as sysdba

SQL> ALTER SYSTEM KILL SESSION ‘275,44741’ immediate;
SQL> ALTER SYSTEM KILL SESSION ‘279,44749’ immediate;

Done and EM does not show it anymore.