"Snapshot too Old" exception when writing to a Global Temp Table.

Hello People,

I'm currently experiencing a perplexing problem with the "ORA-01555: Snapshot too Old" Exception when writing to a Global Temporary Table on an Oracle 9i Release 2 database.

The Temp Table concerned has been created with the ON COMMIT PRESERVE ROWS specification.

I have a PL/SQL Procedure that is performing data validation on the rows in this Temp Table. The rows that pass validation are stored in memory in an associative array.

When validation is complete and I have my array of validated temp table rows/records, I perofrm the following statement:

   DELETE FROM global_temp_table
    WHERE client = :p_current_client;

   COMMIT; That works fine. On the next step, I BULK-Bind INSERT the associative array of validated temp table records:

   FORALL idx IN t_validated_recs_array.FIRST .. t_validated_recs_array.LAST

        INSERT INTO global_temp_table
               VALUES t_validated_recs_array(idx);

   COMMIT; At this point, I get the "ORA-01555: Snapshot too old" exception.

I cannot understand why I'm getting this exception, as the only cursor I opened has been closed and I know for a fact that nobody else is connected to the database I'm using at the moment.

Is this a result of delayed block cleanout?

I've taken a look at the following link about this particular error, but nothing there seems to apply to my situation:

http://www.fors.com/orasupp//rdbms/dba/40689_1.HTM

Unfortunately, I don't have the necessary privs to be able to view the rollback segment concerned. All I know is that we just have a single rollback segment (the first time I've ever encountered this; all other sites I've worked at have had multiple rollback segments), and I've been told that it's "big". Personally, I'm wondering if it's "big enough", or if we should create at least one additional rollback segment.

Does anybody here have any opinions or help/advice to offer?

Cheers.

James Received on Wed May 28 2008 - 19:54:40 CDT