Oracle数据库对象查询是否被锁定及解锁

232312

Oracle数据库对象是否被锁定查询SQL及解锁过程SQL详见下面：
--查询当前用户下被锁的对象
SELECT B.OBJECT_NAME, B.OWNER, C.SID,C.SERIAL#,C.OSUSER, C.MACHINE, C.TERMINAL
  FROM V$LOCKED_OBJECT A, DBA_OBJECTS B, V$SESSION C
WHERE A.OBJECT_ID = B.OBJECT_ID
   AND A.SESSION_ID = C.SID
   AND c.STATUS='INACTIVE'
   AND C.TYPE='USER'
   ORDER BY 1;

--解锁语句  
DECLARE
  V_SQL VARCHAR2(500);
  CURSOR C_SESSION IS
     SELECT B.OBJECT_NAME,
        B.OWNER,
        C.SID AS SESSION_ID,
        C.SERIAL# AS SERIAL#,
        C.OSUSER,
        C.MACHINE,
        C.TERMINAL
     FROM V$LOCKED_OBJECT A, DBA_OBJECTS B, V$SESSION C
     WHERE A.OBJECT_ID = B.OBJECT_ID AND A.SESSION_ID = C.SID
       AND C.STATUS = 'INACTIVE' AND C.TYPE = 'USER'
     ORDER BY 1;
BEGIN
  FOR GET_SESSION IN C_SESSION
    LOOP
      BEGIN
        V_SQL:='ALTER SYSTEM KILL SESSION '''||GET_SESSION.SESSION_ID||','||GET_SESSION.SERIAL#||''' IMMEDIATE';
        EXECUTE IMMEDIATE V_SQL;
      EXCEPTION
        WHEN OTHERS THEN
          NULL;
      END;
    END LOOP;
END;
/