Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Session does not terminate when a connection drops while running PLSQL.

Re: Session does not terminate when a connection drops while running PLSQL.

From: jim rowe <jimlorraine_at_hotmail.com>
Date: 5 Jul 2002 02:03:12 -0700
Message-ID: <9a05ce47.0207050103.20722916@posting.google.com>


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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US