Oracle Select Query Over Database Link Suddenly Hangs Endlessly
We learned that in rare circumstances a distributed transaction fails and leaves some kind of "half lock" on one side of the distributed transaction. Because the other side was gone it never resolved. At least one of the common tools that looks up locks didn't see these locks. However, we were able to find them (I think we queried v$lock, I don't remember, it's been over a year since it last happened). To fix it we determined what process was running that session and our DBA killed the OS process with a kill -9. It was a rare problem and I haven't seen it in about 18 months, so it's possible that it is only something that happens on earlier versions of Oracle . (However, it usually happened only once or twice a year at most, so it could be we've just been lucky.)