Oracle 9 - tablespace default storage

From: Jens H. Hamann <jhh_at_agramkow.dk>
Date: Thu, 19 Feb 2004 20:20:53 +0100
Message-ID: <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; Received on Thu Feb 19 2004 - 20:20:53 CET

Original text of this message