Job randomly hangs reading db file

From: Rob Edgar <robedgar_at_hkstar.com>
Date: Sat, 10 Mar 2001 13:24:03 +0800
Message-ID: <98cdp3$nul14@imsp212.netvigator.com>

I have a pl/sql procedure called from a job, the procedure loops through a table and calls a java function that checks the status of a url.

The java part seem to be no problem and anyway I catch all exceptions inside the java routine.

However the procedure just hangs randomly, example the last run died after 40mins and had processed 2500 url's.

There is no exception thrown, nothing in the trace files, and the job shows as running and nothing except a "shutdown abort" will stop the job.

The only thing I can see is from V_$SESSION_WAIT which shows the following entry for this session
db file sequential read:10
block#:58
blocks:1
WAITED KNOWN TIME and the time it is waiting is continuously incrementing

Whenever it hangs I get the same thing although the values are different each time ie it dies at differnet point in reading the file.

The procedure is pretty simple
procedure CHECK_LINKS
is
  iStatus pls_integer;
begin
  for rs in (select url from application_notes where url like 'http://%' group by url )loop

    begin

        iStatus := checkLink(rs.url);
        insert into url_verification_log(url, statusno)
        values(rs.url, iStatus);
        commit;
    exception when others then
      rollback;

    end;
  end loop;
exception when others then
  null;
end;

Does anyone know what the problem might be

Tks
Rob Received on Fri Mar 09 2001 - 23:24:03 CST