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

From: Daniel A. Morgan <dmorgan_at_exesolutions.com>
Date: Sun, 11 Mar 2001 06:27:26 -0800
Message-ID: <3AAB8B4E.5E0812B2@exesolutions.com>

Do you see the part where the SYSTEM tablespace is created?

Change it to create a locally managed tablespace. That is what Oracle says works. I haven't had reason to try it yet but I will do so in the next week or two and report my results.

Daniel A. Morgan

"Howard J. Rogers" wrote:

> "Daniel A. Morgan" <dmorgan_at_exesolutions.com> wrote in message
> news:3AAB287A.C993852E_at_exesolutions.com...
> > > This I like. "You can create a database....". Any suggestions as to
 *how*?
> > >
> > > I mean, ordinarily the 'extent management local' clause belongs to the
> > > 'create tablespace' instruction. The one thing you don't have in a
 'create
> > > database' statement is a 'create tablespace' clause.
> >
> > Excuse me ... but yes you do. And here's an example:

>

> Your example shows that the create database command does NOT have a 'create
> tablespace' clause. You create a database with just one tablespace (SYSTEM)
> by implication... but the statement is a 'datafile 'c:\blah' size X' one.
> All other tablespaces are then simply added afterwards using bog-standard
> 'create tablespace' clauses (as you demonstrate wonderfully), to which the
> 'extent management local' clause can of course be added. That still doesn't
> explain how SYSTEM gets created locally managed.

>


> >

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

> > -- NOARCHIVELOG

> >  CHARACTER SET WE8ISO8859P1

> >       DATAFILE 'D:\ORADATA\DEV\sys1dev.dbf'

> >          SIZE 120M

> >          AUTOEXTEND on

> >          NEXT 12M

> >          MAXSIZE 240M;

>

> That little lot does not make SYSTEM locally managed. That will produce a
> SYSTEM tablespace using traditional dictionary management techniques, with
> the datafile allowed to expand automatically in 12M increments upto a
> maximum of 240M.

>


> >

> >    CREATE ROLLBACK SEGMENT systm_rsp

> >       TABLESPACE SYS

> >       STORAGE (INITIAL 128K NEXT 128K MINEXTENTS 2 MAXEXTENTS 100

 PCTINCREASE 0)


> >

> >       OPTIMAL 256K

> >       ONLINE

> >       PERMANENT;

> >

>

> Optimal is a bad one to set, but let it pass. "Permanent" is a waste of
> time, since it's the default anyway. And if this was locally managed, the
> default storage clause would be a complete waste of time, of course.

>


> >    CREATE TABLESPACE user_rsp

> >       DATAFILE 'D:\ORADATA\DEV\rbs1dev.dbf'

> >          SIZE 128M

> >          REUSE

> >          AUTOEXTEND on

> >          NEXT 8M

> >          MAXSIZE 256M

> >       DEFAULT STORAGE (INITIAL 256K NEXT 256K MINEXTENTS 2 MAXEXTENTS 100

> > PCTINCREASE 1)

> >       ONLINE

> >       PERMANENT;

> >

> >    CREATE PUBLIC ROLLBACK SEGMENT rb1

> >    TABLESPACE  user_rsp

> >    OPTIMAL 512K;

> >

> >    ALTER ROLLBACK SEGMENT rb1 ONLINE;

> >

> >    CREATE TABLESPACE user_tsp

> >       DATAFILE 'D:\ORADATA\DEV\tsp1dev.dbf'

> >          SIZE 64M

> >          REUSE

> >          AUTOEXTEND on

> >          NEXT 8M

> >          MAXSIZE 128M

> >       DEFAULT STORAGE (INITIAL 64K NEXT 64K MINEXTENTS 1 MAXEXTENTS 249

> > PCTINCREASE 1)

> >       ONLINE

> >       TEMPORARY;

> >

> >    CREATE TABLESPACE data_dsp

> >       DATAFILE 'D:\ORADATA\DEV\data1dev.dbf'

> >          SIZE 2560M

> >          REUSE

> >          AUTOEXTEND on

> >          NEXT 5M

> >          MAXSIZE 10240M;

> >       DEFAULT STORAGE (INITIAL 64K NEXT 64K MINEXTENTS 1 MAXEXTENTS 249

> > PCTINCREASE 1)

> >       ONLINE

> >       PERMANENT;

> >
> > So now that we've solved that problem just change the tablespace
 definitions to
> > locally managed.
> >

>

> Why on earth would you want to do things this way? Everything after the
> 'create database....maxsize 240M' in your example is just a bunch of simple
> create tablespace commands. Why not simply create them locally managed in
> the first place?

>

> You aren't suggesting, I hope, that everything after the 'maxsize 240M'
> clause is part of the "create database" statement? At the end of that first
> bit of syntax, your database is fully formed, with one tablespace, and is
> left in the open stage. Hence all subsequent 'create tablespace' commands
> are just normal, individual commands that could be issued at any time, and
> with the locally managed variant, without a problem. I realise that all the
> text books tell you to include the whole lot in one script, but that doesn't
> mean that the one script contains only a single SQL statement.

>

> And, incidentally, have you ever tried 'just chang(ing) the tablespace
> definitions to locally managed'?? Because you can't. There's a package
> which will convert dictionary-managed tablespace to locally managed, but
> it's not functional in 8.1.5, and is only reliable (as Jonathan Lewis posted
> here just the other day) from 8.1.6.3 onwards -and even then, I wouldn't
> trust it further than I could throw it. But as for any 'alter tablespace'
> command, forget it.

>

> And you still haven't shown me how SYSTEM gets *created* locally managed.

>

> HJR

>

> > Daniel A. Morgan
> >
  Received on Sun Mar 11 2001 - 08:27:26 CST