Let My Data Go!

Beautifully written about Oracle DB Session Locks,
also check http://www.oracle-base.com/articles/misc/killing-oracle-sessions.php

Oracle Tips and Tricks -- David Fitzjarrell

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:

V$LOCK
V$LOCKED_OBJECT

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

TM —…

View original post 2,320 more words

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s