1, 'Null',
2, 'Row Share',
3, 'Row Excl.',
4, 'Share',
5, 'S/Row Excl,',
6, 'Exclusive',
request, ltrim(to_char(m.request, '990'))) request,
m.id1,
m.id2
from v$session sn, v$lock m
where (sn.sid = m.sid and m.request != 0)
or (sn.sid = m.sid and m.request = 0 and lmode = 4 and (id1, id2)
in(select s.id1, s.id2
from v$lock s
where request != 0
and s.id1 = m.id1
and s.id2 = m.id2)
)
order by id1, id2, m.request;
spool off
clear breaks
5. 显示持有锁的会话的信息
set linesize 132 pagesize 66
break on Kill on username on terminal
column Kill heading 'Kill String' fromat a13
column res heading 'Resource Type' format 999
column id1 format 9999990
column id2 format 9999990
column lmode heading 'Lock Held' format a20
column request heading 'Lock Requested' format a20
column serial# format 99999
column username format a10 heading "Username"
column terminal heading Term format a6
column tab format a35 heading "Table Name"
column owner format a9
column Address format a18
select nvl(s.username, 'Internal') username,
nvl(s.terminal, 'None') terminal,
l.sid || ',' || s.serial# Kill,
u1.name || '.' || substr(t1.name, 1, 20) tab,
decode(l.lmode, 1, 'No Lock',
2, 'Row Share',
3, 'Row Exclusive',
4, 'Share',
5, 'Share Row Exclusive',
6, 'Exclusive', null) lmode,
decode(l.request, 1, 'No Lock',
2, 'Row Share',
3, 'Row Exclusive',
4, 'Share',
5, 'Share Row Exclusive',
6, 'Exclusive', null) request
from v$lock l, v$session s, sys.user$ u1, sys.obj$ t1
where l.sid = s.sid
and t1.obj# = decode(l.id2, 0, l.id1, l.id2)
and u1.user# = t1.owner#
and s.type != 'BACKGROUND'
order by 1, 2, 5;
6. 用于鉴别系统中闩性能的脚本
column name heading "Name" format a20
column pid heading "HSid" format a3
column gets heading "Gets" format 999999990
column misses heading "Miss" format 99990
column im_gets heading "ImG" format 99999990
column im_misses heading "ImM" format 999990
column sleeps heading "Sleeps" format 99990
select n.name name, h.pid pid, l.gets gets, l.misses misses,
l.immediate_gets im_gets, l.immediate_misses im_misses, l.sleeps sleeps
from v$latchname n, v$latchholder h, v$latch l
where l.latch# = n.latch#
and l.addr = h.laddr(+);
责任编辑：小草
您现在的位置： 