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

"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.

[Quoted] [Quoted] Not sure what you mean here... I never use the wizards and all that jazz. I [Quoted] just use the 'create database' command, issued from within server manager [Quoted] (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.
>

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

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

[Quoted] [Quoted] 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 [Quoted] how you can claim that you 'get to choose' what to call the SYSTEM rollback segment is plain wrong.

[Quoted] [Quoted] 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, [Quoted] it's not the SYSTEM rollback segment. Because it's created in the SYSTEM [Quoted] 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.
>

[Quoted] [Quoted] I'm dubious. Show us the output from dba_rollback_segs. I guarantee you [Quoted] 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