select s.username,decode(l.type,'TM','TABLE LOCK','TX','ROW LOCK',null) "Lock Level",
o.owner,o.object_name,o.object_type from
v$session s,v$lock l,dba_objects o
where s.sid=l.sid and o.object_id=l.id1
and s.username is not null
/



Who is locking objects... who is waiting

select lpad(' ',decode(l.xidusn,0,3,0))||l.oracle_username "User name",
o.owner,o.object_name,o.object_type
from v$locked_object l,dba_objects o
where l.object_id=o.object_id
order by o.object_id,1 desc
/



select sw.username "Waiting user",bu.username "Locking User",dw.lock_type,dw.mode_held,dw.mode_requested
from dba_waiters dw,v$session sw,v$session bu
where dw.waiting_session=sw.sid and
dw.holding_session=bu.sid
/


@$ORACLE_HOME/rdbms/admin/utllockt.sql