Re: "Snapshot too Old" exception when writing to a Global Temp Table.
Date: Thu, 29 May 2008 01:26:40 GMT
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;
> 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 ..
> INSERT INTO global_temp_table
> VALUES t_validated_recs_array(idx);
> 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:
> 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
> Does anybody here have any opinions or help/advice to offer?
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