看锁总结

查看阻塞

set pagesize 40 linesize 150

column blockers format a45

column waiters format a45

select '阻塞者('||sb.sid||':'||sb.serial#||'-'||sb.username||')-'||qb.sql_text blockers,

'等待者('||sw.sid||':'||sw.serial#||'-'||sw.username||')-'||qw.sql_text waiters

from v$lock lb,v$lock lw,v$session sb,v$session sw,v$sql qb,v$sql qw

where lb.sid=sb.sid

and lw.sid=sw.sid

and sb.prev_sql_addr=qb.address

and sw.sql_address=qw.address

and lb.id1=lw.id1

and sb.lockwait is null

and sw.lockwait is not null

and lb.block=1;

查看锁

set pagesize 40 linesize 150

column username format a9

column sid format 9999

column serial# format 99999999

column mode_locked format a12

column os_user_name format a16

column object_name format a12

column object_type format a12

select s.username,s.sid,s.serial#,

decode(lo.locked_mode,

0,'none',

1,'null',

2,'row-s(ss)',

3,'row-x(sx)',

4,'share',

5,'s/row-x(ssx)',

6,'exclusive',

to_char(lo.locked_mode)) mode_locked,

lo.os_user_name,

do.object_name,do.object_type

from v$session s,v$locked_object lo,dba_objects do

where

lo.object_id=do.object_id and s.sid=lo.session_id;

#########################以下看锁的信息##########################

select * from v$sql vl,v$session vn

where vl.ADDRESS= decode(vn.SQL_ADDRESS,null,vn.PREV_SQL_ADDR,VN.SQL_ADDRESS)

and vn.sid=137;

看看这个sid在执行啥 （现在这个可以看全局，安全一点，不会kill错）

set long 9999

select  SQL_FULLTEXT,sid,serial#,OSUSER,MACHINE,PROGRAM,process,TERMINAL  from v$sqlarea,v$session where hash_value=sql_hash_value and address=sql_address and sid in (803); 

process其实就是进程的pid，windows下是pid：线程号

PROGRAM 就是程序名咯

其他不解释了，看得懂

这个可以看到系统pid号，程序名称

select a.sid, a.SERIAL#, b.spid, a.status, a.PROGRAM
from v$session a, V$PROCESS b
where a.sid in (137)
and a.paddr=b.ADDR
order by a.sid;

或者这个

select  sid,serial#,OSUSER,MACHINE,PROGRAM,process,TERMINAL  from v$session where sid in (4275,4277,2857); 

根据系统pid查看数据库在执行的sql

set long 9999

select  SQL_FULLTEXT,OSUSER,MACHINE,PROGRAM,process,TERMINAL  from v$sqlarea,v$session where hash_value=sql_hash_value and address=sql_address and sid in (select a.sid from v$session a, V$PROCESS b where b.spid in (1200264) and a.paddr=b.ADDR);