Oracle UPDATEs to Certain Table Hang Indefinitely

Question

I can update some tables just fine, but a certain table I have cannot be updated by either my ASP.NET application or SQLPLUS. Both hang indefinitely. I have to forcibly quit SQLPLUS. I have update statments both in standard OracleCommand objects running a:

cmd.ExecuteNonQuery();

but I also have asp:SqlDataSources bound to a GridView. Nothing seems to work.

There's a similar question here: http://stackoverflow.com/questions/1172378/oracle-update-hangs. I have the rough idea that something has to have a lock of some sort on this table, but I don't know how to remove it or even find it for that matter. Any help would be greatly appreciated.

I'm running Oracle 9i EE 9.2.

Answer 1

Chances are that another session has uncommited work (ie UPDATEs or DELETEs)

You can find which session(s) this might be with...

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

EDIT:

Chances are you've got a rouge bit of code somewhere with a missing COMMIT.

Check the offending session id in V$SESSION and see if you can figure out which code it could be.

To help you find the rouge code (assuming that's what it is), you can find the last SQL command ran by a session by using...

select *
from   v_$sqltext, v_$session
where  v_$session.prev_hash_value = v_$sqltext.hash_value
and    [some filter on v_$session]