OracleDB‎ > ‎

Kill Session

kill oracle user session

look up who is logged in and holding how many sessions

SELECT username, COUNT(*) FROM v$session GROUP BY username;
SQL> SELECT sid, serial# FROM v$session
2* WHERE username='SALAPATI';
SID SERIAL#
----------------
10 32
SQL> ALTER SYSTEM KILL SESSION '10,32';
System altered.
SQL>

if that is taking too long you can kill the session at the operating system level. get the process ID buy running the following

SQL> SELECT process,sid,serial# FROM v$session
WHERE username='&user';
Enter value for user: SALAPATI
old 2: username='&user'
new 2: username='SALAPATI'
PROCESS SID SERIAL#
-----------------------------------
2920:2836 10 34
SQL>
select vs.sid, vs.username, vs.osuser, vs.process fg_pid,
vp.spid bg_pid
from v$session vs, v$process vp
where vs.paddr = vp.addr


Find out who is blocking session

SELECT sid, blocking_session, username, event FROM v$session WHERE blocking_session_status = 'VALID';
SID BLOCKING_SESSION USERNAME EVENT
--- ---------------- -------- ----------------------------------
24 32 SALAPATI enq: TX - row lock contention
SQL>


The previous query shows that the user with the SID 24 is being blocked by user with the SID 32. The event column shows the type of lock that the blocking session holds.


The data dictionary tables that you need to look at to find locking information are the DBA_LOCKS, DBA_BLOCKERS, and DBA_WAITERS views. If, for some reason, you don't see the DBA_BLOCKERS view, run the catblock.sql script, located in the $ORACLE_HOME/rdbms/admin directory, to create it.



Identify how many days Oracle users have been idle:


select USERNAME, floor(LAST_CALL_ET/86400) from v$session where status='INACTIVE';




Comments