CODEWITHSUNDEEP
sqloracle-databaseoracle10goracle11g
Guddu
Guddu

Reputation: 1588

Lock Held Time on Tables

How can i find the locks held on a table along with the process that is holding the lock and on which table and for how long the lock is being held already for?

Upvotes: 0

Views: 1653

Answers (1)

Gaurav Soni
Gaurav Soni

Reputation: 6346

Not tested this query ,but i think this will give you desired result . 

select session_id "sid",SERIAL#  "Serial",
 substr(object_name,1,20) "Object",
 substr(os_user_name,1,10) "Terminal",
 substr(oracle_username,1,10) "Locker",
 nvl(lockwait,'active') "Wait",
 decode(locked_mode,
        2, 'row share',
        3, 'row exclusive',
        4, 'share',
        5, 'share row exclusive',
        6, 'exclusive',  'unknown') "Lockmode",
 OBJECT_TYPE "Type"
 ,ctime "locked_time"
FROM
 SYS.V_$LOCKED_OBJECT A,
 SYS.ALL_OBJECTS B,
 SYS.V_$SESSION c,
 V$LOCK d
WHERE
 A.OBJECT_ID = B.OBJECT_ID AND
 C.SID = A.SESSION_ID
 AND d.sid =A.session_id
ORDER BY 1 ASC, 5 Desc
/

Here ctime is the time since current mode(locked_mode) was granted. I have found this query in the reference below: Script

Upvotes: 1

Related Questions