Identifying locks and session details

Which session is locking and who is waiting on a database object.

set line 200
select l.inst_id,s.sid, s.serial#,l.TYPE,l.ID1,l.ID2,l.LMODE, l.REQUEST, blocking_session, blocking_instance from gv$lock l,gv$session s where (ID1,ID2,l.TYPE) in (select ID1,ID2,TYPE from gv$lock where request>0) and l.inst_id=s.inst_id and l.sid=s.sid;

Show details of a particular session and what it is waiting on

set line 200 col username for a10 col event for a60
select inst_id, sid, serial#, username, status, event, seconds_in_wait, state --osuser, --machine, --program from gv$session where inst_id = # and sid = ### and serial# = #### ;

Look at what objects a session has locked:

col object_name for a30 col owner for a15

select c.owner, c.object_id, c.object_name,  c.object_type, b.sid, b.serial#, b.status, b.username holder_username,    decode(locked_mode, 0, 'None', 1, 'Null', 2, 'Row Share',         3, 'Row Exclusive', 4, 'Share', 5, 'Sub Share Exclusive',         6, 'Exclusive',         locked_mode) locked_mode  from v$locked_object a ,  v$session b, dba_objects c  where b.sid = a.session_id  and a.object_id = c.object_id  and b.sid = ???;

Which row in a table is being locked

select  object_type, owner, object_name,  nvl(subobject_name,'none') part_name, data_object_id,  dbms_rowid.rowid_create (  1,o.data_object_id,row_wait_file#,row_wait_block#,row_wait_row#) row_id from v$session s, dba_objects o where o.object_id = s.row_wait_obj# and sid = ###;
select * from {object_name} where rowid = '{row_id}';

Look at what statement the session is currently running

select SQL_ID, PLSQL_OBJECT_ID, PLSQL_SUBPROGRAM_ID, STATUS, EVENT from v$session where sid = 527;

Kill the session

alter SYSTEM kill session 'SID, SERIAL#,@INST_ID';

*********************************************
keyword lock
*********************************************