Re: Rollback segments available?

"Frank Habers" <fhabers_at_inergy.nl> wrote in message news:9rjflt$2rua$1_at_scavenger.euro.net...
> Question: We use ETL-tool Powercenter to load our data warehouse
(Oracle8).
> When we monitor the process, we see that Oracle uses a lot of diskspace
for
> the rollbacksegments. It seems to me very curious, because we have a
> commit-interval of 10.000 records and there are no concurrent queries on
the
> data warehouse.
>
> My question is: When are used-rolllbacksegments again available? Why
doesn't
> Oracle uses the rollbacksegments of an old committed transaction instead
of
> extent the rollbacksegment? Can we force to use old rollbacksegments.
>
> Thanks in advance,
>
> Frank
>
>

Please read the Oracle Concepts manual or search the archives of this newsgroup.
Looks like you know very little about rollbacksegments, and this is not the place to provide a tutorial on them, nor a replacement for RTM.

Just a few little pointers:
Oracle can deal with multiple transactions on one rollbacksegment, also Oracle will release a rollback segment once a commit has completed. Query v$rollstat and v$transaction as to see what is really going on, 'using a lot of diskspace' doesn't tell anything, except you have that tablespace on autoextend which is *bad*
Also do not forget setting optimal clause will shrink the rollback segments, when you think you can't cope with their size.

Also : rollback applies to data *and* indexes. If you already have indexes on those tables, you will generate much more redo, and indexes which are in need of rebuild immediately

Regards,

Sybrand Bakker
Senior Oracle DBA Received on Mon Oct 29 2001 - 06:08:49 CST