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%';