Managing active connections in Oracle is crucial for maintaining database performance and ensuring smooth operations. Sometimes, you may need to terminate a specific session, especially if it’s causing performance issues or conflicts. Here’s how to identify and kill a specific connection in an Oracle database.

Step 1: Identify Active Sessions

To find active sessions and determine which one to terminate, use the following query:

SELECT 
    s.sid,
    s.serial#,
    s.username,
    s.machine,
    s.program,
    q.sql_text
FROM 
    v$session s
LEFT JOIN 
    v$sql q
ON 
    s.sql_id = q.sql_id
WHERE 
    s.type = 'USER';

Explanation:

• SID: Session ID.
• SERIAL#: Unique session identifier.
• USERNAME: Database user.
• MACHINE: Host machine of the user.
• PROGRAM: Application program initiating the session.
• SQL_TEXT: The query is executed by the session.

This query provides detailed information about active user sessions, including their running queries.

Step 2: Kill a Specific Session

Once you’ve identified the SID and SERIAL# of the session to terminate, use the following command to kill it:

Kill a session:

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

Example:

To kill a session with SID=26 and SERIAL#=6764, execute:

ALTER SYSTEM KILL SESSION '26,6764' IMMEDIATE;

Force Disconnect:

If the above command doesn’t terminate the session due to lingering processes, forcefully disconnect it:

ALTER SYSTEM DISCONNECT SESSION 'sid,serial#' IMMEDIATE POST_TRANSACTION;

Step 3: Verify Termination

To confirm the session is terminated, rerun the session query:

SELECT sid, serial#, username 
FROM v$session 
WHERE username IS NOT NULL;

The terminated session should no longer appear in the results.

Common Scenarios for Terminating Sessions

1. Idle Sessions:
   Sessions left open without activity but still consuming resources.
2. Blocking Sessions:
   A session holding locks and preventing other transactions from proceeding.
3. Unauthorized Access:
   Detect and terminate unauthorized connections to maintain security.

Precautions

1. Identify Dependencies:
   Ensure the session isn’t running critical or long-running transactions before terminating.
2. Communicate with Users:
   Notify users if their sessions are about to be disconnected.
3. Use with Caution:
   Killing sessions abruptly may result in data inconsistencies or application errors.

Conclusion

By using the ALTER SYSTEM KILL SESSION command, you can safely terminate specific user connections in the Oracle Database. This helps maintain database performance, resolves conflicts and ensures smooth operations.

Would you like further guidance on Oracle session management or troubleshooting? 😊

Ahmad Gohar

With over 18 years of experience in software architecture, Java technologies, and leadership, I specialize in crafting scalable, future-proof solutions for global organizations. Whether it’s transforming legacy systems, building cutting-edge cloud-native applications, or mentoring teams to excel, I’m committed to delivering value-driven results.

See Full Bio