---------------------
-- 蟇碁Π 螳豌 譟壱--
---------------------
col object_name format a30
col subobject_name format a10
col oracle_username format a20
select a.object_name, a.subobject_name, b.process, b.session_id, b.oracle_username, b.locked_mode
from dba_objects a, v$locked_object b
where a.object_id = b.object_id;
------------------------
--觚襦 語 覈磯--
------------------------
column username for a10
column sid for 999
column lock_type for a25
column mode_held for a11
column mod_requested for a10
column lock_id1 for a8
column lock_id2 format a8
select a.sid,
decode(a.type, 'MR', 'Media Recovery',
'RT', 'Redo Thread',
'UN', 'User Name',
'TX', 'Transaction',
'TM', 'DML',
'UL', 'PL/SQL User Lock',
'DX', 'Distributed Xaction',
'CF', 'Control File',
'IS', 'Instance State',
'FS', 'File Set',
'IR', 'Instance Recovery',
'ST', 'Disk Space Transaction',
'TS', 'Temp Segment',
'IV', 'Library Cache Invaildation',
'LS', 'Log Start or Switch',
'RW', 'Row Wait',
'SQ', 'Sequence Number',
'TE', 'Extend Table',
'TT', 'Temp Table', a.type) lock_type,
decode(a.lmode,
0, 'None', /*Non Lock equivalent*/
1, 'Null', /*N*/
2, 'Row-S(SS)', /*L*/
3, 'Row-X(SX)', /*R*/
4, 'Share', /*S*/
5, 'S/Row-X(SSX)',/*C*/
6, 'Exclusive', /*X*/
to_char(a.lmode)) mode_held,
decode(a.request,
0, 'None', /*Non Lock equivalent*/
1, 'Null', /*N*/
2, 'Row-S(SS)', /*L*/
3, 'Row-X(SX)', /*R*/
4, 'Share', /*S*/
5, 'S/Row-X(SSX)',/*C*/
6, 'Exclusive', /*X*/
to_char(a.request)) mode_requested,
to_char(a.id1) lock_id1,
to_char(a.id2) lock_id2
from v$lock a
where a.type not in ('MR', 'DM', 'RT')
and (id1, id2) in (select b.id1, b.id2
from v$lock b
where b.id1 = a.id1
and b.id2=a.id2
and b.request > 0)
order by 5,6;
-------------------------
--曙朱 誤 wait 譟壱--
-------------------------
select s.sid, s.username, s.machine,
decode(s.status, 'INACTIVE', '曙 蟇瑚 語(INACTIVE)', s.status) status, e.event
from v$session s, v$session_wait e
where s.sid = e.sid