beverly@ai.cs.wisc.edu (Beverly Seavey) (11/20/90)
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
scott@prism.gatech.EDU (Scott Holt) (11/20/90)
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
sharnyo@chez.oracle.com (Andi Harnyo) (11/22/90)
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 You are trying to create an object on non-system tablespace using the system rollback segment. The DBA guide pg 16-5 specifically mentions that you need to create an additional rollback segment in the system tablespace first. Don't forget to restart the db after adding the rbs. Regards, Andi
kbittner@oracle.uucp (Kurt Bittner) (12/03/90)
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 Create a rollback segment in the system tablespace (I usually do PUBLIC ones, but that's somewhat a matter of preference). Shut down the DB to make sure that the rollback segment is used, then you'll be fine. Kurt Bittner Consultant - Oracle Chicago kbittner@oracle.com "My opinions are purely my own and do not reflect those of Oracle Corporation."