Re: to create a non-SYSTEM tablespace

i seem to recall my database creation scripts creating a rollback segment R0 in the system tablespace, and then later, after the roll back segments R01, R02, R03, etc were created in the RBS tablespaces, taking the R0 rollback segment offline.

the R0 rollback segment still exists, still in the system tablespace, and still offline. in the unlikely event i ever need it again...

"Howard J. Rogers" <howardjr_at_www.com> wrote in message news:3b116818_at_news.iprimus.com.au...
> Well, I'll stick my neck out -before 8i, the courses ran on 8.0.5, and all
> the students used to get (as a matter of course design, I think) 'unable
 to
> use system rollback segment for updates in non-system tablespace' -and
 that
> was trying to insert a new record in the EMP table, *which was housed in a
> 'USERS' tablespace*... so, in 8.0.5, from memory, and crossing my big toes
> and other extremeties, just in case, it was also true that you didn't need
 a
> non-SYSTEM rollback segment to create non-SYSTEM tablespace.
>
> Can't speak to what was going on in 7.3, of course. I'm too young to
> remember.
>
> Regards ;-)
> HJR
> --
> =============================!!=============================
> The views expressed are my own only, and definitely NOT those of Oracle
> Corporation
> =============================!!=============================
>
>
> "Niall Litchfield" <niall.litchfield_at_dial.pipex.com> wrote in message
> news:3b10f71c$0$15028$cc9e4d1f_at_news.dial.pipex.com...
> > Don't worry about contradicting me Howard, I'm wrong at least as often
 as
> > most - and was posting from memory. Now i think more carefully I'm sure
 that
> > at least as far as 8i goes you are right, since my database creation
 scripts
> > first started failing on 8i when I couldn't create a ROLLBACK SEGMENT in
 a
> > non-system (and locally managed natch ) tablespace, which somewhat
 implies
> > that the tablespace had been successfully created !
> >
> > So Dino it looks like I and your book are wrong at least as far as 8i
 goes.
> >
> >
> > --
> >
> > Niall Litchfield
> > Oracle DBA
> > Audit Commission UK
> > "Howard J. Rogers" <howardjr_at_www.com> wrote in message
> > news:3b10e292_at_news.iprimus.com.au...
> > > I hate to contradict someone of Niall's posting record, but this
 simply
> > > isn't true (and I've just tested it), and hence the original statement
 in
> > > Dino's Oracle book is completely incorrect. Creating a tablespace
 simply
> > > means generating an O/S file, and updating the data dictionary to
 recognise
> > > the new logical entity -and the SYSTEM rollback segment alone is
 plenty
> > > sufficient to cope with the necessary DML on the data dictionary.
> > >
> > > For the record (8.1.7 on NT, but it's also true of 8.1.5 on Solaris):
> > > ===========
> > > QUOTE ON:
> > >
> > > SQL> select segment_name,status from dba_rollback_segs;
> > >
> > > SEGMENT_NAME STATUS
> > > ------------------------------ ----------------
> > > SYSTEM ONLINE
> > > RBS0 ONLINE
> > > RBS1 ONLINE
> > > RBS2 ONLINE
> > > RBS3 ONLINE
> > > RBS4 ONLINE
> > > RBS5 ONLINE
> > > RBS6 ONLINE
> > >
> > > 8 rows selected.
> > >
> > > SQL> alter rollback segment rbs0 offline;
> > >
> > > Rollback segment altered.
> > >
> > > SQL> alter rollback segment rbs1 offline;
> > >
> > > Rollback segment altered.
> > >
> > > SQL> alter rollback segment rbs2 offline;
> > >
> > > Rollback segment altered.
> > >
> > > SQL> alter rollback segment rbs3 offline;
> > >
> > > Rollback segment altered.
> > >
> > > SQL> alter rollback segment rbs4 offline;
> > >
> > > Rollback segment altered.
> > >
> > > SQL> alter rollback segment rbs5 offline;
> > >
> > > Rollback segment altered.
> > >
> > > SQL> alter rollback segment rbs6 offline;
> > >
> > > Rollback segment altered.
> > >
> > > SQL> select segment_name,status from dba_rollback_segs;
> > >
> > > SEGMENT_NAME STATUS
> > > ------------------------------ ----------------
> > > SYSTEM ONLINE
> > > RBS0 OFFLINE
> > > RBS1 OFFLINE
> > > RBS2 OFFLINE
> > > RBS3 OFFLINE
> > > RBS4 OFFLINE
> > > RBS5 OFFLINE
> > > RBS6 OFFLINE
> > >
> > > 8 rows selected.
> > >
> > > SQL> create tablespace blah datafile 'c:\blah.dbf' size 1m;
> > >
> > > Tablespace created.
> > >
> > > QUOTE OFF
> > > ============
> > >
> > > Creating a SEGMENT, however, in locally managed tablespace DOES
 require
 a
> > > non-system rollback segment to be online, since the extent allocation
 is
> > > handled by doing DML on the tablespace's bitmap -and that's non-SYSTEM
> > > tablespace DML, the rollback for which must be housed in a non-SYSTEM
> > > rollback segment.
> > >
> > > Regards
> > > HJR
> > > --
> > > =============================!!=============================
> > > The views expressed are my own only, and definitely NOT those of
 Oracle
> > > Corporation
> > > =============================!!=============================
> > >
> > >
> > > "Niall Litchfield" <niall.litchfield_at_dial.pipex.com> wrote in message
> > > news:3b0fe338$0$12246$cc9e4d1f_at_news.dial.pipex.com...
> > > > yep
> > > >
> > > > If your database has one system rollback segment in the system
 tablespace
> > > > then an attempt to issue
> > > >
> > > > create tablespace blah datafile '/blah/blah' size 100m;
> > > >
> > > > will fail with a message along the lines of cannot use system
 rollback
> > > > segment for this operation.
> > > >
> > > > you first need to issue the command
> > > > create rollback segment tmp;
> > > >
> > > > then create your non-system tablespaces and finally drop (or at
 least
 place
> > > > offline) the tmp rollback segment.
> > > >
> > > > HTH
> > > > --
> > > >
> > > > Niall Litchfield
> > > > Oracle DBA
> > > > Audit Commission UK
> > > > "Dino Hsu" <dino1_at_ms1.hinet.net> wrote in message
> > > > news:v1hvgto3lb40mo6v5hjffa48a2e2emkmk2_at_4ax.com...
> > > > > Dear all,
> > > > >
> > > > > In a book, it says:
> > > > > "In order to create non-SYSTEM tablespaces, you must first create
 a
> > > > > second rollback segment within the SYSTEM tablespace."
> > > > >
> > > > > Can anyone explain this? Thanks in advance.
> > > > >
> > > > > Dino
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>
Received on Mon May 28 2001 - 01:18:30 CDT