死锁故障处理oracle

查看那些表被锁住：

select b.owner,b.object_name,a.session_id,a.locked_mode from v$locked_object a,dba_objects b where b.object_id = a.object_id;

查看引起死锁的会话：

select b.username,b.sid,b.serial#,logon_time from v$locked_object a,v$session b where a.session_id = b.sid order by b.logon_time;

查看被阻塞的会话：

select * from dba_waiters;

可以提交或回滚阻塞的话，释放锁或者杀掉ORACLE进程：

alter system kill sessin 'sid,serial#';

杀oracle无用进程

select 'kill -9 '||spid
  from v$process
 where addr in (select p.addr
                  from v$process p
                 where pid <> 1
                minus
                select s.paddr
                  from v$session s);

查看连接关闭，但是会话还在的sid

select sid, program, machine
  from v$session
 where paddr in
       (SELECT s.paddr
          FROM x$kglpn p, v$session s
         WHERE p.kglpnuse = s.saddr(+)
           AND p.kglpnmod <> 0
           and kglpnhdl in
               (select p1raw
                  from v$session_wait
                 where event in ('library cache pin',
                                 'library cache lock',
                                 'library cache load lock')));

查看等待的对象

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

查看造成等待的会话

select t2.username,t2.sid,t2.serial#,t2.logon_time from v$locked_object t1,v$session t2 where t1.session_id=t2.sid order by t2.logon_time;

结束指定会话

alter system kill session '14972,29501';

alter system kill session '29,35377';

查看锁表

select * from dba_blockers;

select * from v$lock;

查看是否被锁表

select a.object_name,
       b.session_id,
       c.serial#,
       c.program,
       c.username,
       c.command,
       c.machine,
       c.lockwait
  from all_objects a, v$locked_object b, v$session c
 where a.object_id = b.object_id
   and c.sid = b.session_id;

Select sid,
       serial#,
       username,
       SCHEMANAME,
       osuser,
       MACHINE,
       terminal,
       PROGRAM,
       owner,
       object_name,
       object_type,
       o.object_id,
       s.sql_id,
       s.event
  from dba_objects o, v$locked_object l, v$session s
 where o.object_id = l.object_id
   and s.sid = l.session_id and object_name=upper('SDI_BOND_YIELD_CURVE') and s.username='IR_INDICATOR';

select * from dba_ddl_locks where name=upper('qx_broadband_rent_sum0');

阻塞会话查看

select blocksession.sid as blk_sess_sid,
       blocksession.serial# as blk_sess_serial#,
       blocksession.username as blk_sess_username,
    blocksession.program as blk_sess_program,
       blocksession.osuser as blk_sess_osuser,
    blocksession.logon_time as blk_sess_logon_time,
       blocksession.machine as blk_sess_machine,
       blocksession.status as blk_sess_status,
       blockobject.object_name as blk_table,
       waitsession.sid as wait_sess_sid,
       waitsession.serial# as wait_sess_serial#,
       waitsession.username as wait_sess_username,
    waitsession.program as wait_sess_program,
       waitsession.osuser as wait_sess_osuser,
    waitsession.logon_time as wait_sess_logon_time,
       waitsession.machine as wait_sess_machine,
       waitsession.status as wait_sess_status,
       bsqlarea.SQL_TEXT as blk_ses_sql,
       sqlarea.SQL_TEXT as wait_ses_sql
  from v$lock blocklock,
       v$lock waitlock,
       v$session blocksession,
       v$session waitsession,
       v$locked_object lockedobject,
       dba_objects blockobject,
       v$sqlarea sqlarea,
       v$sqlarea bsqlarea
 where blocklock.block = 1 and blocklock.sid != waitlock.sid and
 blocklock.id1 = waitlock.id1 and blocklock.id2 = waitlock.id2 and
 blocklock.sid = blocksession.sid and waitlock.sid = waitsession.sid and
 lockedobject.session_id = blocksession.sid and
 lockedobject.object_id = blockobject.object_id and
 waitsession.SQL_ADDRESS = sqlarea.ADDRESS(+) and
 waitsession.SQL_HASH_VALUE = sqlarea.HASH_VALUE(+) and
 nvl(blocksession.sql_id,blocksession.PREV_SQL_ID) = bsqlarea.sql_id(+);

