Re: alter rollback segment

"Michiel" <cube_at_REMOVE.dds.nl> wrote in message news:3af3108a$0$27218_at_reader4...
> Hi,
>
> I have a question about altering rollback segment (typically RBS01.DBF),
> Info: oracle info: 8.1.6r2 on Linux (SuSe 6.4)
>
> Point is: after creating the 'create scripts' for a new database (and I
 ran
> them), the default rollback segment is about 500Mb, which is much too
 large
> in my opinion (although I'm not a DBA, just a developer).

Careful. Do you mean my rollback segment is 500Mb, or my rollback segment tablespace DATAFILE is 500Mb? I think you mean the latter.

A quick way to correct that would be to simply create a new rollback segment tablespace of an appropriate size, and then drop the old 500Mb one. I'd suggest you open the database in restricted session mode (since you are poking around with segments and tablespaces, the database must be fully open, but you don't want anyone actually using the thing for real work).

Then: create tablespace rbs2 datafile 'c:\blah\rbs02.dbf' size 100M (or whatever you want) autoextend on next 10M maxsize 250M default storage (initial x M next x M minextents 6); (Pick a size for the extents that works for you).

That creates you a new tablespace. Now: create rollbacksegment rbs99 tablespace rbs2 (and repeat for as many rollback segments as you want).

Edit your init.ora file so that the newly-created rollback segments are brought online when you bounce the Instance, and remove all reference to the ones that exist in the old tablespace (you'll be editing the "rollback_segments=" parameter).

Now bounce your Instance.

Finally, drop tablespace rbs01 including contents. (Should work if you've done the previous step correctly, because none of the old rollback segments will be online at this stage).

That will get rid of the old tablespace and the rollback segments it contained -but it won't get rid of the datafile off the disk, so you need to drop out to the O/S and do that, too.

That's the short form, anyway: creation of rollback segments is a bit trickier than that, and sizing them appropriately is important. And I'd also suggest you use locally managed tablespace to house them, rather than the dictionary-managed version I've given you above. But whatever refinements you care to add, the principles are as outlined above.

Regards
HJR
>My older databases
> still have these huge rollback '.dbf' files, so I want to change them.
> Perhaps someone here also has a create script (with SID for argument and
> perhaps some other parameters) which create a 'good' database.
>
> Question:
> How do I set a new size (resize) for a rollback segment (when my database
 is
> stopped).
>
> although I have the book 'Oracle Development Unleahsed', I was not able to
> find the proper statements, and the 'dbassist' command on Xwindows results
> in an ordinnary core dump (perhaps common in 8.1.6R2) when issuing 'change
> database'. It must have to do with:
>
> "
> alter rollback segment <name> offline;
> blabla;
> "
>
>
> thnx in advance,
> Michiel
>
>
>
>
>
Received on Fri May 04 2001 - 21:09:23 CDT