CREATE OR REPLACE PROCEDURE SOLVE_LOCKAS V_SQL VARCHAR2(3000); CUR_LOCK SYS_REFCURSOR; TYPE TP_LOCK IS RECORD( V_SID NUMBER, V_TYPE VARCHAR2(10), V_ID1 NUMBER, V_ID2 NUMBER, V_LMODE VARCHAR2(20), V_REQUEST NUMBER, V_LOCK_TIME NUMBER, V_BLOCK NUMBER ); RECORDS_LOCK TP_LOCK; BEGIN V_SQL:='SELECT SID,TYPE,ID1,ID2, LMODE ,REQUEST,CTIME,BLOCK FROM V$LOCK WHERE SID IN (133,9)' ; OPEN CUR_LOCK FOR V_SQL; LOOP FETCH CUR_LOCK INTO RECORDS_LOCK; IF TO_NUMBER(RECORDS_LOCK.V_LMODE) = 0 THEN RECORDS_LOCK.V_LMODE :='NONE'; ELSIF TO_NUMBER(RECORDS_LOCK.V_LMODE) = 1 THEN RECORDS_LOCK.V_LMODE :='NULL'; ELSIF TO_NUMBER(RECORDS_LOCK.V_LMODE) = 2 THEN RECORDS_LOCK.V_LMODE :='ROW SHARE'; ELSIF TO_NUMBER(RECORDS_LOCK.V_LMODE) = 3 THEN RECORDS_LOCK.V_LMODE :='ROW EXCLUSIVE'; ELSIF TO_NUMBER(RECORDS_LOCK.V_LMODE) = 4 THEN RECORDS_LOCK.V_LMODE :='SHARE'; ELSIF TO_NUMBER(RECORDS_LOCK.V_LMODE) = 5 THEN RECORDS_LOCK.V_LMODE :='SHARE ROW EXCLUSIVE'; ELSIF TO_NUMBER(RECORDS_LOCK.V_LMODE) = 6 THEN RECORDS_LOCK.V_LMODE :='EXCLUSIVE'; END IF; EXIT WHEN CUR_LOCK%NOTFOUND;IF RECORDS_LOCK.V_REQUEST <>0 THEN DBMS_OUTPUT.PUT_LINE(RECORDS_LOCK.V_SID||' is request a lock ,lock_mode is '||RECORDS_LOCK.V_LMODE||' and beging locked '|| RECORDS_LOCK.V_LOCK_TIME);END IF; IF RECORDS_LOCK.V_BLOCK <>0 THEN DBMS_OUTPUT.put_line(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/4774307.html
