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 Facebook
Facebook
Tweet about this on Twitter
Twitter
Share on LinkedIn
Linkedin

Leave a Reply