Re: Is it possible to create a System tablespace with Local managed extent?

From: Howard J. Rogers <howardjr_at_www.com>
Date: Wed, 14 Mar 2001 20:09:40 +1100
Message-ID: <3aaf3510_at_news.iprimus.com.au>


"Daniel A. Morgan" <dmorgan_at_exesolutions.com> wrote in message news:3AAF1059.933CCDB0_at_exesolutions.com... [Snip]
> > That's true. I think I posted originally that the one non-system
 rollback
> > segment can be created in the SYSTEM tablespace: where it's housed is
> > irrelevant. But it must NOT be the one called SYSTEM -'cos that *is*
 the
> > system rollback segment, and won't suffice. On the training courses
 (and I
> > believe in the sample database supplied by Oracle for 8i installs)
 there's
> > one called SYSROL (just to be bloody confusing about it), which is
 housed in
> > the SYSTEM tablespace -but it isn't the system rollback segment, and
> > therefore counts for the purposes of creating your first locally managed
> > segment.
>
> You are both correct and incorrect. What you state is true of Oracle's
 install
> program creates the database instance.

Not sure what you mean here... I never use the wizards and all that jazz. I just use the 'create database' command, issued from within server manager (or SQL*Plus if I'm feeling slightly weird).

>But if you do what I did for my testing.
> And build every tablespace and rollback segment in a script executed from
 Server
> Manager it is not. When rolling your own you can call the SYSTEM
 tablespace
> rollback segment anything you want. I chose the name SYSTEM_RSP.
>

Well. When you issue the 'create database' command from server manager, you actually call the sql.bsq script (provided by Oracle). This is a line from my current sql.bsq (it's the fourth line after the mass of REM'd comments):

create rollback segment SYSTEM tablespace SYSTEM   storage (initial 50K next 50K)

In other words, you don't ever issue a command to create the SYSTEM rollback segment -it's done for you as part of the 'create database' command. And, unless you edit sql.bsq, that first rollback segment is called SYSTEM. So how you can claim that you 'get to choose' what to call the SYSTEM rollback segment is plain wrong.

The fact that you then created for yourself a rollback segment called 'SYSTEM_RSP' is irrelevant. Because it's not the one created by sql.bsq, it's not the SYSTEM rollback segment. Because it's created in the SYSTEM tablespace, it's by definition (as we've found out) a dictionary-managed rollback segment. You can create that second one because the first one is also in dictionary-managed tablespace.

> > So there must have been 2 rollback segments before you started. One
 called
> > system, and one called something else, both housed in SYSTEM tablespace.
> > That's fine (though it's not fine from the Oracle point of
 view -personally,
> > I think the need for one dictionary, non-system rollback segment is a
 real
> > pain in the butt, and makes me think that locally managed anything
 hasn't
> > been properly thought through. Hopefully, 9i will resolve all these
> > issues!)
>
> No. Only the single SYSTEM tablespace rollback segment named SYSTEM_RSP.
 After
> that I created a locally managed tablespace named USER_RSP and had no
 problem
> building rollback segments within it.
>

I'm dubious. Show us the output from dba_rollback_segs. I guarantee you did not have NO rollback segments before SYSTEM_RSP was created, unless you edited sql.bsq.

Why not take this step by step? Issue the following simple command:

create database BLAH
logfile group 1 'path/filename' size 150K,

          group 2 'path/filename' size 150K datafile 'path/system01.dbf' size 80M;

Let that cook, and you'll have a new, raw database with one SYSTEM tablespace.

Now run %ORACLE_HOME%/'rdbms/admin/catalog.sql to build the data dictionary views.

Then do a select * from dba_rollback_segs and tell me what you've got. I guarantee you'll have a rollback segment called SYSTEM.

> I hope this clarifies things at least as far as version 8.1.7.
>

Not really. You don't show how you created your database. You don't show how you created SYSTEM_RSP. You don't say whether you edited sql.bsq.

A simple question.... do you include SYSTEM_RSP in your init.ora to ensure it is brought online whenever the Instance is re-started?

Regards
HJR
> And I reserve the right to be wrong again.
>
> Daniel A. Morgan
>
Received on Wed Mar 14 2001 - 10:09:40 CET

Original text of this message