1 person found this helpful
at the time of block, you can query dba_lock,dba_dml_locks,dba_ddl_locks, dba_blockers, dba_waiters, v$lock dictionary views to extract the details.
Thanks for reply.
We could code this into the package for next time. Is there any way to identify this now since lock gone - any way to query this data historically.
You can't get it from history, when you get the lock again then query the table mentioned above. Also, to check any kind of session blocking issue use that query.
select b.session_id ,a.SERIAL#, a.username "Blocker Details"
from v$session a,dba_lock b
where b.session_id = a.sid
and b.blocking_others = 'Blocking';