oracle常用的检查锁和的脚本.docVIP

oracle常用的检查锁和的脚本 把Oracle中常用的检查锁和闩的脚本总结一些。 用于检查系统中锁的简单脚本 select s. username, s. sid, 1. type, 1. idl, 1. id2, 1.1 mode, request, p. spid PID from v$lock 1, v$session s, v$process p where s. sid = 1. sid and p.addr = s.paddr and s.username is not null order by idl, s.sid, request: 显示数据库锁的信息 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$sqItext 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$; 产生在数据库中持有的锁的报表 select b. sid, c. username, c. osuser, c. terminal, decode (b. id2, 0, a. object_name, Trans~， to_char(b? idl)) object_name, b. type, decode (b. lmode, 0,1,2,3,4, decode (b. lmode, 0, 1, 2, 3,4, NulT, Row Share，, Row ExcT , Share，,5, Sha Row Exc, 6, Exclusive, Other) Lock Mode, decode(b. request, 0,…， Null, Row Share, Row ExcT , Share，, Sha Row Exc，, Exclusive, Other) Req Mode from dba_objects a, v$lock b, v$session c where a. object_id(+)二 b. idl and b. sid 二 c. sid and c.username is not null order by b? sid, b. id2; 产生等待锁的用户的报告 column username format al5 column sid format 9990 heading sid column type format a4 column lmode format 990 heading IIELD column request format 990 heading REQ column idl format 9999990 column id2 format 9999990 break on idl skip 1 dup spool tfslckwt. 1st select sn. username, m. sid, m. type, decode (m. lmode, 0, None, Null, Row Share，, Row Excl., Share，, S/Row Excl. Exclusive, lmode, 1 trim(to_cha.r (lmode, ，990’))) lmode, decode (m. request, 0, None, 1, ^NulT, Row Share, ，Row Excl., Share，, S/Row Excl, , Exclusive, request, ltrim(to_char (m? request, 990))) request, m. idl, 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 (idl, id2) in (select s. idl, s. id2 from v$lock s where request !二 0 and s. idl 二 m? idl and s