Oracle锁处理、解锁方法

1、查询锁情况

select sid,serial#,event,BLOCKING_SESSION from v$session where event like '%TX%';

2、根据SID查询具体信息(可忽略)

select sid,serial#,username,machine,blocking_session from v$session where sid=<SID>;

3、杀掉会话

#根据1和2中查到的SID和SERIAL# 定位会话，并杀掉

ALTER SYSTEM DISCONNECT SESSION '<SID>,<SERIAL>' IMMEDIATE;

或

ALTER SYSTEM KILL SESSION '<SID>,<SERIAL>';

或

select p.SPID,p.USERNAME,s.SERIAL#,p.PROGRAM

from v$process p,v$session s

where p.ADDR=s.PADDR

AND s.sid='<SID>';

kill -9 <PID>；

附件：

#查询阻塞脚本

col waiting_session for a20

col lock_type for a15

col mode_requested for a10

col mode_held for a10

col lock_id1 for a10

col lock_id2 for a10

set linesize 120

set pagesize 999

with dba_locks_cust as

(SELECT   inst_id||'_'||sid session_id,

            DECODE (TYPE,

                    'MR', 'Media Recovery',

                    'RT', 'Redo Thread',

                    'UN', 'User Name',

                    'TX', 'Transaction',

                    'TM', 'DML',

                    'UL', 'PL/SQL User Lock',

                    'DX', 'Distributed Xaction',

                    'CF', 'Control File',

                    'IS', 'Instance State',

                    'FS', 'File Set',

                    'IR', 'Instance Recovery',

                    'ST', 'Disk Space Transaction',

                    'TS', 'Temp Segment',

                    'IV', 'Library Cache Invalidation',

                    'LS', 'Log Start or Switch',

                    'RW', 'Row Wait',

                    'SQ', 'Sequence Number',

                    'TE', 'Extend Table',

                    'TT', 'Temp Table',

                    TYPE)

               lock_type,

            DECODE (lmode,

                    0, 'None',                       /* Mon Lock equivalent */

                    1, 'Null',                                         /* N */

                    2, 'Row-S (SS)',                                   /* L */

                    3, 'Row-X (SX)',                                   /* R */

                    4, 'Share',                                        /* S */

                    5, 'S/Row-X (SSX)',                                /* C */

                    6, 'Exclusive',                                    /* X */

                    TO_CHAR (lmode))

               mode_held,

            DECODE (request,

                    0, 'None',                       /* Mon Lock equivalent */

                    1, 'Null',                                         /* N */

                    2, 'Row-S (SS)',                                   /* L */

                    3, 'Row-X (SX)',                                   /* R */

                    4, 'Share',                                        /* S */

                    5, 'S/Row-X (SSX)',                                /* C */

                    6, 'Exclusive',                                    /* X */

                    TO_CHAR (request))

               mode_requested,

            TO_CHAR (id1) lock_id1,

            TO_CHAR (id2) lock_id2,

            ctime last_convert,

            DECODE (block,

                    0, 'Not Blocking',  /* Not blocking any other processes */

                    1, 'Blocking',      /* This lock blocks other processes */

                    2, 'Global',   /* This lock is global, so we can't tell */

                    TO_CHAR (block))

               blocking_others

     FROM  gv$lock

),

lock_temp as

(select * from dba_locks_cust),

lock_holder as

(

 select w.session_id waiting_session,

        h.session_id holding_session,

        w.lock_type,

        h.mode_held,

        w.mode_requested,

        w.lock_id1,

        w.lock_id2

  from lock_temp w, lock_temp h

 where h.blocking_others in  ('Blocking','Global')

  and  h.mode_held      !=  'None'

  and  h.mode_held      !=  'Null'

  and  w.mode_requested !=  'None'

  and  w.lock_type       =  h.lock_type

  and  w.lock_id1        =  h.lock_id1

  and  w.lock_id2        =  h.lock_id2

),

lock_holders as

(select waiting_session,holding_session,lock_type,mode_held,

mode_requested,lock_id1,lock_id2

 from lock_holder

  union all

  select holding_session, null, 'None', null, null, null, null

    from lock_holder

 minus

  select waiting_session, null, 'None', null, null, null, null

    from lock_holder

  )

select  lpad(' ',3*(level-1)) || waiting_session waiting_session,

        lock_type,

        mode_requested,

        mode_held,

        lock_id1,

        lock_id2

 from lock_holders

connect by  prior waiting_session = holding_session

  start with holding_session is null;

查询阻塞脚本

巴特西

Oracle锁处理、解锁方法

最新文章

热门文章