Oracle死锁处理实用技巧与案例分析

alter system kill session '97,12441';

SELECT * FROM V$DB_OBJECT_CACHE WHERE OWNER='LLTRADE' AND LOCKS!='0'; 


SELECT S.USERNAME,S.OSUSER,S.SID,S.SERIAL#,P.SPID FROM V$SESSION S,V$PROCESS P WHERE S.PADDR=P.ADDR AND S.USERNAME IS NOT NULL;

select t2.username||'   '||t2.sid||'   '||t2.serial#||'   '||t2.logon_time||'   '||t3.sql_text
 from v$locked_object t1,v$session t2,v$sqltext t3
 where t1.session_id=t2.sid 
 and t2.sql_address=t3.address
order by t2.logon_time ,T2.sid;

DBMS_STATS.GATHER_TABLE_STATS(ownname=>'lltrade',tabname=>'tagtprepaylog',CASCADE=>TRUE,estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE);
        
select 'blocker('||wb.holding_session||':'||sb.username||')-sql:'||qb.sql_text blockers,sb.machine,sb.terminal,
       'waiter ('||wb.waiting_session||':'||sw.username||')-sql:'||qw.sql_text waiters ,sw.machine,sb.terminal
from  dba_waiters wb,
      v$session   sb,
      v$session   sw,
      v$sqlarea   qb,
      v$sqlarea   qw
where wb.holding_session=sb.sid
and   wb.waiting_session=sw.sid
and   sb.prev_sql_addr=qb.address
and   sw.sql_address=qw.address
and   wb.mode_held<>'None' 

select v$lock.sid,
decode(v$lock.type,