Re: ORA-01654 Problem

You may issue the following statements to detect the problem:

    select max(bytes)

      from user_free_space
      where tablespace_name='ESA_INDEX';

You may issue "alter tablespace esa_index coalesce" before you issue the query. The max must be large than next_extent (2097152 in your case). Since the whole extent must be put into a free extent, the total free space is irrelative here. If the max is less than 2M, you must enlarge the tablespace more than 2M. Then,

    select extents

      from user_segments
      where segment_name='SYS_COO32069'
        and segment_type='INDEX';

The extents must be less than maxextents(121 in your case). If the index reaches the maxextents,
you may issue the "alter index" to modify the maxextents parameter.

    alter index SYS_COO32069 storage (maxextents n);

n is a number large than 121 or unlimited.

You did not say what verson your Oracle is. If your version is too old, you can't issue the prior "alter index" statement to config more maxextents,
the leftover way is to recreate the index with larger initial and next parameters.

Tapan Trivedi <tapan.trivedi_at_abbnm.com> wrote in message news:379F30A4.B19B23D8_at_abbnm.com...
> Hey Gurus,
>
> Hi . On one of our field systems I am facing a problem. I am getting
> ORA-01654 Unable to extend index
> INDEXNAME.SYS_COO32069 BY 1024 in Tablespace Tablespacename.
> When I look up the error it says
> 01654, 00000, "unable to extend index %s.%s by %s in tablespace %s"
> // *Cause: Failed to allocate an extent for index segment in
> tablespace.
> // *Action: Use ALTER TABLESPACE ADD DATAFILE statement to add one or
> more
> // files to the tablespace indicated.
>
>
> When I increase the tablespace it still gives me an
> error. Please let me know if I am doing something wrong. Underneath are
> some sql statements that I ran that might give you an idea as to what
> is happening. If you need more info please let me know.
>
> Tapan H Trivedi
>
>
> SQL> select * from dba_tablespaces
> 2 where tablespace_name = 'ESA_INDEX';
>
> TABLESPACE_NAME INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS
> ------------------------------ -------------- ----------- -----------
> MAX_EXTENTS PCT_INCREASE STATUS CONTENTS
> ----------- ------------ --------- ---------
> ESA_INDEX 2097152 2097152 2
> 121 0 ONLINE PERMANENT
>
>
> 1 select * from dba_tablespaces
> 2* where tablespace_name = 'ESA_TBS'
> SQL> /
>
> TABLESPACE_NAME INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS
> ------------------------------ -------------- ----------- -----------
> MAX_EXTENTS PCT_INCREASE STATUS CONTENTS
> ----------- ------------ --------- ---------
> ESA_TBS 10240 10240 1
> 121 50 ONLINE PERMANENT
>
>
>
> select owner,constraint_name from user_constraints
> 2 where constraint_name = 'SYS_C0032069';
>
> OWNER CONSTRAINT_NAME
> ------------------------------ ------------------------------
> RANGER SYS_C0032069
>
> SQL> select * from user_constraints
> 2 where constraint_name = 'SYS_C0032069';
>
> OWNER CONSTRAINT_NAME C
> ------------------------------ ------------------------------ -
> TABLE_NAME
> ------------------------------
> SEARCH_CONDITION
> --------------------------------------------------------------------------------
> R_OWNER R_CONSTRAINT_NAME DELETE_RU
> STATUS
> ------------------------------ ------------------------------ ---------
> --------
> RANGER SYS_C0032069 P
> ESDATA
>
>
> ENABLED
Received on Fri Aug 06 1999 - 00:34:01 CDT