How to find the SQL that locks resources?
Hi, All,
On our production db, we have some applications locks some table for a long time, we want to find out the SQL, I join the following table
select a.sid as sid,
a.serial# as serial_num,
b.sql_text sql
v$session a,
v$sqltext b,
dba_lock c
where a.sid = c.session_id
and a.username = 'XXXX'
and ((a.sql_address = b.address
and a.sql_hash_value = b.hash_value)
or (a.prev_sql_addr = b.address
and a.prev_hash_value = b.hash_value))
But the sql_text is not the SQL that locks the table, instead, it is the last SQL from that sid/serial#.
Is there a way to find the SQL that hold the locks?
Thanks