Script pro zjistění aktuálně locknutých objektů/tabulek

select
substr(a.os_user_name,1,15) "OS User",
substr(a.oracle_username,1,8) "DB User",
substr(b.owner,1,8) "Schema",
substr(b.object_name,1,20) "Object Name",
substr(b.object_type,1,10) "Type",
substr(c.segment_name,1,15) "RBS",
substr(d.used_urec,1,12) "# of Records",
e.sid,
e.serial#,
case when a.locked_mode='1' then 'Null'
when a.locked_mode='2' then 'Concurrent Read'
when a.locked_mode='3' then 'Concurrent Write'
when a.locked_mode='4' then 'Protected Read'
when a.locked_mode='5' then 'Protected Write'
when a.locked_mode='6' then 'Exclusive'
else ' '
end as locked_desc
,v.type ,vt.DESCRIPTION as LOCK_INFO /* pro lock info */
from v$locked_object a, dba_objects b, dba_rollback_segs c, v$transaction d, v$session e ,v$lock v, V$LOCK_TYPE vt /* pro lock info */
where
a.object_id = b.object_id
and
a.xidusn = c.segment_id
and
a.xidusn = d.xidusn
and
a.xidslot = d.xidslot
and
d.addr = e.taddr
and v.sid=e.sid and v.type=vt.type /* pro lock info */
group by a.os_user_name,a.oracle_username,b.owner,b.object_name,b.object_type,c.segment_name,d.used_urec,e.sid,
e.serial#,a.locked_mode,v.type ,vt.DESCRIPTION
order by object_name

Příspěvek byl publikován v rubrice Admin scripts, Oracle SQL. Můžete si uložit jeho odkaz mezi své oblíbené záložky.