• 抓取锁的sql语句-第五次修改


    CREATE OR REPLACE PROCEDURE SOLVE_LOCK

    AS

    V_SQL VARCHAR2(3000); --定义 v_sql 接受抓取锁的sql语句
    V_SQL02 VARCHAR2(3000);
    V_SQL03 VARCHAR2(3000);

    KILL_SID NUMBER;
    KILL_SERIAL NUMBER;

    CUR_LOCK SYS_REFCURSOR; --定义游标变量,循环执行抓取锁的sql语句
    CUR_LOCK02 SYS_REFCURSOR;


    TYPE TP_LOCK IS RECORD( --定义 record类型的 变量
    V_SID NUMBER,
    V_TYPE VARCHAR2(10),
    V_ID1 NUMBER,
    V_ID2 NUMBER,
    V_LMODE VARCHAR2(200),
    V_REQUEST VARCHAR2(200),
    V_LOCK_TIME NUMBER,
    V_BLOCK NUMBER );
    RECORDS_LOCK TP_LOCK;

    TYPE TP_LOCK02 IS RECORD(
    WAITING_SID NUMBER,
    WAITING_SQL VARCHAR2(1000),
    BLOCKER_EVENT VARCHAR2(1000),
    BLOCKING_SID NUMBER,
    BLOCKING_SQL VARCHAR2(1000));
    RECORDS_LOCK02 TP_LOCK02;

    V_BLOCKING_SID NUMBER;
    V_WAITING_SID NUMBER;

    BEGIN
     
    DBMS_OUTPUT.PUT_LINE('------------------查找数据库中是否有锁阻塞、锁等待的情况------------------');
    V_SQL:='SELECT SID,TYPE,ID1,ID2,
    DECODE(LMODE,0, ''NONE'',1,''NULL'', 2, ''ROW SHARE'' ,3, ''ROW EXCLUSIVE'' ,4, ''SHARE'' ,5, ''SHARE ROW EXCLUSIVE'' ,6 ,''EXCLUSIVE'' ,''HAHA'') LOCK_TYPE,
    DECODE(REQUEST,0, ''NONE'',1,''NULL'', 2, ''ROW SHARE'' ,3, ''ROW EXCLUSIVE'' ,4, ''SHARE'' ,5, ''SHARE ROW EXCLUSIVE'' ,6 ,''EXCLUSIVE'' ,''HAHA'') LOCK_REQUEST,
    CTIME,BLOCK FROM V$LOCK WHERE TYPE IN (''TM'',''TX'')' ;


    OPEN CUR_LOCK FOR V_SQL;
    LOOP
    FETCH CUR_LOCK INTO RECORDS_LOCK;
    EXIT WHEN CUR_LOCK%NOTFOUND;

    IF RECORDS_LOCK.V_REQUEST <> 'NONE' THEN --抓取发出请求锁的会话
    DBMS_OUTPUT.PUT_LINE('waiting sid: '||RECORDS_LOCK.V_SID||' is request a lock ,lock_mode is '||RECORDS_LOCK.V_REQUEST||' and being locked '|| RECORDS_LOCK.V_LOCK_TIME||'s');
    V_WAITING_SID:=RECORDS_LOCK.V_SID;
    END IF;

    IF RECORDS_LOCK.V_BLOCK <> 0 THEN --抓取发生锁阻塞的会话
    DBMS_OUTPUT.put_line('blocking sid: '||RECORDS_LOCK.V_SID||' is make a lock , lock_mode is '||RECORDS_LOCK.V_LMODE);
    V_BLOCKING_SID:=RECORDS_LOCK.V_SID;
    END IF;
    END LOOP;
    CLOSE CUR_LOCK;  

    DBMS_OUTPUT.PUT_LINE('------------------查找产生锁的会话、锁等待的会话------------------------');
    V_SQL02:='SELECT DISTINCT
    WAITER.SID "WAITING_SID",
    W_SQL.SQL_TEXT "SQL FROM WAITING SESSION",

    BLOCKER.EVENT "BLOCKER EVENT",
    BLOCKER.SID "BLOCKING SID",

    B_SQL.SQL_TEXT "SQL FROM BLOCKING SESSION"

    FROM V$SESSION WAITER,
    V$SESSION BLOCKER,
    V$SQL W_SQL,
    V$SQL B_SQL
    WHERE WAITER.EVENT=''enq: TX - row lock contention'' AND WAITER.BLOCKING_SESSION=BLOCKER.SID
    AND W_SQL.SQL_ID=WAITER.SQL_ID
    AND B_SQL.SQL_ID =NVL(BLOCKER.SQL_ID,BLOCKER.PREV_SQL_ID)';  

    OPEN CUR_LOCK02 FOR V_SQL02;
    LOOP  
    FETCH CUR_LOCK02 INTO RECORDS_LOCK02;
    EXIT WHEN CUR_LOCK02%NOTFOUND;

    IF RECORDS_LOCK02.WAITING_SID IS NOT NULL THEN
    DBMS_OUTPUT.put_line('waiting sid: '||RECORDS_LOCK02.WAITING_SID||' WAIT_SQL IS: '||RECORDS_LOCK02.WAITING_SQL);
    END IF;

    IF RECORDS_LOCK02.BLOCKING_SID IS NOT NULL THEN
    DBMS_OUTPUT.put_line('blocking sid: '||RECORDS_LOCK02.BLOCKING_SID||' BLOCK_SQL IS: '||RECORDS_LOCK02.BLOCKING_SQL);
    END IF;



    END LOOP;

    CLOSE CUR_LOCK02;


    DBMS_OUTPUT.PUT_LINE('------------------解决 锁阻塞、锁等待------------------');

    SELECT   SID,SERIAL# INTO KILL_SID,KILL_SERIAL FROM V$SESSION WHERE SID=V_BLOCKING_SID ;
       
    DBMS_OUTPUT.put_line('ACTION: ALTER SYSTEM KILL SESSION ( '||KILL_SID||','||KILL_SERIAL||')');



    END SOLVE_LOCK;

  • 相关阅读:
    cookie的路径
    cookie的生命
    cookie详解
    cookie简介&用途
    编码
    请求转发和重定向的区别
    request:域
    request:请求转发,请求包含
    常用的html语法
    request:获取请求的URL
  • 原文地址:https://www.cnblogs.com/iyoume2008/p/4780338.html
Copyright © 2020-2023  润新知