锁的查看和解决

---是否有锁
 select a.*,'有锁' from v$lock a where block=1;

 --阻塞者的会话
  select b.serial#,a.*,b.machine,b.client_info,'阻塞者的会话','alter system kill session '''||a.sid||','||b.serial#||''''
  from v$lock a,v$session b where a.block=1 and a.sid=b.sid;
 
 --等待者的语句
 select distinct '等待者',sw.sid,client_info,sw.serial#,sw.username,qw.sql_text,'等待者的语句'  
 from v$lock lb,v$lock lw,v$session sw,v$sql qw where lw.sid=sw.sid and sw.sql_address=qw.address and sw.lockwait is not

null and lb.block>0;
 
---ECARD用户锁的表
 SELECT b.OBJECT_NAME,a.*,'ECARD用户锁的表' FROM V$LOCKED_OBJECT a,
(select OBJECT_NAME,object_id from dba_objects  where object_id in (SELECT object_id  FROM V$LOCKED_OBJECT) and

owner='SCOTT') b
where a.object_id=b.object_id;
 
 ---阻塞者正在执行的语句
 select distinct sb.sid,sb.username,qb.sql_text,'blockers阻塞者正在执行的语句'  from  v$session sb,v$sql qb,v$lock lb
 where  sb.prev_sql_addr=qb.address  and  lb.block=1 and  lb.sid=sb.sid;

--查看ECARD用户对同一张表的表DML操作
  select * from v$sql where sql_text like  '%&table_name%' and command_type in (6,7,2)   and PARSING_SCHEMA_NAME='ECARD';

-- 杀掉会话
alter system kill session '195,6075';
alter system kill session '11,27006' immediate;

--查询会话状态
 select * from v$session a where a.sid=131

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/31401161/viewspace-2131597/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/31401161/viewspace-2131597/