Identify session waiting on enq: TX - row lock contention

/*
enq: TX - row lock contention:
This is indicative of a session waiting for a row lock held by another session; the amount of wait time associated with this wait event is excessive and can be responsible for performance issues observed in the application. TX enqueue are acquired exclusive when a transaction initiates its first change and held until the transaction does a COMMIT or ROLLBACK.
*/

--For which SQL currently is waiting on

select sid, sql_text from v$session s, v$sql q
where sid in
(select sid from v$session where state in ('WAITING') and wait_class != 'Idle' and event='enq: TX - row lock contention'
and (q.sql_id = s.sql_id or q.sql_id = s.prev_sql_id));

--The blocking session is
select blocking_session, sid, serial#, wait_class, seconds_in_wait
from v$session where blocking_session is not NULL
order by blocking_session;


--finding locked objects
SELECT O.OWNER, O.OBJECT_ID, O.OBJECT_NAME, O.OBJECT_TYPE, L.TYPE
FROM DBA_OBJECTS O, V$LOCK L
WHERE O.OBJECT_ID = L.ID1

and O.OWNER !='SYS';


-- report the session and tables that are blocking other sessions
select    nvl(S.USERNAME,'Internal') username,
    nvl(S.TERMINAL,'None') terminal,
    L.SID||','||S.SERIAL# Kill,
    U1.NAME||'.'||substr(T1.NAME,1,20) tab,
    decode(L.LMODE,1,'No Lock',
        2,'Row Share',
        3,'Row Exclusive',
        4,'Share',
        5,'Share Row Exclusive',
        6,'Exclusive',null) lmode,
    decode(L.REQUEST,1,'No Lock',
        2,'Row Share',
        3,'Row Exclusive',
        4,'Share',
        5,'Share Row Exclusive',
        6,'Exclusive',null) request
from    V$LOCK L,
    V$SESSION S,
    SYS.USER$ U1,
    SYS.OBJ$ T1
where    L.SID = S.SID
and    T1.OBJ# = decode(L.ID2,0,L.ID1,L.ID2)
and    U1.USER# = T1.OWNER#
and    S.TYPE != 'BACKGROUND'
and     S.sid in(select blocking_session from v$session where blocking_session is not null)
order by 1,2,5
/

--Query to see uncommitted sessions:

SELECT t.start_time, s.sid, s.serial#,s.sql_id,s.event, s.username, s.status,s.schemaname, s.osuser
   , s.process, s.machine, s.terminal, s.program, s.module
   , to_char(s.logon_time,'DD/MON/YY HH24:MI:SS') logon_time
FROM v$transaction t, v$session s
WHERE s.saddr = t.ses_addr
ORDER BY start_time