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

Jimbo1 <jamestheboarder_at_googlemail.com> wrote in news:c95081f8-d500-
493f-901b-56fa5d2e54b2_at_f24g2000prh.googlegroups.com:

> 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> >