Posted by: Monish | August 4, 2009

Unlock record of a Oracle table

Session: 1

SQL> select * from dept;
DEPTNO     DNAME          LOC
---------- -------------- -------------
10         ACCOUNTING     NEW YORK
20         RESEARCH       DALLAS
30         SALES          CHICAGO
40         OPERATIONS     BOSTON

Lock one row.

SQL> update dept set dname='DEV' where deptno=30;
1 row updated.

Do not commit

Session: 2

Try to lock the same row

SQL> update dept set dname='TESTING' where deptno=30;

Execute this srcipts to gather lock statistic.

SQL> select (select username from v$session where sid=a.sid) BLOCKER,
2 a.sid,
4 (select username from v$session where sid=b.sid) BLOCKEE,
5 b.sid
6 from v$lock a, v$lock b
7 where a.block = 1
8 and b.request > 0
9 and a.id1 = b.id1
10 and b.id2 = b.id2
11 /

BLOCKER                        SID        'ISBLOCKING BLOCKEE                        SID
------------------------------ ---------- ----------- ------------------------------ ----------
SCOTT                          146        IS BLOCKING SCOTT                          151

SQL> col OBJ format a15
SQL> col SS format a15
SQL> set linesize 200

SQL> select owner||'.'||object_name obj
2 ,oracle_username||' ('||s.status||')' oruser
3 ,os_user_name osuser
4 ,l.process unix
5 ,''''||s.sid||','||s.serial#||'''' ss
6 , rs
7 ,to_char(s.logon_time,'yyyy/mm/dd hh24:mi:ss') time
8 from v$locked_object l
9 ,dba_objects o
10 ,v$session s
11 ,v$transaction t
12 ,v$rollname r
13 where l.object_id = o.object_id
14 and s.sid=l.session_id
15 and s.taddr=t.addr
16 and t.xidusn=r.usn
17 order by osuser, ss, obj
18 /

OBJ             ORUSER            OSUSER                         UNIX         SS RS TIME
--------------- ----------------- ------------------------------ ------------ --------------- ------------------------------ -------------------
SCOTT.DEPT      SCOTT (INACTIVE)  oracle                          17809       '146,188'

_SYSSMU1$ 2009/08/04 06:03:37

here ss means ,

Session: 3

Kill the session to release the lock:


SQL> alter system kill session '146,188';

System altered.


Readers are leaders. You can only master the craft if you constantly study and keep up the hands-on practice. Following are some of the great books for your reference.

Please use the below links to grab your copy. Thank you! 🙂

Expert Oracle Database Architecture (by Thomas Kyte and Darl Kuhn) –>

Expert Oracle Database Architecture: Oracle Database 9i, 10g, and 11g Programming Techniques and Solutions –>

Oracle Database 12c DBA Handbook (Oracle Press) –>
RMAN Recipes for Oracle Database 12c-A Problem-Solution Approach –>
Oracle Database 12c Performance Tuning Recipes –>

Oracle RMAN 11g Backup and Recovery (Oracle Press) –>

OCA/OCP Oracle Database 12c All-in-One Exam Guide (Exams 1Z0-061, 1Z0-062, & 1Z0-063) –>

OCA/OCP Oracle Database 11g All-in-One Exam Guide with CD-ROM –>

Expert Oracle RAC Performance Diagnostics and Tuning –>

Pro Oracle Database 11g Administration (Expert’s Voice in Oracle) –>



  1. Useful stuff!
    Thanks 🙂

  2. Really useful info regarding Unlock record of a Oracle table.

  3. today, i have this problem ‘record is locked by another user’.
    this solution is very useful. now, i can update record.
    thank you very much.

  4. Excellent blog, very well constructed and 100% useful, it helped me with my particular case of record blocked.

  5. Thank you all for your comments..
    In real life scenario what I realized that we can’t simply kill a session which is blocking our session. Since you can find all the session details from v$session, you can ask the user or session owner to release the lock by commenting his/her session or by rolling it back.
    So based on the situation u need to decide if you need to kill the session or wait till the user release the lock.

  6. Thanks!

  7. Really Great job, very useful and well described. Saved me much time of googling. THANK YOU!!!

  8. thank you for this valuable info 🙂

Leave a Reply

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

You are commenting using your 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 )

Connecting to %s


%d bloggers like this: