抓取锁的sql语句-第一次修改 - Oracle-fans - 博客园

CREATE OR REPLACE PROCEDURE SOLVE_LOCK

AS

 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;