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

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

The create database command does not, in and of itself, create any rollback segments anywhere. You must specify them. At least that is my previous experience and my experience with 8.1.7. The only rollback segments created were the ones I coded.

> 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)

Not sure if that line is in mine or not as I did it at work and I am now at home. But I can tell you without fear of contradiction that, as stated above, the only rollback segment created was the one I specified with an unusual name.

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

Perhaps if you don't ... Oracle does. But I can tell you that if you do ... Oracle doesn't.

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

Tell that to the database that is functioning perfectly. The SYSTEM_RSP rollback segment is in the SYSTEM tablespace, it is private to SYSTEM, and no other rollback segment performing that function was created.

> > > So there must have been 2 rollback segments before you started.

Lets try this very very carefully.

When I started there was a machine with Solaris 2.8. No Oracle software.

I installed Oracle without any database. None. Nada. Nyet. Zilch.

I then issued something similar to the following:

CREATE DATABASE DEV
   CONTROLFILE REUSE

       LOGFILE 'F:\ORADATA\DEV\log01dev.dbf' size 6M reuse,
               'F:\ORADATA\DEV\log02dev.dbf' size 6M reuse

-- ARCHIVELOG
-- or
-- NOARCHIVELOG
 CHARACTER SET WE8ISO8859P1
      DATAFILE 'D:\ORADATA\DEV\sys1dev.dbf'
         SIZE 120M
         AUTOEXTEND on
         NEXT 120M
         MAXSIZE 240M;

   CREATE ROLLBACK SEGMENT systm_rsp
      TABLESPACE SYSTEM
      STORAGE (INITIAL 128K NEXT 128K MINEXTENTS 2 MAXEXTENTS 100 OPTIMAL 256K);

   ALTER ROLLBACK SEGMENT systm_rsp ONLINE;

And that was all that was created.

If you wish to argue on this point go for it. But I'll not be party to 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.

Excuse me please for being short ... but I really don't have the time to be your lacky. I tried something out to confirm a matter related to locally managed system tablespaces. I did it. I admitted, and corrected, my error from a previous posting. If you want to beat this one to death do it on your own server.

Daniel A. Morgan Received on Wed Mar 14 2001 - 22:50:15 CST