Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Is it possible to create a System tablespace with Local managed extent?

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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US