Re: Rollback segments (Oracle 6)

In article <2gsl9o$b0e_at_joanna.wes.army.mil>, Ernest Boswell <u4imcehb_at_apollo.wes.army.mil> wrote:
>Can someone shed some light on how Oracle 6 handles rollback segments.
 

>Our DBA (commonly known as Don't Bother to Ask) tells me that once a rollback
>segment is used, it isn't freed again until the database is shutdown and
>restarted.

Not true. In Oracle 6, when a rollback segment grabs a new extent, it is not released until the rollback segment is dropped. Stopping and re-starting the database can only terminate users and rollback any transactions that may have been in progress.

The problem is that if you run a large transaction, this will force a rollback segment out to a large size. Next time you run it, it will probably run in a different rollback segment. This will fail to extend if your other rollback segment(s) have filled up the tablespace and give you an ORA-1547. To get around this (in 6.0.33 and later) use "set transaction use rollback segment .."

>I'm working with a database about 300M in size,
>primary table having _at_800,000 rows. This is a scientific database (water-
>quality) in the building process, so there are frequent database-wide updates.
>The rollback space is about 250 M, but I get rollback errors even when I'm
>operating against only a subset of the database. Any help/ideas/advice?
>

I suspect you have low volume transactions and large ones. If this is true, I would suggest you drop all but 1 or 2 rollback segments and pre-extend them to fill the rollback tablespace. i.e. 250 meg, 2 rollbacks = 124 meg each. If your transactions require more than this then you should look at your transactions. Use more frequent commits or break your updates up into smaller sections.

Hope it helps,
Andrew.

-- 
--------------------------------------*---------------------------------------
N. Andrew May.                        | Database Administrator
Coles-Myer Ltd                        | President VOUG
53 Hoddle Street, Collingwood,        | Tel: +61 3 483 7389.  Fax: 483 7381