CREATE OR REPLACE PROCEDURE SOLVE_LOCK
AS
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 );
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 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('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');
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;
END LOOP;
CLOSE CUR_LOCK;
END SOLVE_LOCK;
/