How to Get the Locked Tables List In Oracle

Get the Locked Tables List

SELECT l.inst_id,

    SUBSTR (L.ORACLE_USERNAME, 1, 8) ORA_USER,

    SUBSTR (L.SESSION_ID, 1, 3) SID, S.serial#,

    SUBSTR (O.OWNER || '.' || O.OBJECT_NAME, 1, 40) OBJECT,

    P.SPID OS_PID,

    DECODE (L.LOCKED_MODE,

           0, 'NONE',

           1, 'NULL',

           2, 'ROW SHARE',

           3, 'ROW EXCLUSIVE',

           4, 'SHARE',

           5, 'SHARE ROW EXCLUSIVE',

           6, 'EXCLUSIVE',

           NULL)

      LOCK_MODE

FROM sys.GV_$LOCKED_OBJECT L,

    DBA_OBJECTS O,

    sys.GV_$SESSION S,

    sys.GV_$PROCESS P

WHERE L.OBJECT_ID = O.OBJECT_ID

    AND l.inst_id = s.inst_id

    AND L.SESSION_ID = S.SID

    AND s.inst_id = p.inst_id

    AND S.PADDR = P.ADDR(+)

ORDER BY   l.inst_id;

And to get the details of a particular session given by the sid in the above query use this query

select STATUS ,  PROCESS , PROGRAM , LOGON_TIME  from v$session where sid=

This will give you queries currently running for more than 60 seconds. Note that it prints multiple lines per running query if the SQL has multiple lines. Look at the sid,serial# to see what belongs together.

SELECT s.username,

    s.sid,

    s.serial#,

    s.last_call_et / 60 mins_running,

    q.sql_text    

FROM v$session s

JOIN v$sqltext_with_newlines q ON s.sql_address = q.address

WHERE status = 'ACTIVE' AND TYPE <> 'BACKGROUND' AND last_call_et > 60

ORDER BY sid, serial#, q.piece