If you administer a busy database you will see this error from time to time. It is nothing to worry about, but it can be quite annoying when it stops you from doing something.

It simply means that session has a lock on an object that your session is trying to update (or lock). More specifically, your session has asked for a lock on the same object, but has specified the 'nowait' clause. The nowait clause, upon finding something locked, returns an error, rather than waiting for the lock to be released.

Your options are as follows:

• Make a cup of tea and try again later. Hopefully whatever was locking the object will have finished, and you can carry on.
• Attempt to find out who/what is locking the object and kill it off. Use a to find the culprit.

Show sessions that are blocking each other

select 'SID ' || l1.sid ||' is blocking  ' || l2.sid blocking

from v$lock l1, v$lock l2

where l1.block =1 

and l2.request > 0

and l1.id1=l2.id1

and l1.id2=l2.id2

/

Show locked objects
set lines 100 pages 999
col username  format a20
col sess_id  format a10
col object format a25
col mode_held format a10
select oracle_username || ' (' || s.osuser || ')' username, 
s.sid || ',' || s.serial# sess_id, owner || '.' || object_name object, 
object_type, decode( l.block , 0, 'Not Blocking' , 1, 'Blocking' , 2, 'Global') status, 
decode(v.locked_mode , 0, 'None' , 1, 'Null' , 2, 'Row-S (SS)' , 3, 'Row-X (SX)' , 4, 'Share' , 5, 'S/Row-X (SSX)' , 6, 'Exclusive', TO_CHAR(lmode)) mode_held
from v$locked_object v, dba_objects d, v$lock l, v$session s
where  v.object_id = d.object_id
and  v.object_id = l.id1
and  v.session_id = s.sidorder by oracle_username, session_id
/
 
 
Show which row is locked
select do.object_name, 
row_wait_obj#, row_wait_file#, 
row_wait_block#, row_wait_row#, 
dbms_rowid.rowid_create (1, ROW_WAIT_OBJ#, ROW_WAIT_FILE#,     
ROW_WAIT_BLOCK#, ROW_WAIT_ROW#)
from v$session s, dba_objects do
where sid=&sidand  s.ROW_WAIT_OBJ# = do.OBJECT_ID
/
 
 
List locks
column lock_type format a12
column mode_held format a10
column mode_requested format a10
column blocking_others format a20
column username format a10
SELECT session_id, 
lock_type, 
mode_held, 
mode_requested, 
blocking_others, 
lock_id1
FROM dba_lock l
WHERE  lock_type NOT IN ('Media Recovery', 'Redo Thread')
/