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

From: Peter Sharman <psharman_at_us.oracle.com>
Date: Thu, 25 Mar 1999 09:38:15 -0800
Message-ID: <36FA7487.CF455A80@us.oracle.com>

Sybrand et al

Let's start off with some clarifications before I asnwer Sybrand's question. When a database is first created using the CREATE DATABASE command, only a single rollback segment is created. This is the SYSTEM rollback segment and it is created in the SYSTEM tablespace. The SYSTEM rollback segment has one basic difference from any other rollback segment, including any other rollback segments that are created in the SYSTEM tablespace. This difference is that the SYSTEM rollback segment can only be used for transactions that occur on objects inside the SYSTEM tablespace. This is done because the main purpose of the SYSTEM rollback segment is to handle rollback for DDL transactions - that is transactions against the data dictionary tables themselves. Making the SYSTEM rollback usable only for the SYSTEM tablespace was simply an easy way to enforce that. It is possible for the SYSTEM rollback segment to be used for non-data dictionary tables, but only if those tables are created inside the SYSTEM tablespace (which is very bad development practice).

OK, so now we have the SYSTEM tablespace with the SYSTEM rollback segment in it. In early releases of Oracle7, and I forget where exactly this changed (7.2?), you had to create a "dummy" rollback segment and bring it online before you created additional tablespaces. In later releases, you could create additional tablespaces without the dummy rollback segment. So what people would do is create the RBS tablespace, then create the additional rollback segments they might need, bring them online (make sure you add them to the init.ora file though) and away you go.

There was only one situation this didn't work in, and that's the one Sybrand mentions - create database followed by full import without the additional tablespace and rollback segment creation steps in the middle. As for 8i, here's a direct cut from the Administrator's manual:

Attention: After installation, you must create at least one rollback segment in the SYSTEM tablespace in addition to the SYSTEM rollback segment before you can create any schema objects.

So in response to Sybrand's question, it looks like it's still the same in 8i. I haven't actually tested it, but I assume the document writers did!

HTH. Pete

Sybrand Bakker wrote:

> 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
>
> ------------------------------------------------------------------------
>
> Sybrand Bakker <postbus_at_sybrandb.demon.nl>
>
> Sybrand Bakker
> <postbus_at_sybrandb.demon.nl>
> Netscape Conference Address
> Netscape Conference DLS Server
> Additional Information:
> Last Name Bakker
> First Name Sybrand
> Version 2.1

--

Regards

Pete

---

Peter Sharman                             Email: psharman_at_us.oracle.com
WISE Course Development Manager           Phone: +1.650.607.0109 (int'l)
Worldwide Internal Services Education            (650)607 0109 (local)

San Francisco

SQL> select standard_disclaimer, witty_remark   2 from company_requirements;

Opinions are mine and do not necessarily reflect those of Oracle Corporation

"Controlling application developers is like herding cats." Kevin Loney, ORACLE DBA Handbook
"Oh no it's not! It's much harder than that!" Bruce Pihlamae, long term ORACLE DBA

---

Received on Thu Mar 25 1999 - 11:38:15 CST