set echo off
set feedback off
set heading off
set verify off
set linesize 180
set pagesize 999
column username format a16
column event format a40
column sql_child_number format 9999 heading 'SQL Child|Number'
column blocking_instance format 9999 heading 'Blocker|Instance'
column blocking_session format 99999 heading 'Blocker|Session'
column FINAL_BLOCKING_INSTANCE format 9999 heading 'F_Blocker|Instance'
column FINAL_BLOCKING_SESSION format 99999 heading 'F_Blocker|Session'
column "Lock" format a6
column "Mode" format a6
spool 1.log
Select a.sid,
       a.username,
       a.PROCESS,
       a.MACHINE,
       a.PROGRAM,
       a.logon_time,
       a.sql_id,
       sysdate,
       to_char(b.SQL_FULLTEXT),
       a.seq#,
       a.event,
       a.sql_child_number,
       chr(to_char(bitand(p1, -16777216)) / 16777215) ||
       chr(to_char(bitand(p1, 16711680)) / 65535) "Lock",
       to_char(bitand(p1, 65535)) "Mode",
       a.blocking_instance,
       a.blocking_session,
       a.final_blocking_instance,
       a.final_blocking_session,
       a.last_call_et
  From v$session a,v$sql b
 Where a.sql_id=b.sql_id and a.event = 'enq: TX - row lock contention';

查看锁表的原因

select l.session_id sid,
       s.serial#,
       l.locked_mode,
       l.oracle_username,
       s.user#,
       l.os_user_name,
       s.machine,
       s.terminal,
       a.sql_text,
       a.action
  from v$sqlarea a, v$session s, v$locked_object l
 where l.session_id = s.sid
   and s.prev_sql_addr = a.address
 order by sid, s.serial#;

查看被锁的表

select p.spid,
       c.object_name,
       b.session_id,
       b.oracle_username,
       b.os_user_name
  from v$process p, v$session a, v$locked_object b, all_objects c
 where p.addr = a.paddr
   and a.process = b.process
   and c.object_id = b.object_id;

杀会话    

Select 'alter system kill session ''' || sid || ',' || serial# || ''';'
  from dba_objects o, v$locked_object l, v$session s
 where o.object_id = l.object_id
   and s.sid = l.session_id
   and s.username = 'ABP_INT'
   AND object_name = 'INF_INC_DATA_INF';

select 'alter system disconnect session '''||sid||','||serial#||''''||' immediate;' from v$session where status = 'INACTIVE';

alter system disconnect session '12834,30510' immediate;

查看会话连接总数

SELECT sid,serial#,username,osuser,machine,program,status,event,BLOCKING_SESSION,sql_id FROM gV$SESSION where command=2 order by event;

SELECT sid,serial#,username,osuser,machine,program,status,event,BLOCKING_SESSION,sql_id FROM gV$SESSION where blocking_session is not null order by event;

select event,count(*) from v$session where username='ASSET' group by event order by 2;

select username,sid,event,BLOCKING_INSTANCE,BLOCKING_SESSION,program from v$session where sid='239';

select username,machine,program,count(*) from v$session group by username,machine,program order by 4 desc;

SELECT sid,serial#,username,osuser,machine,program,status FROM V$SESSION where username='ICC_REMIND'/*WHERE status='INACTIVE'*/ order by username;

select * from v$process where addr in (select paddr from v$session where program='JDBC Thin Client');

SELECT sid,serial#,username,osuser,machine,program,status FROM V$SESSION where machine='WORKGROUP\OAS-UMAP-07';

SELECT sid,serial#,username,osuser,machine,program,status FROM V$SESSION where osuser like '%w_tydk_%';

SELECT sid,serial#,username,osuser,machine,program,status FROM V$SESSION /*WHERE status='INACTIVE'*/ order by username;

select * FROM V$PROCESS WHERE ADDR IN(SELECT paddr FROM V$SESSION WHERE machine like 'ducc_dca%' and status='INACTIVE');

select spid from v$process where addr not in (select paddr from v$session);

select * from v$sqltext a where a.address in(select addr from v$process b where b.pid='2703');

select a.pid,a.spid,b.sql_text,b.sql_id from v$process a,v$sqltext b where a.addr=b.address and a.pid='1324';

select * from v$session where username='IAM';

select count(*) from v$session;

select count(*) from v$process;

检查正在回滚

