Re: Rollback Segment Trouble

carmen_at_verdi.iisd.sra.com (Carmen Iannacone) writes:

>Hi,
 

> I've gotten the following errors during large deletes
> in SQL*Plus. I've checked the Server Codes and Messages
> manual, but I'm still somewhat unsure as to how to proceed.
 

>ORA-01562: failed to extend rollback segment (id = 1)
>ORA-01628: max # extents (121) reached for rollback segment MY_ROLLBACK
 

> Notably, why is "max # extents" 121? Should I be doing
> some house keeping to prevent this? I haven't gotten
> through the FAQ, so if you could direct me to a portion
> of it which is applicable, I'd appreciate it.

you could add another rollback segment, (don't forget to write the name of the new rollback segment in your init.ora file) OR (my suggestion, IF the table is REALLY big) you could write a PL/SQL script to delete a max. of, say 500 rows, after which you commit. The delete statement (in the PL/SQL file) would look like:

DECLARE
        num_deleted integer;

BEGIN

	num_deleted := 1;
	total_deleted := 0;
	WHILE num_deleted > 0 LOOP
		DELETE from my_tablename WHERE (my where clause) AND ROWNUM < 500;
		num_deleted := SQL%ROWCOUNT;
		COMMIT;
	END LOOP;

END; /

It's definitely better to do deletes in this manner if the delete will remove more than a couple thousand rows. (IMHO)

--
David Pape                               | "The people who own the country    |
pape_at_plato.Chemietechnik.Uni-Dortmund.DE |  ought to govern it"               |
Dortmund, Germany                        |  - John Jay                        |
tel: 49 231 7552682                      |  (signer of the U.S. Constitution) |