How to find locked objects

Here is the script how to find locked objects in oracle.

select
oracle_username
os_user_name,
locked_mode,
object_name,
object_type
from
v$locked_object a,dba_objects b
where
a.object_id = b.object_id

The possible values for lock_mode are:
1: null,
2: Row Share (SS),
3: Row Exclusive (SX),
4: Share (S),
5: Share Row Exclusive (SSX) and
6: Exclusive(X)

1st step to solve this problem is determine who’s holding the lock:

select
	s1.username || '@' || s1.machine
	|| ' ( SID,S#=' || s1.sid || ',' || s1.serial# || ' )  is blocking '
	|| s2.username || '@' || s2.machine
	|| ' ( SID,S#=' || s2.sid || ',' || s2.serial# || ' )'
		AS blocking_status
from
	v$lock l1,
	v$session s1,
	v$lock l2,
	v$session s2
where
	s1.sid = l1.sid
	and s2.sid = l2.sid
	and l1.BLOCK = 1
	and l2.request > 0
	and l1.id1 = l2.id1
	and l2.id2 = l2.id2;

2nd Step is alter system to kill session appropriate with query result :
Take care with alter system command.

alter system kill session 'sid,serial#';

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