Oracle中有哪些锁处理方式

本篇文章为大家展示了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=;

3、杀掉会话

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

ALTER SYSTEM DISCONNECT SESSION ',' IMMEDIATE;

或

ALTER SYSTEM KILL SESSION ',';

附件：

#查询阻塞脚本

col waiting_session for a20col lock_type for a15col mode_requested for a10col mode_held for a10col lock_id1 for a10col lock_id2 for a10set linesize 120set pagesize 999with 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_holdersconnect by prior waiting_session = holding_session start with holding_session is null;

上述内容就是Oracle中有哪些锁处理方式，你们学到知识或技能了吗？如果还想学到更多技能或者丰富自己的知识储备，欢迎关注行业资讯频道。