SQL QUERY TO FIND LOCKS IN ORACLE

SQL QUERY TO FIND LOCKS IN ORACLE

Use the below query to immediately find locked objects and blocking sessions to solve performance impact on oracle database

col sid format 999999
col username format a15
col sql_id format a15
col event format a15
col lmode format 99 justify center
col block format 999999
col object_id format 999999
col sql_text format a20
col BLKINST format 99
col blocking_session format 99
select x.sid,x.username,x.sql_id,x.BLOCKING_INSTANCE as BLKINST,x.blocking_session,x.event,y.lmode,z.object_id,a.sql_text
from v$session x
inner join v$lock y on x.sid=y.sid
inner join v$locked_object z on y.sid = z.session_id
inner join v$sql a on x.sql_id = a.sql_id
where x.status='ACTIVE' and x.blocking_session is NOT NULL;