ORA-00054: resource busy and acquire with NOWAIT specified while executing procedure

Problem Description:



While executing procedure from sql developer I am getting error "resource busy and acquire with NOWAIT specified"



Error:

exec mtep_miscellaneous_pkg.mtep_delete_delta_proc;Error report:ORA-00054: resource busy and acquire with NOWAIT specifiedORA-06512:



Solution of the Problem

1. select * from v$locked_object

This view lists all locks acquired by every transaction on the system.

In order to see locked object query,



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.sid order by oracle_username, session_id



Cause of the Problem

There was another one session opened and the person was trying to update the table and havent done the commit. Hence throwing error.



SQL> alter system kill session '142, 232';



Now it works.



Now if the person who opened the second session trying execute some query error will be generated saying that session has been killed.

****Happy CoDiNg!!****