468,290 Members | 1,993 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,290 developers. It's quick & easy.

Oracle 9 - tablespace default storage

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;
Jul 19 '05 #1
1 19965
"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
Jul 19 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

11 posts views Thread by Philip D Heady | last post: by
5 posts views Thread by Srini | last post: by
4 posts views Thread by susmita_ganguly | last post: by
4 posts views Thread by Ulrich Sprick | last post: by
6 posts views Thread by robert | last post: by
56 posts views Thread by Ashish Patankar | last post: by
1 post views Thread by Jens H. Hamann | last post: by
2 posts views Thread by MrBee | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.