Re: Why two rollback segments in system tablespace to create other tablespaces?

The system rollback segment, ie the rollback segment named SYSTEM in the tablespace SYSTEM is created automatically and is used for DDL transactions ONLY. This means initially you have NO ordinary rollback segments. Try creating an empty database, apply a full database export to it, it will NOT work, as the tablespaces will be created before any non-system rollback segments (Question to Thomas Kyte and/or Peter Sharman: is this resolved in Oracle 8i?). You need to create an additional ordinary rollback segment in this tablespace system INITIALLY. This should be a PRIVATE rollback segment, and you can put it offline in normal circumstances, after tablespace creation. You should however retain it, if anything happens to your tablespaces (especially the tablespace with the rollback segments in it), in order to be capable of enabling it for maintenance operations. I always have a special init.ora file, with that rollback segment only. This enables me to have a fully operational database for DBA operations on the other tablespaces.

Hth,

Sybrand Bakker, Oracle DBA

Cheong wrote:

> Surely someone could answer this question:
>
> Oracle7 Server SQL Reference says,
> "(The prerequisite to create tablespace is that) ... the SYSTEM tablespace
> must contain at least two rollback segments including the SYSTEM rollback
> segment.
>
> I tried creating other tablespaces with only one SYSTEM rollback segment. It
> works.
>
> I also tried creating other tablespaces with one SYSTEM rollback segment,
> one TMP rollback segment on SYSTEM tablespace. It works also.
>
> So why must there be "at least two rollback segments including the SYSTEM
> rollback segment" in order to create other non-system tablespaces?
>
> Cheong

Received on Thu Mar 25 1999 - 00:18:16 CST