Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Usenet -> c.d.o.server -> Re: Adding rollback

Re: Adding rollback

From: Howard J. Rogers <>
Date: Tue, 17 Aug 2004 09:15:11 +1000
Message-ID: <4121401e$0$16377$>

bw wrote:

> I have inherited a 9.2.0 database where the only rollback segments
> that exist are what was created for SYSTEM. Can I get away with
> adding the needed rollback segments (RBS01... etc.) to the init.ora,
> creating them in the existing RBS tablespace? Once this has been
> completed do I then bounce the instance so they are recognized via the
> init.ora?

I presume you've checked the truth of your first sentence by selecting from dba_rollback_segs? If you really only see one row in that view, describing the SYSTEM rollback segment, then yes, you need to think how to add in extra rollback segments.

Except (1) you can get them added in automatically in 9i and (2) you probably have an spfile and not an init.ora, so no simple text editing is allowed.

In 9i, the concept of 'automatic undo' was invented. It simply means Oracle itself creates (and destroys) rollback segments on an as-needed basis, automatically and transparently. Marketing (and therefore everyone else) now calls these automatically created rollback segments "undo segments", but they're physically the same thing.

If you wish to use automatic undo, you need to create an undo tablespace, and then set an initialisation parameter to say you've got one. That would be:

create undo tablespace UNDOTBS1 datafile '/wherever/whatever.dbf' size Xm;

and then:

alter system set undo_tablespace='UNDOTBS1' scope=spfile; AND alter system set undo_management='AUTO' scope=spfile;

And then bounce your instance.

You only need those alter system statements if you are indeed using an spfile. Do a show parameter spfile to find out (if it displays a value, you are. If it displays a null value, you're using an init.ora, and you can just add UNDO_TABLESPACE=UNDOTBS1 and UNDO_MANAGEMENT=AUTO into your init.ora using a text editor).

After that, it's all automatically taken care of. You just need to make sure the undo tablespace you've created is big enough.

If you really want to use old-fashioned, manual rollback segments, then you can do so if you're mad enough, and the procedure would be much as you describe it in your original post. Just make sure undo_management=MANUAL in the spfile or the init.ora.

HJR Received on Mon Aug 16 2004 - 18:15:11 CDT

Original text of this message