oracle 查过程锁,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, ' ') <> ' ';