Oracle 常用指令 & SQL - Lock 篇 (Lock section)

Oracle Lock 相關

-- RAC 架構查詢 lock

set linesize 180
col user_status format a15
col sid_serial format a15
col program format a30 wrapped
col machine format a15 wrapped
col osuser format a15 wrapped
col conn_instance format a15
col object_name format a25 wrapped
 SELECT DECODE (l.block, 0, 'Waiting', 'Blocking ->') user_status,
     CHR (39) || s.sid || ',' || s.serial# || CHR (39) sid_serial,
     (SELECT instance_name
      FROM gv$instance
      WHERE inst_id = l.inst_id)
      conn_instance,
     s.sid,
     s.program,
     s.osuser,
     s.machine,
     DECODE (l.TYPE,
         'RT', 'Redo Log Buffer',
         'TD', 'Dictionary',
         'TM', 'DML',
         'TS', 'Temp Segments',
         'TX', 'Transaction',
         'UL', 'User',
         'RW', 'Row Wait',
         l.TYPE)
      lock_type--,id1
           --,id2
     ,
     DECODE (l.lmode,
         0, 'None',
         1, 'Null',
         2, 'Row Share',
         3, 'Row Excl.',
         4, 'Share',
         5, 'S/Row Excl.',
         6, 'Exclusive',
         LTRIM (TO_CHAR (lmode, '990')))
      lock_mode,
     ctime--,DECODE(l.BLOCK, 0, 'Not Blocking', 1, 'Blocking', 2, 'Global') lock_status
     ,
     object_name
  FROM gv$lock l
     JOIN gv$session s ON (l.inst_id = s.inst_id AND l.sid = s.sid)
     JOIN gv$locked_object o
      ON (o.inst_id = s.inst_id AND s.sid = o.session_id)
     JOIN dba_objects d ON (d.object_id = o.object_id)
  WHERE (l.id1, l.id2, l.TYPE) IN (SELECT id1, id2, TYPE
                   FROM gv$lock
                   WHERE request > 0)
ORDER BY id1, id2, ctime DESC; 

-- RAC 架構查詢 lock

SELECT DISTINCT
     s1.username
    || '@'
    || s1.machine
    || ' ( INST='
    || s1.inst_id
    || ' SID='
    || s1.sid
    || ' Serail#='
    || s1.serial#
    || ' ) IS BLOCKING '
    || s2.username
    || '@'
    || s2.machine
    || ' ( INST='
    || s2.inst_id
    || ' SID='
    || s2.sid
    || ' Serial#='
    || s2.serial#
    || ' ) '
     AS blocking_status
 FROM gv$lock l1,
    gv$session s1,
    gv$lock l2,
    gv$session s2
 WHERE   s1.sid = l1.sid
    AND s2.sid = l2.sid
    AND s1.inst_id = l1.inst_id
    AND s2.inst_id = l2.inst_id
    AND l1.block > 0
    AND l2.request > 0
    AND l1.id1 = l2.id1
    AND l1.id2 = l2.id2;

-- Kill Session in RAC
alter system kill session 'sid,serial#,@inst_id';

-- Kill Session in Standalone
alter system kill session 'sid,serial#;

-- 整批 kill session by username

declare
  cursor connList is
    select serial#, sid, inst_id from gv$session where username = upper('USERNAME');
begin
  for rec in connList
  loop
    execute immediate 'alter system kill session ''' || rec.sid || ',' || rec.serial# || ',@' || rec.inst_id || ''' immediate' ;
  end loop;
end;
/

-- 列出作業系統待 kill session process (db session 砍不掉時，從作業系統上砍) (需逐台執行)

select 'kill -9 '|| spid from v$process where addr in (select paddr from v$session where username = upper('USERNAME'));
select 'kill -9 '|| spid from v$process where addr in (select paddr from v$session where sid = 1234);

-- kill 所有 Oracle 連接的 Process (逐台執行)

ps aux | grep -v grep | grep LOCAL=NO | awk '{print $2}' | xargs kill -9

-- Single Instance 查詢 lock

set linesize 180
col user_status format a15
col sid_serial format a15
col program format a30 wrapped
col machine format a15 wrapped
col osuser format a15 wrapped
col conn_instance format a15
col object_name format a25 wrapped
 SELECT DECODE (l.block, 0, 'Waiting', 'Blocking ->') user_status,
     CHR (39) || s.sid || ',' || s.serial# || CHR (39) sid_serial,
     (SELECT instance_name
      FROM v$instance)
      conn_instance,
     s.sid,
     s.program,
     s.osuser,
     s.machine,
     DECODE (l.TYPE,
         'RT', 'Redo Log Buffer',
         'TD', 'Dictionary',
         'TM', 'DML',
         'TS', 'Temp Segments',
         'TX', 'Transaction',
         'UL', 'User',
         'RW', 'Row Wait',
         l.TYPE)
      lock_type--,id1
           --,id2
     ,
     DECODE (l.lmode,
         0, 'None',
         1, 'Null',
         2, 'Row Share',
         3, 'Row Excl.',
         4, 'Share',
         5, 'S/Row Excl.',
         6, 'Exclusive',
         LTRIM (TO_CHAR (lmode, '990')))
      lock_mode,
     ctime--,DECODE(l.BLOCK, 0, 'Not Blocking', 1, 'Blocking', 2, 'Global') lock_status
     ,
     object_name
  FROM v$lock l
     JOIN v$session s ON (l.sid = s.sid)
     JOIN v$locked_object o
      ON (s.sid = o.session_id)
     JOIN dba_objects d ON (d.object_id = o.object_id)
  WHERE (l.id1, l.id2, l.TYPE) IN (SELECT id1, id2, TYPE
                   FROM v$lock
                   WHERE request > 0)
ORDER BY id1, id2, ctime DESC;