Re: About ORA-01658 unable to create INITIAL extent for segment in tablespace <ts>

Marck wrote:

> Hi Gurus,
>
> I have a questions about this error.
>
> I have this conf
>
> CREATE TABLESPACE ts1_data
> DATAFILE '/d00/oradata/ts1_data.dbf' size 118M
> AUTOEXTEND ON NEXT 59M MAXSIZE 236M;
Crikey. Autoextend is a bad idea. But incrementing in steps of 59M and stopping at 236M is even whackier. This is fine-tuning space management gone mad (IMHO, natch).

And no default storage clause. And no minimum extent clause either. Sad, sad, sad.

[snip]

> CREATE TABLE T1 (
> id_t1 NUMBER NOT NULL, eS NUMBER NULL, eD NUMBER NULL,
> PRIMARY KEY (id_t1)
> USING INDEX
> PCTFREE 10
> TABLESPACE ts1_INDEX
> STORAGE ( INITIAL 14k NEXT 14k
> MINEXTENTS 1 MAXEXTENTS 249 )
> )
> PCTFREE 10
> TABLESPACE ts1_DATA
> STORAGE ( INITIAL 20K NEXT 20K
> MINEXTENTS 1 MAXEXTENTS 249 PCTINCREASE 0 );
>
> CREATE TABLE T2 (
> id_t2 NUMBER NOT NULL, e VARCHAR2(10) NULL,
> te VARCHAR2(70) NULL,
> PRIMARY KEY (id_t2)
> USING INDEX
> PCTFREE 10
> TABLESPACE ts1_INDEX
> STORAGE ( INITIAL 1K NEXT 1K MINEXTENTS 1
> MAXEXTENTS 250 ))
> PCTFREE 10
> TABLESPACE ts1_DATA
> STORAGE ( INITIAL 4K NEXT 4K MINEXTENTS 1
> MAXEXTENTS 50 PCTINCREASE 0)
So you immediately have two different extent sizes inside TS1_DATA, and you won't get a 4K initial anyway (minimum allocation for initial is 5 Oracle blocks).

> CREATE TABLE T3(
> id_t3 NUMBER NOT NULL, id_t NUMBER NULL,
> id_t1 NUMBER NOT NULL, t VARCHAR2(255) NOT NULL,
> d VARCHAR2(2000) NULL, fi DATE NULL,
> fe DATE DEFAULT NULL, ln VARCHAR2(50) NULL,
> fn VARCHAR2(100) NULL, fs NUMBER NULL,
> b NUMBER(1) NULL, p NUMBER(1) NULL,
> PRIMARY KEY (id_t2)
> USING INDEX
> PCTFREE 10
> TABLESPACE ts1_INDEX
> STORAGE ( INITIAL 18k NEXT 18k MINEXTENTS 1
> MAXEXTENTS 249),
> FOREIGN KEY (id_t1) REFERENCES T1)
> PCTFREE 10
> TABLESPACE ts1_DATA
> STORAGE (
> INITIAL 267K NEXT 267K MINEXTENTS 1
> MAXEXTENTS 249 PCTINCREASE 0)
And this is just getting crazy. 267K??? Why not 266? or 268?

> The storage values where calculated following the 'ORACLE DBA
> HandBook'.

Presumably a reference to Kevin Loney's masterpiece? Trash it. Indeed, trash every book you've got from Oracle Press unless it also happens to have the magic words 'Tom' and 'Kyte' on the front.

[snip]

> any suggestions ?

Can I suggest you get a lot more relaxed about storage than you appear to be? You're running 8i, so you should be using locally managed tablespaces in any case, not the dictionary-managed monsters you've produced here. If your application vendor says 'we don't support LMT', then dump the vendor and find one that does, and in the meantime at least implement default storage clauses and minimum extent clauses.

Why? Because the way you've got it at the moment, you are going to suffer from tablespace fragmentation. And you are already suffering from the primary drawback of dictionary-managed tablespace, which is a propensity to, and a feeling that one must, fine-tune space management to the nth degree.

Switch to locally managed tablespaces and you can never fragment it (or at worst, with autoallocate, very,very rarely fragment it), and more importantly, you can stop worrying about extent sizes and extent numbers. (New article just up at www.dizwell.com in the Basic Admin FAQ explains this in some detail).

Just create a couple of locally-managed autoallocate tablespaces, and forget all about the extent sizing stuff. Then you can move on and concentrate on matters which really do have a bearing on performance.

> My Oracle is the 8i and the server is AIX

With an up-to-date 8i, there's no excuse: dictionary managed tablespace is ancient history, and you should be using LMT.

Regards
HJR Received on Thu May 06 2004 - 19:04:11 CDT