How to identify active sessions in Oracle and kill them if required

Kill Active Sessions

Killing active sessions in Oracle can be necessary for various reasons, such as terminating runaway processes, resolving locking issues, or freeing up resources. This process should be handled carefully to avoid unintended consequences. Here are the steps and considerations for safely terminating active sessions.

Steps to Kill Active Sessions

1. Identify the Session: First, you need to identify the session you want to terminate.
2. Kill the Session: Use the ALTER SYSTEM KILL SESSION command to terminate the session.

Identify the Session

You can use the V$SESSION view to find the session you want to kill. Here’s an example query to identify active sessions:

Kill the Session

Once you have identified the SID and SERIAL# of the session you want to terminate, you can use the ALTER SYSTEM KILL SESSION command:

ALTER SYSTEM KILL SESSION 'sid,serial#';

Forcefully Terminating a Session

If the session does not terminate immediately, you can forcefully kill it using the IMMEDIATE clause:

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

The following script generates the script for killing sessions in oracle as well as OS (Linux/Windows etc)

SELECT 'alter system kill session ''' || A.SID || ',' || A.SERIAL# || ''' immediate;' oracle_kill_command, 'kill -9 ' || b.spid os_kill_command, SUBSTR (A.MACHINE, 1, 25) machine_name, B.SPID os_pid, A.LOGON_TIME FROM V$SESSION A, V$PROCESS B WHERE TRUNC (A.LOGON_TIME) <= TRUNC (SYSDATE) AND A.PADDR = B.ADDR AND a.status = 'ACTIVE' AND (A.USERNAME IN ('&USRNAME') AND A.USERNAME IS NOT NULL) ORDER BY SUBSTR (A.MACHINE, 1, 25), a.logon_time DESC;

Here

<username> -- is the particular user sessions of your database. You need to replace this with.

Here is the OUTPUT

You can directly execute ORACLE_KILL_COMMAND data shown in the screen shot above.

and to kill the corresponding OS process ids use the OS_KILL_COMMAND data shown in the screen shot above

Post-Kill Steps

After killing a session, it's a good practice to check if the session has been terminated properly and to verify if there are any residual effects:

1.Check for Remaining Session:

SELECT 

    SID, 

    SERIAL#, 

    USERNAME, 

    STATUS 

FROM 

    V$SESSION 

WHERE 

    SID = <sid>;

2. Monitor for Locks: Ensure there are no locks held by the killed session.

SELECT SESSION_ID, LOCK_TYPE, MODE_HELD FROM DBA_LOCKS WHERE SESSION_ID = <sid>;

if DBA_LOCKS view is not there click here

Considerations

• Impact on Transactions: Killing a session can cause uncommitted transactions to roll back, which might impact other sessions or applications.
• Privileges: You need the appropriate privileges (ALTER SYSTEM) to kill sessions.
• Caution with System Sessions: Be cautious when killing system or background sessions, as this might impact the stability of the Oracle instance.

Using Oracle Enterprise Manager (OEM) to Kill Sessions

1. Log in to Oracle Enterprise Manager.
2. Navigate to the Performance Page: Select the database instance you want to monitor.
3. Active Sessions: Go to the “Sessions” tab to see a list of active sessions.
4. Select the Session: Identify the session you want to kill.
5. Kill the Session: Use the provided interface option to kill the session.