Re: Rollback segments errors

In article <8t0uo1$qkd$1_at_muguet.sncf.fr>,   "Pascal LAMBERT" <pascal.lambert_at_sncf.fr> wrote:
> Hi,
>
> if you got to delete the entire table use the SQL command TRUNCATE
 TABLE who
> do not use rollback segment.
>
> Else, use a PL/SQL block who cycles DELETE like this :
>
> BEGIN
> LOOP
> DELETE FROM TABLE WHERE ROWNUM < n; (where n = 1000, 2000
 or
> more)
> EXIT WHEN SQL%NOTFOUND;
> COMMIT;
> END LOOP;
> COMMIT;
> END;
>
> Regards,
>
> PASCAL
>
> <barko_at_attglobal.net> a écrit dans le message :
> 39f264fe_3_at_news1.prserv.net...
> > I have been getting the following two errors when attempting to
 delete
 rows
> > from a fairly large table.
> >
> > ORA-01623
> > ORA-01562
> >
> > Both mention that the maximum number of extents (121) has been
 reached
> > during rollback. To get around this I have to drop the table and
 reload
 it.
> >
> > Any suggestions?
> >
> > Chris
> >

Pascal gave you good advise about using truncate if you want to delete all the rows, but if not besides using pl/sql to perform the deletes like he suggested you need to look at the possibility that you need to drop and recreate your rbs segments with a larger extent size. Your rbs extent size X maxextents needs to consume enough space to support about 120% of the largest transaction you want to be able to support. The other option is to create a special large rbs segment to used via the 'set transaction use rollback x;' command for large jobs.

--
Mark D. Powell  -- The only advice that counts is the advice that
 you follow so follow your own advice --


Sent via Deja.com http://www.deja.com/
Before you buy.