一聚教程网:一个值得你收藏的教程网站

时时彩开奖官网:Oracle锁处理、解锁方法

时间:2018-07-02 19:50:12 编辑:猪哥 来源:转载

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 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;

文章评论

热门栏目

  • “游、购、娱”持续升温 中国经济内生动力表现强劲 2019-02-22
  • 殷之光朗诵《我骄傲我是中国人》 2019-02-22
  • 天津检察机关依法决定对窦华港涉嫌受贿案移送审查起诉 2019-02-21
  • 个税法迎第七次大修 起征点调至每年6万元 2019-02-20
  • 价值-热门标签-华商生活 2019-02-19
  • 互联网论坛社区服务管理规定 2019-02-18
  • 合肥一批干部任前公示 朱海舟拟提名为包河区副区长人选 2019-02-18
  • 首趟辽宁援疆旅游专列907名游客进疆抵达沙湾县 2019-02-17
  • 第13期全人代第1回会議政協第13期全国委員会第1回会議 2019-02-17
  • 20年前的伤情鉴定还管用吗? 2019-02-16
  • 2018中国汽车品牌发展峰会在京召开 2019-02-16
  • 陕北共产党发起人——李子洲 2019-02-15
  • 消委会教你如何挑选家居服 2019-02-14
  • 陕西卫计委党组书记胡志强被查,系山西省委原书记胡富国长子 2019-02-14
  • 我发现从五+年代农业用化肥农药,在六+年代几百年长的柿树几乎死光。没人研究! 2019-02-13
  • 390| 261| 570| 150| 938| 667| 773| 656| 336| 237|