oracle10G锁查询、批量杀锁及常用sql

前言

记录1.oracle10G锁查杀技巧 2.资源检查方面的sql

 

锁查杀 

找出所有被锁的对象。注意：不一定是死锁，大部分应该是阻塞，如果发现大量的锁对象，一定要检查程序逻辑了,优化sql

select l.session_id,o.owner,o.object_name
from v$locked_object l,dba_objects o
where l.object_id=o.object_id

批量杀  注意：杀某用户的时候，用另一个用户登录去杀  
权限:角色权限dba 系统权限 unlimited tablespace

declare cursor mycur is  
select b.sid,b.serial#  
　　from v$locked_object a,v$session b  
　　where a.session_id = b.sid group by b.sid,b.serial#;  
  
  
begin  
  for cur in mycur  
    loop    
     execute immediate ( 'alter system  kill session  '''||cur.sid || ','|| cur.SERIAL# ||''' ');  
     end loop;  
  
end;  

 

查询最占用资源的查询

磁盘读频率大于100秒的sql
select b.username username,a.disk_reads reads,
    a.executions exec,a.disk_reads/decode(a.executions,0,1,a.executions) rds_exec_ratio,
    a.sql_text Statement
from  v$sqlarea a,dba_users b
where a.parsing_user_id=b.user_id
 and a.disk_reads > 100000   --单位1000=1s
order by a.disk_reads desc;

查询完整sql语句

根据SELECT * FROM v$sqlarea中找到hash_value

然后执行
SELECT * FROM v$sqltext WHERE hash_value = '&hash_value' ORDER BY piece

即可得到完整的sql语句