This select statement can be run by someone with DBA privileges (as it makes use of v$ views) to list who is locking what.
select a.session_id, c.serial#, a.oracle_username, a.os_user_name, c.machine, a.locked_mode, b.object_name, b.object_type from v$locked_object a,dba_objects b, v$session c where a.object_id = b.object_id and a.session_id = c.sid;
The query below is more specific. With it, you will be able to identify exactly which session is block another session, if any such conditions exist.
select s1.username || '@' || s1.machine || ' ( SID=' || s1.sid || ' ) is blocking ' || s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' as blocking 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;
If necessary, we can use the following example to kill a particular session. It may be useful if the locking session is actually from a hanging process, for example, though typically we should have the user release his/her lock gracefully.
alter system kill session '123,5555' immediate;
Note that the “123” above should be replaced by the session ID, and the “5555” should be replaced by the serial #. Those two values are shown in the first two fields in the select SQL statement above.