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

http://asktom.oracle.com & do keyword search on ORA-01555

>I know for a fact that nobody else is connected to the database I'm
using at the moment.

Then as Pogo once said, "Wehave met the enemy, & they is us." Received on Wed May 28 2008 - 20:26:40 CDT