Re: ORA-01654 Problem

From: Sybrand Bakker <postmaster_at_sybrandb.demon.nl>
Date: Wed, 28 Jul 1999 19:40:04 +0200
Message-ID: <933183594.3409.0.pluto.d4ee154e@news.demon.nl>

Hi Tapan,
Could you issue the following statements

select segment_name, bytes/(1024*/1024) M, extents from user_segments
where segment_name = 'SYS_COO32069'
/
select sum(bytes)/(1024*1024)
from user_free_space
where tablespace_name = 'ESA_TBS'
/
SELECT *
FROM DBA_FREE_SPACE
WHERE TABLESPACE_NAME = 'ESA_TBS'
/
This will show you

- the total size of the index
- the amount of free space
- whether a next extent will 'fit'

You could try to use
alter tablespace esa_tbs coalesce
but SMON should already have done that.

Hth,

Sybrand Bakker, Oracle DBA

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 Wed Jul 28 1999 - 12:40:04 CDT