Wednesday, December 29, 2010

Oracle: Reading Table Locks And From Where

SELECT ALL 
   ALL_OBJECTS.OWNER, 
   ALL_OBJECTS.OBJECT_NAME,
   ALL_OBJECTS.OBJECT_TYPE, 
   V$LOCKED_OBJECT.ORACLE_USERNAME,
   V$LOCKED_OBJECT.OS_USER_NAME,
   DECODE(V$LOCKED_OBJECT.LOCKED_MODE,
            0, 'None', 
            1, 'Null', 
            2, 'Row-S (SS)', 
            3, 'Row-X (SX)', 
            4, 'Share', 
            5, 'S/Row-X (SSX)', 
            6, 'Exclusive', 'Unknown') LockMode,
   V$SESSION.MACHINE, 
   V$SESSION.PROGRAM, 
   V$SESSION.LOGON_TIME,
   V$SESSION.TERMINAL, 
   V$SESSION.SID, 
   V$SESSION.SERIAL#
FROM 
   V$LOCKED_OBJECT, ALL_OBJECTS, V$SESSION
WHERE 
  ((ALL_OBJECTS.OBJECT_ID=V$LOCKED_OBJECT.OBJECT_ID)
      AND (V$LOCKED_OBJECT.SESSION_ID=V$SESSION.SID));

No comments:

Post a Comment