Re: Oracle 9 - tablespace default storage

"Jens H. Hamann" <jhh_at_agramkow.dk> wrote in message news:<40350c8d$0$1558$edfadb0f_at_dread14.news.tele.dk>...
> Hi, I am having a strange problem with the storage parameters in Oracle
> 9.2.0.1 database.
> I create a tablespace with some default storage parameters but when i am
> adding a table to the tablespace i can not override the NEXT statement?
> No matter what i set the NEXT storage parameters to, it is always the same
> as the tablespace default, the INITIAL is overriden just fine.
> I have included my script below, can someone please point out what could be
> wrong.
>
> Thanks in advance!
> Jens Hamann
>
> CREATE TABLESPACE JHH_DATA DATAFILE 'C:\OraDb\JHH_DATA_00.dbf' SIZE 50M
> AUTOEXTEND ON NEXT 50M MAXSIZE 4000M DEFAULT STORAGE (INITIAL 24K NEXT 24K
> MINEXTENTS 1 MAXEXTENTS UNLIMITED PCTINCREASE 0);
> CREATE TABLESPACE JHH_INDEX DATAFILE 'C:\OraDb\JHH_IDX00.dbf' SIZE 50M
> AUTOEXTEND ON NEXT 50M MAXSIZE 4000M DEFAULT STORAGE (INITIAL 24K NEXT 24K
> MINEXTENTS 1 MAXEXTENTS UNLIMITED PCTINCREASE 0);
> CREATE TEMPORARY TABLESPACE JHH_TEMP TEMPFILE 'C:\OraDb\JHH_TEMP00.dbf' SIZE
> 50M AUTOEXTEND ON NEXT 50M MAXSIZE 4000M;
> CREATE USER JHH IDENTIFIED BY JHH;
> GRANT DBA, CONNECT TO JHH;
> ALTER USER JHH DEFAULT TABLESPACE JHH_DATA TEMPORARY TABLESPACE JHH_TEMP;
> ALTER USER JHH QUOTA UNLIMITED ON JHH_DATA QUOTA UNLIMITED ON JHH_INDEX
> QUOTA UNLIMITED ON JHH_TEMP;
>
> CREATE TABLE JHH.LARGE_TABLE (
> COLUMN_1 NUMBER (5) NOT NULL,
> COLUMN_2 NUMBER (5),
> COLUMN_3 VARCHAR2 (50),
> CONSTRAINT PK_LARGE_TABLE
> PRIMARY KEY ( COLUMN_1 )
> USING INDEX
> TABLESPACE JHH_INDEX PCTFREE 10
> STORAGE ( INITIAL 10M NEXT 10M PCTINCREASE 0 ))
> TABLESPACE JHH_DATA
> PCTFREE 10 PCTUSED 40
> INITRANS 1 MAXTRANS 255
> STORAGE (
> INITIAL 10M NEXT 10M PCTINCREASE 0
> MINEXTENTS 1 MAXEXTENTS 2147483645 )
> NOCACHE;
>
> CREATE TABLE JHH.SMALL_TABLE (
> COLUMN_1 NUMBER (5) NOT NULL,
> COLUMN_2 NUMBER (5),
> COLUMN_3 VARCHAR2 (50),
> CONSTRAINT PK_SMALL_TABLE
> PRIMARY KEY ( COLUMN_1 )
> USING INDEX
> TABLESPACE JHH_INDEX PCTFREE 10
> STORAGE ( INITIAL 64K NEXT 64K PCTINCREASE 0 ))
> TABLESPACE JHH_DATA
> PCTFREE 10 PCTUSED 40
> INITRANS 1 MAXTRANS 255
> STORAGE (
> INITIAL 64K NEXT 64K PCTINCREASE 0
> MINEXTENTS 1 MAXEXTENTS 2147483645 )
> NOCACHE;
In locally managed tablespaces next will always be equal to init, by design.
In Oracle 9i locally managed tablespaces are the default. IMO, you shouldn't bother, especially if the tablespaces are created with autoallocate. You don't want explicit storage clauses anymore: they cause maintenance.

Sybrand Bakker
Senior Oracle DBA Received on Fri Feb 20 2004 - 13:35:57 CET