ORA-01654 Problem

From: Tapan Trivedi <tapan.trivedi_at_abbnm.com>
Date: Wed, 28 Jul 1999 11:32:36 -0500
Message-ID: <379F30A4.B19B23D8@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 Wed Jul 28 1999 - 11:32:36 CDT