Re: Oracle 9 - tablespace default storage

Hello Howard, thank you for your time to explain it to me.

I am confused though, does this mean that i should create different tablespaces for each size of table i have?

For example a small table that only hold little data and is updated infrequent should reside in tablespace for small tables (lets say 100k extents). And a larger table that hold lots of data and is updated frequently should be in a tablespace for large tables, i.e. 10M extends.

If the above is true this means in fact that i would have to create three data tablespaces and the same amount of index tablespaces i.e. six tablespaces one for each extent type 100k/1M/100M.

Please advise?

best regards
Jens Hamann

"Howard J. Rogers" <hjr_at_dizwell.com> wrote in message news:4035d078$0$15135$afc38c87_at_news.optusnet.com.au...
>
> "Jens H. Hamann" <jhh_at_agramkow.dk> wrote in message
> news:4035c482$0$1611$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?
>
> Why would you want it to in the first place? No-one should ever have
> segment-specific storage clauses that mention extent sizes. Ever.
>
> > 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.
>
> You have locally managed tablespaces, probably. In 9i these are the
default
> unless you explicitly demand EXTENT MANAGEMENT DICTIONARY when you create
> the tablespace. In LMT, initial is sort-of respected, but next is
completely
> and utterly ignored. What you've actually got, too, is autoallocated LMT.
>
> > I have included my script below, can someone please point out what could
> be
> > wrong.
>
> Apart from most of it, you mean? It's an OK script for 8.0, sort of. But
it
> is woefully out of date for 9i. Autoextension on data files is convenient,
> but it's not particularly good practice, nor good for performance. At
least
> you've remembered to put in a NEXT and a MAXSIZE clause. But the default
> storage clause is largely not applicable in 9i. MAXEXTENTS and NEXT for
> example are irrelevant, as is PCTINCREASE.
>
> To actually request PCTINCREASE for a segment when you create it is sheer
> and utter madness, even in Oracle version 7!
>
> And to have differntly-sized extents for different segments in the same
> tablespace is a recipe for fragmentation, even in Oracle version 7.
>
> You need to get thee to tahiti.oracle.com and have a hunt around for
> references to locally managed tablespace and why it's such a good idea.
You
> could google on the topic, too. What you're doing has no real place in a
9i
> database.
>
> As an example of where the problems start:
>
> connect / as sysdba
> SQL > CREATE TABLESPACE JHH_DATA DATAFILE
> '/u01/app/oracle.oradata/jhh_data.dbf' SIZE 50M
> 2 AUTOEXTEND ON NEXT 50M MAXSIZE 4000M DEFAULT STORAGE (INITIAL 24K
NEXT
> 24K
> 3 MINEXTENTS 1 MAXEXTENTS UNLIMITED PCTINCREASE 0);
>
> Tablespace created.
>
> SQL> select extent_management from dba_tablespaces
> 2 where tablespace_name='JHH_DATA';
>
> EXTENT_MAN
> ------------
> LOCAL
>
> Regards
> HJR
>
>
>
> >
> > 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 Fri Feb 20 2004 - 04:30:13 CST