Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: to create a non-SYSTEM tablespace

Re: to create a non-SYSTEM tablespace

From: Niall Litchfield <niall.litchfield_at_dial.pipex.com>
Date: Sun, 27 May 2001 13:49:22 +0100
Message-ID: <3b10f71c$0$15028$cc9e4d1f@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 Sun May 27 2001 - 07:49:22 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US