Tutorials

SELECT OBJECT_NAME, SID, OSUSER, SCHEMANAME, RECURSIVE, LAST_CALL_ET/(60) MINUTES,
(case type when 'TM' then 'DML/TABLE LOCK'
when 'TX' then 'TRANSACTION LOCK'
when 'MR' then 'MEDIA RECOVERY'
when 'ST' then 'DISK SPACE TRANSACTION'
when 'UL' then 'USER SUPPLIED'
else 'LOCKED'
end) LOCK_TYPE,
decode(lmode,0,'NONE',1,'NULL',2,'ROW S',3,'ROW X',4,'TABLE S',5,'SHARED ROW X',6,'TABLE X',NULL) LOCK_MODE,
decode(REQUEST,0,'NONE',1,'NULL',2,'ROW S',3,'ROW X',4,'TABLE S',5,'SHARED ROW X',6,'TABLE X',NULL) REQUEST,
CTIME/(60) LOCKED_SINCE_MIN,SQL_TEXT
FROM V$TRANSACTION JOIN V$SESSION ON SADDR = SES_ADDR JOIN V$LOCK USING (SID)
JOIN V$SQLAREA ON HASH_VALUE = SQL_HASH_VALUE AND SQL_ADDRESS = ADDRESS
JOIN V$LOCKED_OBJECT ON SID=SESSION_ID
JOIN DBA_OBJECTS USING (OBJECT_ID)