Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Lock + Insert..

425831Aug 23 2006 — edited Aug 27 2006
Hi,

I have a job in my Oracle Forms 10g/ Oracle DB 10g application that runs every minute to identify if there is a Blocking Lock in the database.

I have been getting an INSERT statement as the cause of the blocking lock every day! How can an insert statement create a blocking?
I have not used LOCK TABLE tab1 IN EXCLUSIVE MODE;

The query I use in my job to get the Blocking SQL text is:

SELECT vs.SID, vs.username, vs.logon_time, vs.machine, vs.sql_hash_value,
vsql.piece, vsql.sql_text, vs.prev_hash_value
FROM v$session vs, v$sqltext vsql
WHERE SID IN (SELECT LPAD (' ', DECODE (vl.request, 0, 0, 1)) || SID
FROM v$lock vl
WHERE id1 IN (SELECT id1
FROM v$lock vl2
WHERE vl2.lmode = 0))
AND vs.sql_hash_value = vsql.hash_value
ORDER BY 2, 5, 6;

Please advice

Warm Regards
Sajan
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Sep 24 2006
Added on Aug 23 2006
5 comments
674 views