Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Session does not terminate when a connection drops while running PLSQL.
Thanks very much for your comments. The main option seems to be to
create a background job to run the procedure and allow the transaction
to return quickly. Hopefully this will allow the session to terminate
normally if there is a client failure.
Here is a little more information to clarify some of your questions.
The PL/SQL procedure writes a set of data files using the UTL_FILE package. The files can be many 100s of MBs and contain data from millions of rows, so i don't think its possible to tune it to run in a few seconds.
The system is a batch data-processing system, so there are no human users sitting waiting on the transaction to complete. The amount of time the transaction takes to return is not a critical issue.
When the client fails, the session hangs. The pl/sql procedure never completes. The file it was writing stays in a locked state, and locks which it has on rows are not released, which leads me to think that the database is not cleaning up correctly. (Note that the procedure doesn't do any explicit locking. Any locks are implicitly done by oracle.)
Thanks again,
Jim.
Received on Fri Jul 05 2002 - 04:03:12 CDT