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

From: Daniel A. Morgan <dmorgan_at_exesolutions.com>
Date: Sat, 10 Mar 2001 23:25:46 -0800
Message-ID: <3AAB287A.C993852E@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:

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;

   CREATE ROLLBACK SEGMENT systm_rsp
      TABLESPACE SYS
      STORAGE (INITIAL 128K NEXT 128K MINEXTENTS 2 MAXEXTENTS 100 PCTINCREASE 0)

      OPTIMAL 256K
      ONLINE
      PERMANENT;

   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.

Daniel A. Morgan Received on Sun Mar 11 2001 - 01:25:46 CST