Re: to create a non-SYSTEM tablespace

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

> > > >

> > >

> > >

> >

> >

>

>