1. 用于检查系统中锁的简单脚本
select s.username, s.sid, l.type, l.id1, l.id2, l.lmode, l.request, p.spid PID
from v$lock l, v$session s, v$process p
where s.sid = l.sid
and p.addr = s.paddr
and s.username is not null
order by id1, s.sid, request;
2. 显示数据库锁的信息
set pagesize 60
set linesize 132
select s.username username, a.sid sid, a.owner || '.' || a.object object, s.lockwait,
t.sql_text sql
from v$sqltext t, v$session s, v$access a
where t.address = s.sql_address
and t.hash_value = s.sql_hash_value
and s.sid = a.sid
and a.owner != 'SYS'
and upper(substr(a.object,1,2)) != 'V$';
/
3. 产生在数据库中持有的锁的报表
select b.sid, c.username, c.osuser, c.terminal,
decode(b.id2, 0, a.object_name, 'Trans-' || to_char(b.id1)) object_name,
b.type,
decode(b.lmode, 0, '-Waiting-',
1, 'Null',
2, 'Row Share',
3, 'Row Excl',
4, 'Share',
5, 'Sha Row Exc',
6, 'Exclusive', 'Other') "Lock Mode",
decode(b.request, 0, ' ',
1, 'Null',
2, 'Row Share',
3, 'Row Excl',
4, 'Share',
5, 'Sha Row Exc',
6, 'Exclusive', 'Other') "Req Mode"
from dba_objects a, v$lock b, v$session c
where a.object_id(+) = b.id1
and b.sid = c.sid
and c.username is not null
order by b.sid, b.id2;
4. 产生等待锁的用户的报告
column username format a15
column sid format 9990 heading sid
column type format a4
column lmode format 990 heading 'HELD'
column request format 990 heading 'REQ'
column id1 format 9999990
column id2 format 9999990
break on id1 skip 1 dup
spool tfslckwt.lst
select sn.username, m.sid, m.type,
decode(m.lmode, 0, 'None',
1, 'Null',
2, 'Row Share',
3, 'Row Excl.',
4, 'Share',
5, 'S/Row Excl.'
6, 'Exclusive',
lmode, ltrim(to_char(lmode, '990'))) lmode,
decode(m.request, 0, 'None',
责任编辑：小草
您现在的位置： 