Sessions & Locks
How to generate KILL SESSION and OS kill commands for a session in Oracle
For a given Oracle SID, generate the ALTER SYSTEM KILL SESSION, the ps command to find the process, and the OS-level kill -9 command.
How to get a human-readable summary of blocking sessions in Oracle
Print a one-line "X is blocking Y" summary for every Oracle blocking session across the RAC, joined from GV$LOCK and GV$SESSION.
How to find an Oracle session from an OS PID
Given an OS process id (SPID), find the matching Oracle session — with an optional query that also returns the SQL being executed.
How to check session count per user in Oracle
Count how many sessions each Oracle user has from V$SESSION grouped by username.
How to generate KILL SESSION statements in Oracle
Generate ALTER SYSTEM KILL SESSION statements for Oracle sessions matching common filters: by SQL_ID, by username, INACTIVE, SNIPED, or blocking chains.
How to analyze lock holders and waiters across RAC in Oracle
Show lock holders and waiters across all Oracle RAC instances with lock mode, requested mode, SQL_ID, and seconds in wait from GV$LOCK and GV$SESSION.
How to check session details and SQL text for a given SID in Oracle
Show full session context (OS PID, machine, wait state, SQL_ID) and the SQL text a given Oracle SID is running.
How to disconnect a session in Oracle
Disconnect an Oracle session cleanly with ALTER SYSTEM DISCONNECT SESSION POST_TRANSACTION or IMMEDIATE.
How to kill a session in Oracle
Kill a specific Oracle session with ALTER SYSTEM KILL SESSION IMMEDIATE and verify it is gone.
How to check top wait events for a session in Oracle
Rank cumulative wait events for a given Oracle session from V$SESSION_EVENT to see where its time went.
How to check idle sessions over a threshold in Oracle
List Oracle sessions idle for more than N seconds so you can decide whether to disconnect them.
How to check open cursors per session in Oracle
Count open cursors per Oracle session and compare against OPEN_CURSORS to catch leaks before ORA-01000.
How to check what SQL a session is running in Oracle
Show the current and previous SQL for a given Oracle session with the full SQL text from V$SQL.
How to check DDL and DML locks in Oracle
List Oracle DDL locks (parse and library) and DML locks (TM enqueues) currently held.
How to check row lock contention in Oracle
Show Oracle sessions waiting on enq: TX - row lock contention and the object, waiter, and blocker involved.
How to check locked objects in Oracle
List Oracle objects currently locked with the holding session, lock mode, and object type.
How to check blocking and blocked sessions in Oracle
Find which Oracle sessions are blocking others, what they are waiting on, and the SQL involved.
How to find the root blocker in a lock chain in Oracle
Walk an Oracle lock chain to the ultimate blocker at the head using FINAL_BLOCKING_SESSION.
How to check active sessions in Oracle
List active Oracle user sessions with username, machine, program, and the SQL they are running.