Xref: utzoo comp.sys.sgi:6906 comp.databases:7891
Path: utzoo!utgpu!news-server.csri.toronto.edu!cs.utexas.edu!wuarchive!zaphod.mps.ohio-state.edu!ncar!gatech!prism!scott
From: scott@prism.gatech.EDU (Scott Holt)
Newsgroups: comp.sys.sgi,comp.databases
Subject: Re: need help with ORACLE V6.0.27.7.1 on silicon graphics,rollback segs
Message-ID: <17326@hydra.gatech.EDU>
Date: 20 Nov 90 00:20:18 GMT
References: <11762@spool.cs.wisc.edu>
Followup-To: comp.sys.sgi
Organization: Georgia Institute of Technology
Lines: 95

In article <11762@spool.cs.wisc.edu> beverly@ai.cs.wisc.edu (Beverly Seavey) writes:
>I am trying to add a tablespace to our database ( we have been getting
>by with just SYSTEM up until now.)
>
>I was able to create a tablespace using the stamt:
>
>	 CREATE TABLESPACE TS_DAT_2
>	 DATAFILE 'TS_FILE_2.DAT' SIZE 10M;
>
>Then, because my understanding is that I need a non-system rollback
>segment for a non-system tablespace, I tried to create a rollback
>segment for my new tablespace:
>
>	 CREATE ROLLBACK SEGMENT RBS_DAT_2
>	 TABLESPACE TS_DAT_2;
>
>ORACLE gives me the following error msg:
>
> ORA-01552
>   cannot use system rollback segment for non-system tables.
>
> The SQL Error msgs manual advises : Create one or more private or
> public rollback segments, shut down, then start up again. You may need to
> modify the INIT.ORA parameter ROLLBACK_SEGMENTS_REQUIRED to acquire one 
> of the  new private rollback segments.
>
> This all seems circular: how can I create a rollback segment if oracle
> won't let me? The only INIT.ORA parameter that looked remotely
> relevant is DC_TABLESPACES, which has a default value of 25.
>
> I am using ORACLE  RDBMS V6.0.27.7.1


I belive it is telling you that it cannot use the SYSTEM rollback
segment for tables which are not part of the system tablespace. You
can, however, create a second rollback segement in the system
table space.

We have a number of databases created with a second table space, 
here is how we created them:

startup nomount pfile=initSID_start.ora
create database ....

# install data dictionary views 

   ...

create rollback segment bootstrap_rollback segment;

shutdown
startup pfile=initSID_step2.ora
connect sys ...
create tablespace ts1 ...
create rollback segment r1 tablespace ts1 ...
  
...

shutdown
startup pfile=?/dbs/initSID.ora
drop rollback segment bootstrap_rollback_segment;
exit


This procedure needs three different init.ora files for each of the
stages:

initSID_start.ora  is plain, it doesn't specify any rollback segments,
this defaulting to the single system rollback segement.

initSID_step2.ora starts the database using the bootstrap segment
and has the following line in it:

rollback_segments = (bootstrap_rollback_segment)

finally, the normal startup file is initSID.ora and has the 
newly defined rollback segments specified with a statment like

rollback_segments = (r1 ... )

The bootstrap segment is no longer needed once the build is complete.

Much of this is adapted from appendices of the System Installation
and Users Guide and SRB. In our case its a Sequent, but its the
same on Suns and probably any Oracle system.


Hope this helps.

- Scott
-- 
This is my signature. There are many like it, but this one is mine.
Scott Holt                 		Internet: scott@prism.gatech.edu
Georgia Tech 				UUCP: ..!gatech!prism!scott
Office of Information Technology, Technical Services