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

[Quoted] [Quoted] The create database command does not, in and of itself, create any rollback [Quoted] [Quoted] 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 [Quoted] home. But I can tell you without fear of contradiction that, as stated above, [Quoted] 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.

[Quoted] 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.

[Quoted] Tell that to the database that is functioning perfectly. The SYSTEM_RSP rollback [Quoted] 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.

[Quoted] 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.

[Quoted] 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
[Quoted]       TABLESPACE SYSTEM
[Quoted]       STORAGE (INITIAL 128K NEXT 128K MINEXTENTS 2 MAXEXTENTS 100 OPTIMAL 256K);

[Quoted]    ALTER ROLLBACK SEGMENT systm_rsp ONLINE;

And that was all that was created.

[Quoted] 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.

[Quoted] Excuse me please for being short ... but I really don't have the time to be your [Quoted] lacky. I tried something out to confirm a matter related to locally managed [Quoted] 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 Thu Mar 15 2001 - 05:50:15 CET