2011-01-14
--查锁的SQL
select p.spid, s.serial#, s.sid, t.SQL_TEXT
from v$process p, v$session s, v$sqltext t, dba_objects o, v$locked_object l
where s.paddr = p.addr
and s.sql_address = t.ADDRESS
and s.status='ACTIVE'
and s.username='LXGS'
and o.object_id = l.object_id
and s.sid = l.session_id
order by spid,t.PIECE
;
--取 杀锁进程 语句 需将结果拷贝出来在command window执行
select 'alter system kill session ''' || s.sid || ',' || s.serial# || ''';' kill_session,
decode(l.LOCKED_MODE,0,'none',1,'null',2,'行共享',3,'行独占',4,'共享锁',5,'共享行独占',6,'独占(X)') lock_mode,
p.SPID,s.MACHINE,s.program,s.username ,'kill -9 '||p.spid kill_spid
from v$session s, dba_objects o, v$locked_object l, v$process p
where o.object_id = l.object_id
and s.sid = l.session_id
and s.paddr = p.addr
-- and s.username = 'LXGS'
;
1>.unix/linux下运行top 3查到最高占用cpu的sid
select addr from v$process where spid='最高占用cpu的sid';
2>.根据地址查到会话相关信息
select s.sid,s.SQL_ADDRESS,s.USERNAME,s.STATUS,s.PROGRAM,s.OSUSER,s.MACHINE from v$session s where s.PADDR='$addr';
3>根据会话中的ID和sql地址查找相关锁和SQL
<1>查到出问题的对象
select object_id from v$locked_object where session_id="v$session.sid";
select * from dba_objects where object_id="v$locked_object.object";
<2>查找相关SQL
select * from v$sqltext tt where tt.ADDRESS="v$session.SQL_ADDRESS"
如果以上部份没有记录使用以下SQL查找数据库当前执行SQL
select a.SID, b.SQL_TEXT, a.MACHINE
from v$session a
join v$sql b on a.SQL_ADDRESS = b.ADDRESS
where a.STATUS = 'ACTIVE' and nvl(a.MACHINE, ' ') <> ' ';