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 )

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