select usn,
       state,
       undoblockstotal "Total",
       undoblocksdone "Done",
       undoblockstotal - undoblocksdone "ToDo",
       undoblocksdone / undoblockstotal "percent",
       decode(cputime,
              0,
              'unknown',
              to_char(sysdate + (((undoblockstotal - undoblocksdone) /
                      (undoblocksdone / cputime)) / 86400),
                      'yyyy-mm-dd hh24:mi')) "Estimated time to complete"
  from gv$fast_start_transactions
 WHERE state <> 'RECOVERED';
show parameter fast_start
alter system set fast_start_parallel_rollback=HIGH;

根据sid查看spid及sql_id

SELECT a.username,
       a.OSUSER,
       a.machine,
       a.program,
       a.sid,
       a.serial#,
       b.spid,
    a.process,
       a.status,
       c.piece,
       c.sql_text,
       c.sql_id
  FROM v$session a, v$process b, v$sqltext c
 WHERE b.spid ='103603'
   AND b.addr = a.paddr
   AND a.sql_address = c.address(+)
 ORDER BY c.piece;

SELECT a.username,
       a.OSUSER,
       a.machine,
       a.program,
       a.sid,
       a.serial#,
       b.spid,
       a.process,
       a.status,
       c.piece,
       c.sql_text,
       c.sql_id
  FROM v$session a, v$process b, v$sqltext c
 WHERE b.spid ='16829'
   AND b.addr = a.paddr
   AND a.sql_address = c.address(+)
 ORDER BY c.piece;

SELECT sid,serial#,username,osuser,machine,program,status,event,sql_id FROM gV$SESSION where sid in(1926,1981,1651,2038,2093,2258,2423,2478,2973);

select spid,osuser,s.sql_id,s.program,s.sid,s.machine from v$session s, v$process p where s.paddr = p.addr and p.spid in (1483,1485,1488,1490);

killed会话没关闭处理

select spid, username, terminal, program, background
  from v$process
 where addr in (select p.addr
                  from v$process p
                 where pid <> 1
                minus
                select s.paddr
                  from v$session s);  --11550  11552

SELECT * FROM V$PROCESS WHERE ADDR IN(SELECT paddr FROM V$SESSION WHERE machine like 'ducc_dca%' and status='INACTIVE');

根据数据库sid找出spid（系统进程）

select spid, osuser, s.program from v$session s, v$process p where s.paddr = p.addr and s.sid ='2559';

查看阻塞

select sid,block from v$lock where block=1;

select sid,type,lmode,id1,id2,request,block from v$lock where type in('TX','TM') order by 1,2;

select sid,event from v$session_wait where sid in(69,101);

查看谁阻塞了谁

select object_id,session_id from v$locked_object where object_id in(select object_id from v$locked_object where session_id=11287);

查看等待事件

select event,count(*) from v$session_wait group by event order by 2 desc;

select event,sid,p1,p2,p3 from v$session_wait where event not like 'SQL*%' and event not like 'rdbms%';

select to_char(sample_time, 'yyyymmdd hh24:mi:ss'), event, p1, p2, count(1)
  from gv$active_session_history
 where sql_id = 'b7y00v7k7zv2n'
   and event in ('gc buffer busy acquire', 'gc cr request')
   and inst_id = 2
 group by to_char(sample_time, 'yyyymmdd hh24:mi:ss'), event, p1, p2;

根据等待事件查看sql_id  

select sw.p1,
        s.sql_id,
        s.username,
        count(*),
        (ratio_to_report(count(*)) over()) * 100 pct
   from v$session s, v$session_wait sw
  where s.event like '%db file sequential read%'
    and s.sid = sw.sid
  group by sw.p1, s.sql_id,s.username
  order by count(*) desc;

根据sql_id查看sql  

select * from v$session ss where ss.USERNAME='NOSS_OP' AND ss.STATUS!='INACTIVE' AND ss.PROGRAM='sqlplus@abp-app-007 (TNS V1-V3)';

select to_char(t.SQL_FULLTEXT) from v$sql t where sql_id='d49gm4w1rnj47';

select * from v$sql_plan where sql_id='d49gm4w1rnj47';

查看死锁操作系统进程

select pro.spid from v$session ses,v$process pro where ses.sid=XX and ses.paddr=pro.addr;

查询引起死锁的语句  

select sql_text from v$sql where hash_value in   (select sql_hash_value from v$session where sid in  (select session_id from v$locked_object));