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 )

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