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

[Quoted] [Quoted] "Daniel A. Morgan" <dmorgan_at_exesolutions.com> wrote in message news:3AB04A07.51934AE5_at_exesolutions.com...
> > 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.

[Quoted] [Quoted] Daniel, please. I've been teaching this stuff for years now. The create [Quoted] [Quoted] database command creates the system datafile, the redo logs specified in the [Quoted] command syntax, the control files specified in the init.ora, and one SYTEM [Quoted] rollback segment. This is pretty basic stuff.

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

You are simply wrong on this.

From technet.oracle.com, the following appears when you search on 'Create Database':

QUOTE ON....
When you execute a CREATE DATABASE statement, Oracle performs the following operations:

  a.. Creates the datafiles for the database.

  b.. Creates the control files for the database. See Chapter 5, "Managing Control Files".

  c.. Creates the redo log files for the database. See Chapter 6, "Managing the Online Redo Log".

  d.. Creates the SYSTEM tablespace and the SYSTEM rollback segment.

  e.. Creates the data dictionary.

  f.. Creates the users SYS and SYSTEM. See "Database Administrator Usernames".

  g.. Specifies the character set that stores data in the database

  h.. Mounts and opens the database for use

QUOTE OFF You might note the fourth bullet point.

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

You're talking the same nonsense we had over the locally managed tablespace issue. You don't seem to grasp where the true 'create database' syntax ends, and the rest of your create database SCRIPT begins.

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

...Was created BY YOU. The point is that one is created BY ORACLE, and you don't specify it explicitly in the syntax, because its coded that way in sql.bsq, which is called by the 'create database' command.

> > > > 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
> MAXDATAFILES 1000
> MAXINSTANCES 3
> -- ARCHIVELOG
> -- or
> -- NOARCHIVELOG
> CHARACTER SET WE8ISO8859P1
> DATAFILE 'D:\ORADATA\DEV\sys1dev.dbf'
> SIZE 120M
> AUTOEXTEND on
> NEXT 120M
> MAXSIZE 240M;
>

END OF CREATE DATABASE COMMAND -at this point, you get logs, controlfile, system tablespace and a SYSTEM rollback segment, plus the Users SYS and SYSTEM, and a set of data dictionary base tables. Everything after this point is simply you doing additional SQL commands that could be issued any time, any place.

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

That's all you ASKED to be created. The point is that Oracle creates a rollback segment called SYSTEM whether you ask for it or not, because without one no updates to the data dictionary tables are possible.

>
> If you wish to argue on this point go for it. But I'll not be party to it.
>

No, I didn't think you would. It must be painful to be wrong so badly twice on simple matters of what happens during a 'create database' command. You got the locally managed tablespace bit wrong, and you're wrong on this one too.

You are the one arguing -with Oracle's own documentation, as demonstrated above.

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

I'm trying to teach you something. But since you don't appear to wish to do a simple 'select * from dba_rollback_segs' at the end of the 'create database' command and before the 'create rollback segment systm_rsp', I guess you don't want to learn.

If you would stop trying to create your databases with elaborate scripts, and take it a step at a time, you might get a better idea of what each piece of the puzzle is actually doing for you. You are combining multiple SQL commands in a single script and running away with the idea that somehow it's all part of the create database syntax. It isn't.

HJR
> Daniel A. Morgan
>
Received on Thu Mar 15 2001 - 08:35:36 CET