Who has got the locks?

**Those times as a DBA when you want to track and hunt down who or what is causing high enq locks?
Sweet mum,try this.Note that if you run this on a RAC database the information returned will be relevant only to the instance.

SELECT vh.sid locking_sid, vs.status status, vs.program program_holding, vw.sid waiter_sid, vsw.program program_waiting FROM v$lock vh, v$lock vw, v$session vs, v$session vsw WHERE (vh.id1, vh.id2) IN (SELECT id1, id2 FROM v$lock WHERE request = 0 INTERSECT SELECT id1, id2 FROM v$lock WHERE lmode = 0) AND vh.id1 = vw.id1 AND vh.id2 = vw.id2 AND vh.request = 0 AND vw.lmode = 0 AND vh.sid = vs.sid AND vw.sid = vsw.sid;

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s