Oracle锁处理、解锁方法

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

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

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

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

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 ;