oracle 查锁

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, ' ') <> ' ';