Beautifully written about Oracle DB Session Locks,
also check http://www.oracle-base.com/articles/misc/killing-oracle-sessions.php
I’ve written here regarding Oracle locks but apparently the post didn’t explain enough about the views involved. Let’s fix that.
Two views are probably the most beneficial when investigating locks:
with V$LOCK providing an overall view of the active locks in a database and V$LOCKED_OBJECT providing detail on who has TM (DML) locks against which database objects.
Looking at V$LOCK to see what information is available we see that this view provides:
the lock address (KADDR) the SID of the session holding the lock (SID) the type of lock (TYPE) (including two additional columns [ID1,ID2] which can further identify the lock) the lock mode (LMODE) the requested mode (REQUEST) the time since the current mode was granted (CTIME) whether or not the lock is blocking another session (BLOCK)
The TYPE column describes whether this is a user or a system lock. The three user-type locks are
View original post 2,320 more words