Re: Rollback Segments Question

From: Alex Filonov <afilonov_at_yahoo.com>
Date: 9 Apr 2003 08:54:25 -0700
Message-ID: <336da121.0304090754.7eb4ee76@posting.google.com>

Tim X <timx_at_spamto.devnul.com> wrote in message news:<87of3hs8vj.fsf_at_tiger.rapttech.com.au>...
> I have a couple of questions relating to rollback segments I am hoping
> to get some clarification on from this group. The database is 8.1.7 on
> both True64 and Linux boxes.
>
> 1. According to the 8i documentation, you need to create an additional
> rollback segment when creating a new database. I've also seen this
> mentioned in a couple of books with the rationale that this is
> needed in order to create additional tablespaces and rollback
> segments in the db creation script. My problem with this is that I
> have seen databases created and have created databases where I did
> not need to create an additional rollback segment in the system
> tablespace in order to create tablespaces or rollback segments. So,
> what is the story here. Is this something left over from earlier
> Oracle versions and is not a requirement in newer versions (and
> just has not been removed from the documentation) or is there
> something subtle I'm not aware of?
>
> 2. According to the Oracle 8i documentation, if you do not include
> your rollback segments in the rollback_segment parameter in the
> init.ora file, your rollback segments will not be brought back
> on-line when you bring your database up and will need to explicitly
> be brought on-line with a alter statement. THis is in addition to
> the rollback_segments other role relating to public/private rbs and
> parallel servers. I have done some experiments with 8i and this
> does not seem to be the case. If I don't include the RBS in the
> parameter file, they still come up on-line when the db is
> started. Again, is this just old documentation left from an earlier
> Oracle version, a documentation error or am I missing something?
>
> 3. Can you use locally managed tablespaces for rollback segments?
> According to the Oracle 8i documentation, you cannot use the local
> automatic option for LMT if you want to use the tablespace for
> rollback segments. to me, this implies you could do it if you use
> the "local uniform" option instead. However, when I attempt to do
> this, the rollback segment creation fails with the error
>
> ERROR at line 1:
> ORA-01552: cannot use system rollback segment for non-system tablespace 'RBS'
>
> where RBS is my LMT and RBS01 is the name I am giving to the rollback
> segment. If I drop the tablespace and re-create it as a DMT, then
> everything is fine.
>

I didn't try it myself yet, so it might not work. Try to create a PUBLIC
or PRIVATE rollback segment in some DMT tablespace (not SYSTEM), bring in online, and then try to create rollback segments in LMT tablespace. ORA-1552 actually says that SYSTEM rollback segment is used for operation
it can't be used for. The remaining question is if PUBLIC or PRIVATE rollback segment can be used to create other rollback segments. If it doesn't work, then you need SYSTEM rollback segment for this operation and LMT can't be used for rollback segments. Here's the extract from Administration Guide (9.2):

When a database is created, a single rollback segment named SYSTEM is created in the SYSTEM tablespace. This rollback segment is used in special ways by the Oracle database server, and is not intended for general use. Before you write to objects created in non-SYSTEM tablespaces, you must create and bring online at least one additional rollback segment in a non-SYSTEM tablespace. Note:

When you are initially creating the database, and in order to create additional tablespaces and rollback segments, you must create a second rollback segment in the SYSTEM tablespace. Once these additional rollback segments are created, you should activate the new rollback segments and make the second rollback segment unavailable.

> Any info or pointers greatly appreciated.
>
> TIA
>
> Tim
Received on Wed Apr 09 2003 - 10:54:25 CDT