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

From: Howard J. Rogers <howardjr_at_www.com>
Date: Sun, 11 Mar 2001 20:41:05 +1100
Message-ID: <3aab47fc_at_news.iprimus.com.au>


[Quoted] [Quoted] "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:

[Quoted] Your example shows that the create database command does NOT have a 'create [Quoted] tablespace' clause. You create a database with just one tablespace (SYSTEM) [Quoted] 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 [Quoted] 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;
[Quoted] 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;
>

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

[Quoted] [Quoted] 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 [Quoted] create tablespace commands. Why not simply create them locally managed in the first place?

[Quoted] 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 [Quoted] 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 [Quoted] text books tell you to include the whole lot in one script, but that doesn't [Quoted] mean that the one script contains only a single SQL statement.

[Quoted] 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 [Quoted] 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 - 10:41:05 CET

Original text of this message