🔒 Sessions & Locks
Find blockers, inspect sessions and kill runaways with v$session.
| Snippet | |
|---|---|
| ☆Active Sessions — User sessions doing work right now |
SELECT sid, serial#, username, status, sql_id FROM v$session WHERE status = 'ACTIVE' AND type = 'USER';
|
| ☆Blocking Sessions — Who is waiting and who is blocking them |
SELECT sid, blocking_session, event, seconds_in_wait FROM v$session WHERE blocking_session IS NOT NULL;
|
| ☆Kill Session — Terminate a session (sid,serial#) |
ALTER SYSTEM KILL SESSION '123,45678' IMMEDIATE;
|
| ☆Locked Objects — Tables held by DML locks |
SELECT s.username, o.object_name, l.locked_mode FROM v$lock l JOIN dba_objects o ON l.id1 = o.object_id JOIN v$session s ON l.sid = s.sid WHERE l.type = 'TM';
|
| ☆Session SQL Text — What each session is running |
SELECT s.sid, q.sql_text FROM v$session s JOIN v$sql q ON s.sql_id = q.sql_id WHERE s.username IS NOT NULL;
|
| ☆Long Operations — Progress of long-running operations |
SELECT sid, opname, sofar, totalwork, ROUND(sofar/totalwork*100,1) AS pct_done FROM v$session_longops WHERE sofar < totalwork;
|