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

最新下载

秒速时时彩平台: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-05-20
  • 【春到渭南】随手拍渭南各地区春景,一贴看尽渭南春色! 2019-05-20
  • 汪峰章子怡带女儿醒醒看马戏表演 满脸幸福 2019-05-19
  • 爱护民生:什么基金都不能买,即使获利,也不会给分多少红利,只是意思意思。 2019-05-19
  • 北京市北京盈之宝4S店【在线咨询】 2019-05-18
  • 美媒:研究发现高血压会增加痴呆风险 2019-05-18
  • 房价还会跌吗?这几条黄金线索告诉你背后的事实 ——凤凰网房产西安 2019-05-17
  • 搞市场经济,也不意味这政府就能偷懒,不是什么都甩给市场,美其名曰"市场会自我调节" 2019-05-17
  • 经常吃它到老耳聪目明牙齿好 让你越来越健康-美食资讯 2019-05-16
  • 端午小长假杭州接待游客454.8万人次 9成民宿预订一空 2019-05-16
  • 十九大党章修正案的新时代意蕴 2019-05-15
  • 勇敢挑战“不可能”(今日谈) 2019-05-14
  • 第十三届“三晋之春”合唱比赛在阳泉开幕 2019-05-14
  • 驴友在北京凤凰岭失联 百人搜救七天仍未找到踪迹 2019-05-13
  • 光明网招聘时事新闻分析编辑 2019-05-12
  • 164| 260| 521| 815| 53| 260| 877| 39| 809| 653|