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.sid
order 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=&sid
and 	s.ROW_WAIT_OBJ# = do.OBJECT_ID
/

select * from <table> where rowid=<rowid>;

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')
/