| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Oracle 9 - tablespace default storage
"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
 |
 |