Re: Proper location for rollback segments?

All sounds a bit peculiar to me.

Rollback segments should most definitely be housed in their own tablespace, using datafiles created on separate physical devices from the table and index tablespaces -because every piece of DML on a table generates a piece of DML on the index(es) on that table, and both sets of DML must be rollback-able, and hence generates a double-dose of DML on the rollback segment.

If the rollback segments are housed in the same tablespaces as the tables or indexes, you'll have massive i/o contention as all that DML queues up for access to the physical device.

What you might care to do is something like:

select segment_name, tablespace_name from dba_rollback_segs;

...that will show you unambiguously where your rollback segments actually live. It's possible the DBA Studio display is confusing you a bit. See if the output from the SQL statement makes the display make any more sense. If the SQL statement tells you that you genuinely do have rollback segments within the table and/or index tablespaces, the proper thing to do would be to offline them then drop them, and then re-create a whole new bunch of rollbacks in an appropriate tablespace.

Don't forget, if you have to do this, that rollback segments have to be brought online at Instance startup by including them as a comma-separated list in the ROLLBACK_SEGMENTS= section of the init.ora, so if your new segments use different names from the old, you'll have to edit the init.ora before bouncing your Instance.

Incidentally, TEMP segments are entirely different from rollback segments, and a "one-time operation" (I presume you mean something like a one-off bulk data load) would generate rollback in perfectly normal rollback segments. Temporary segments only ever get touched when you are doing reports which use 'order by', 'group by' clauses or do things like union joins (oh -index creation also uses temporary segments).

Regards
HJR <u298614501_at_spawnkill.ip-mobilphone.net> wrote in message news:l.982106435.1084228515_at_widberry.nacse.org...
> I have an RBS tablespace that contains my rollback segments.
> When working in 8i DBA Studio, I notice that in the tablespace
> tree, every tablespace contains its own "Rollback Segments"
> folder. My understanding was that all rollback segments
> should be created in their own tablespace. Is it proper to do
> that, or to create rollback segments within individual
> tablespaces? Do those individual rollback segment folders
> just provide a location for temporary rollback segments, like
> when you create a large temporary segment to handle a one-time
> operation?
>
> Thanks in advance...
>
>
>
>
> --
> Sent by dbkdbk from hotmail part from com
> This is a spam protected message. Please answer with reference header.
> Posted via http://www.usenet-replayer.com/cgi/content/new
Received on Wed Feb 14 2001 - 04:59:58 CST