CREATE OR REPLACE PROCEDURE SOLVE_LOCKAS V_SQL VARCHAR2(3000); --定义 v_sql 接受抓取锁的sql语句 CUR_LOCK SYS_REFCURSOR; --定义游标变量，循环执行抓取锁的sql语句 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 ); -- V_TYPE VARCHAR2(30), RECORDS_LOCK TP_LOCK; BEGIN 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 SID IN (133,68)' ; -- V_SQL:='SELECT SID,TYPE,ID1,ID2, DECORD(LMODE,0, NONE,1,'NULL' 2, 'ROW SHARE' ,3, 'ROW EXCLUSIVE' 4, 'SHARE' 5, 'SHARE ROW EXCLUSIVE' 6 ,'EXCLUSIVE' ,'HAHA') LOCK_TYPE,REQUEST,CTIME,BLOCK FROM V$LOCK WHERE SID IN (133,9)' ; 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('SID: '||RECORDS_LOCK.V_SID||' is request a lock ,lock_mode is '||RECORDS_LOCK.V_REQUEST||' and being locked '|| RECORDS_LOCK.V_LOCK_TIME);END IF; IF RECORDS_LOCK.V_BLOCK <>0 THEN --抓取发生锁阻塞的会话 DBMS_OUTPUT.put_line('SID: '||RECORDS_LOCK.V_SID||' is make a lock , lock_mode is '||RECORDS_LOCK.V_LMODE); END IF; -- DBMS_OUTPUT.put_line(RECORDS_LOCK.V_SID); END LOOP; CLOSE CUR_LOCK; END SOLVE_LOCK;
转载于:https://www.cnblogs.com/iyoume2008/p/4777040.html
相关资源：JAVA上百实例源码以及开源项目