Oracle 9 - tablespace default storage

[Jens H. Hamann]

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;

[sybrandb]

"Jens H. Hamann" <jh*@agramkow.dk> wrote in message news:<40**********************@dread14.news.tele.d k>...

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