SQL QUERY TO FIND LOCKED OBJECTS BY USER SESSIONS

If you want to find the session which access an objects with row wide lock or table locks you can use this query

col ORACLE_USERNAME format a15
col OS_USER_NAME format a15
col OBJECT_NAME format a15
col NAME format a15
col TYPE format a15
col PROCESS format 999999
col MODE_HELD format a15
col MODE_REQUESTED format a15
select s.sid,s.serial#,do.OBJECT_NAME,lo.ORACLE_USERNAME,lo.PROCESS,lo.LOCKED_MODE,ddl.OWNER,ddl.TYPE,ddl.MODE_HELD,ddl.MODE_REQUESTED,s.event
from dba_ddl_locks ddl
inner join v$locked_object lo on ddl.SESSION_ID=lo.SESSION_ID
inner join dba_objects do on lo.object_id=do.object_id
inner join v$session s on lo.SESSION_ID = s.sid
where object_name='TABX';

Use below query to find the SQL query which lock the table

select distinct k.kglnaobj from x$kgllk k
inner join v$session s on k.kgllkuse=s.saddr
where s.sid in (33,64) and
k.kglnaobj like '%insert%' or k.kglnaobj like '%delete%' or k.kglnaobj like '%update%';

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