13

suddenly my update queries are not executing . i can make select queries but when i try to update records the database hangs infinitly. i tried even from sql plus and nothing happens.

Adriano Carneiro
  • 57,693
  • 12
  • 90
  • 123
erni313
  • 193
  • 1
  • 2
  • 10
  • 1
    Please, can you be more explicit? There is no way we can *guess* what's going on on your system. What do you know? What have you checked? How does your system work, how many users, etc...? What does the update query look like? What kind of DML is running against the same table at the same time? – Lukas Eder Sep 21 '11 at 14:49
  • Can you log off and on again OK? Are you in ARCHIVELOG mode? Do you have uncommitted changes in another session locing your current update? A bit more information would be handy.... – Ollie Sep 21 '11 at 14:53

2 Answers2

38

Most likely you have another open uncommitted transaction for the same set of records, so they are locked for that transaction.

And, most likely, you locked them, running the same UPDATE in another transaction.

Just Commit/rollback your transactions, you should be fine.

Adriano Carneiro
  • 57,693
  • 12
  • 90
  • 123
  • 3
    Now **that** answer is what I call an educated guess ;-) – Lukas Eder Sep 21 '11 at 15:00
  • 5
    Yes. Yes, it is. When you've been around for sometime and you coach beginners, you get that *the-force-is-strong-in-me* kinda vibe and you know their problems before they can tell you everything. :) But I'm not always right, of course. – Adriano Carneiro Sep 21 '11 at 15:01
  • I know that vibe. It goes well with "speak no further and observe" :) – Lukas Eder Sep 21 '11 at 15:07
  • 1
    Five years later... you saved a me a huge amount of time. I wrote a Python script with a query in it and couldn't figure out why it would hang on execution, but after reading this I realized I didn't commit after testing the query in SQL Developer. Thanks! – McGlothlin Dec 16 '16 at 15:20
  • 1
    Ahh thank you! This just saved me as well! I was testing queries in SQL Developer and thought I had committed everything, but something was still uncommitted. Once I disconnected with SQL Developer it asked about committing changes, and after disconnect my python script worked :) – sofly Mar 30 '17 at 17:33
21

This query will show you who is blocking your update. Execute the update that hangs, then in another session run this:

select s1.username || '@' || s1.machine ||
  ' ( SID=' || s1.sid || ' )  is blocking '
  || s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status
  from v$lock l1 join v$lock l2 on (l1.id1 = l2.id1 and l2.id2 = l2.id2) 
                 JOIN v$session s1 ON (s1.sid = l1.sid)
                 JOIN v$session s2 ON (s2.sid = l2.sid)
  WHERE l1.BLOCK=1 and l2.request > 0;

EDIT:

To properly attribute this, it looks like I cribbed this a while back from ORAFAQ.

DCookie
  • 42,630
  • 11
  • 83
  • 92