I have a small procedure compiled on a remote 11g2 XE database and want to call it from my local 11g2 XE using SQL Developer or sqlplus. So I used:
The remote procedure uses some complex DMLs and this takes a while. It happened once that I accidentially pulled the network cable from the adapter at the target computer. The call from my database did not stop and I had to kill the process manually. I tried it another time and it didn't stop either.
It seems to me that the TNS listener doesn't recognize the network failure... Does anyone have a clue what is happening or how I can debug it? Or is network failure recognition maybe a feature turned off in XE?
Thanks and regards,
>> TNS listener doesn't recognize the network failure
Correct, listener is only responsible for passing connection requests to the database instance, not for monitoring sessions.
The pmon process is responsible for monitoring the state of sessions in the database instance. When a session gets disconnected pmon should step in and tidy up, rolling back any uncommitted work.
>> small procedure ... some complex DMLs and this takes a while
So maybe its not so small, in terms of the amount of work that its doing. It will probably take a while to roll back the uncommitted work.