🧩 How to Check Long-Running Sessions in Oracle Database

🧩 Introduction

Long-running sessions can cause performance issues and even block other users. As a DBA, it’s important to identify and manage these sessions.

Step 1: Find Active Long-Running Sessions

SELECT sid, serial#, username, status, osuser, machine, program, sql_id

FROM v$session

WHERE status='ACTIVE'

AND username IS NOT NULL

ORDER BY logon_time;

➡️ Lists active sessions with details such as user, machine, and SQL ID.

Step 2: Identify Long SQL Queries

SELECT a.sid, a.serial#, a.username, b.sql_text

FROM v$session a

JOIN v$sql b ON a.sql_id = b.sql_id

WHERE a.status='ACTIVE'

AND a.username IS NOT NULL;

➡️ Shows the actual SQL being executed by the long-running session.

Step 3: Kill a Problematic Session

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

➡️ Replace sid,serial# with the values from Step 1.

Conclusion

Monitoring and managing long-running sessions is key to keeping your Oracle database responsive and stable.