Re: Rollback Segment Trouble

David Pape (pape_at_plato.chemietechnik.uni-dortmund.de) wrote:
: 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 haven't told this person why it's bombing at 121. There are limits to the number of extents an object can grow to which are based on the value of db_block_size. A block size of 2048 = max extents of 121. Larger block sizes mean more extents. Adding another rollback segment won't help here because each session uses only a single rollback segment. However, you could create a large rollback segment and then force your transaction to use it. I do agree that you should do your deletion in PL/SQL and commit periodically.

		Michael Leblanc, Support Engineer
                    Derby Associates, International
                    E-Mail: derby_at_teal.csn.org

: 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) |
Received on Fri May 13 1994 - 20:51:27 CEST