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;

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s