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

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; CREATE TABLESPACE ts1_INDEX

    DATAFILE '/dxx/oradata/ts1_index.dbf' size 30M     AUTOEXTEND ON NEXT 15M MAXSIZE 60M; And this tables and indexes

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)

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)

The storage values where calculated following the 'ORACLE DBA HandBook'. Theoricaly I have other tables and indexes, and the sum of the space in the INITIAL of ALL tables is the space asign to the tablespaces (data and index), but when i try to create, I just only create the tables above, the last table have other index,

CREATE INDEX XIF29T3 ON T3

(      id_t3 ASC, id_t2 ASC )
       PCTFREE 10
       TABLESPACE UTM_ST_INDEX
       STORAGE ( INITIAL 25k NEXT 25k MINEXTENTS 1
              MAXEXTENTS 249);

when i try to create it the error ORA-01658 appears. Checking all yours messages found that the problem is out of space, but the sum of the INITIAL is minimum to this tables and indexes. When i check the space in the server i saw that the ts1_data is in 101 mb aprox and the ts1_index is in the max size (60M), and checking the DBA_FREE_SPACE the free space for ts1_INDEX is less than 30MB and the ts1_DATA is 140MB more or less.

Why the error appears ? then the INITIAL is not the only condition for creating the index (or table ?, after two more tables, the ts1_DATA is out of space too)
or the free space must be INITIAL + NEXT * (MAXEXTENTS - 1)? is that is the case, we try to calculate that but the number differs very much...

any suggestions ?

My Oracle is the 8i and the server is AIX Received on Thu May 06 2004 - 16:19:14 CDT