Re: Oracle ORA-1555 Snapshot Too Old error

"E. Fong" <efong_at_val.net> wrote:

>I having a problems getting an ORA-1555 Snapshot too old error while trying
>to load a large set of data into a table. The batch job is run a not
>concurrently with other nightly batch jobs and generally runs an hour long.
> It seems that the rollback segments have been aged out by the end of the
>run. I have tried using a larger rollback segment but hasn't helped.
>

The problem is that you are filling up your log files. For several reasons, ORACLE logs all of your transactions to the log file. When it fills one up, it starts using the next one. If you happen to fill up all of your log files, ORACLE attempts to resuse the first one. If, however, this log file is already being used by an uncomitted transaction, ORACLE has nowhere to write the new transactions and you will get the error you described.

There are a number of things you can try:

1. Increase the size of your log files
2. Add additional log files
3. Modify your batch file so that it commits periodically while running. This can easily be done using a PL/SQL script like this:

DECLARE
  CURSOR example IS SELECT ROWID FROM mytable;; BEGIN
  FOR myrecord IN example LOOP
    UPDATE mytable SET whatever....
     WHERE rowid = myrecord.rowid;
    IF MOD(example%rowcount, 500) = 0 THEN       COMMIT;
    END IF;
  END LOOP;
  COMMIT;
END; the key is the if statement which will commit the transaction to the database every 500 records. You want to be careful with this technique because you are automatically committing your records-you need to make sure your script is working properly before you run it against a production db.

Bert Evans
Bert Evans
bevans_at_esper.com Received on Thu Oct 24 1996 - 00:00:00 CEST