Sunday, August 16, 2015

Oracle : Script shows object locks currently held in the database

select /*+ rule */
substrb(vlo.oracle_username,1,10) db_user,
substrb(vlo.os_user_name,1,10) os_user,
substrb(to_char(vlo.session_id),1,4) sid,
decode(vs.lockwait,null,substrb(vs.status,1,3),'WAI') "STA",
substrb(dbo.owner,1,10) owner,
substrb(dbo.object_name,1,25) obj_name,
substrb(dbo.object_type,1,3) typ,
substrb(decode(locked_mode,0,'NONE',1,'NULL',2,'ROWS',3,'ROWX',
  4,'SHAR',5,'SRWX',6,'EXCL',to_char(vlo.locked_mode)),1,4) "MODE"
from v$locked_object vlo, v$session vs, dba_objects dbo
where vlo.session_id = vs.sid
and vs.type != 'BACKGROUND'
and vlo.object_id = dbo.object_id
order by decode(vs.lockwait,null,decode(vs.status,'ACTIVE',2,3),1),3,1
/

No comments:

Post a Comment