How to release a lock in Oracle

This mostly comes from two other sources Killing Oracle Session and What’s blocking my lock?

This involves the system tables v$lock and v$session, and using the ‘ALTER SYSTEM’ statement. This can all be done via sqlplus as the system user (you don’t need to be sysdba).

First, 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;

The result set will look like this:

BLOCKING_STATUS
--------------------------------------------------------------------------------
BEN@INTWAREPOD2145 ( SID,S#=134,11102 )  is blocking BEN@INTWAREPOD2145 ( SID,S#=128,30076 )

Now that we know which session is doing the blocking, (134,11102) in this case, we can kill it.

alter system kill session '134,11102';

Note, the alter system statement is powerful stuff, so take care with it.

It's only fair to share...
Share on FacebookGoogle+Tweet about this on TwitterShare on LinkedIn

Leave a Reply