ORA-1652 Signaled during index rebuild

[kenny chia]

Hi
I was trying to rebuild one of my large index when I encountered

ORA-1652: unable to extend temp segment by 102400 in tablespace GENINDEX

in my alert log file.

I don't think there could be any temp (sort) segment in an index tablespace. Or is there?

Is Oracle trying to use the temp (sort) segment during index rebuild and failed?

During index rebuild, does Oracle keep the old index in the same tablespace while the new index is being built? And if so must we have free index tablespace in order to do index rebuild?

[Claudio Cilloni]

I verified that Oracle deletes the old index only after the new index is built. There is enough space in tablespace GENINDEX? Can you try to rebuild that index in another tablespace? (alter index rebuild tablespace )

ciao
Claudio

[twang]

During an index rebuild, there are two types of temporary segments involved.

First, there are the temporary segments that are used to store partial sort data when the SORT_AREA_SIZE is too small to process the complete sort set. These segments are built in the user's default TEMPORARY tablespace.

Second, as the index is being rebuilt, it uses a segment which is defined as a temporary segment until the rebuild is complete. Once this segment is fully populated, the old index can be dropped and this temporary segment is
redefined as a permanent segment with the index name. The error you are seeing is probably due to there being insufficient room in the index's tablespace to hold both the original index and the new version concurrently. The new version of the index, currently a temp segment, will be in the tablespace where the index is required (Oracle7) or in the default user tablespace (Oracle8).
However the tablespace referenced in the message can be the user's default tablespace (Oracle8) or the index tablespace (Oracle7).

This behaviour is different for different Oracle versions, what is your database verion?

[Tom Geudens]

Hi,
The problem may be that the user you use to do the rebuild doesn't have the right setting for temporary tablespace. You might want to take a look at that first ...

Regards,
Tom Geudens

[kenny chia]

I'm using Oracle8i, so I guess

ORA-1652: unable to extend temp segment by 102400 in tablespace GENINDEX

means that my index tablespace is not big enough to accomodate both the old and new indexes during rebuild

[twang]

Another thought, If the storage clause (next extent for example) for the existing index is set to a very high number (64MB or so) and you don't specify a storage clause with the rebuild command, Oracle will use the storage clause of the existing index to allocate the space for the temporary segments. This will allocate a lot of (unneeded) space and an ora-1652 can occur.
So, first of all, I would suggest to to check the current storage clause in dba_indexes:

Select OWNER
,INDEX_NAME
,TABLESPACE_NAME
,INITIAL_EXTENT
,NEXT_EXTENT
,MIN_EXTENTS
,MAX_EXTENTS
,PCT_INCREASE
from dba_indexes;

[Claudio Cilloni]

You can verify the size of the index with this SQL (user SYSTEM):

SELECT SEGMENT_NAME, SEGMENT_TYPE, BYTES FROM DBA_SEGMENTS WHERE SEGMENT_NAME = '';

the size is shown in bytes. The new index will (probably) need the same amount of space; if in GENINDEX tablespace there isn't enough space, then you know it's a space problem. If it is so, you can rebuild that index in another tablespace (so the old index will be deleted from GENINDEX) an then rebuild it again in GENINDEX. Or you can extend the GENINDEX tablespace.

hth
Claudio

[twang]

Use ALTER TABLESPACE ADD DATAFILE statement to add one or more files to the tablespace indicated(GENINDEX) or enabling autoextend on an existing datafile. Or you may create the object in other tablespace and see if it works.

[Alexander M. Ermes]

Hi there.
Could be two things.
Either the number of extends for the index itself is not big enough or you do not have enough bytes free in your tablespace.
you can test the tablespaces with this little sql :

set termout off
set feedback off
set pages 65
set lines 60
ttitle " Free space report "
column "TS Name" format a20
column "Sum MB" format 99999.99
column "Max MB" format 99999.99
spool /var/tmp/free1&&2..lst.&&1
select tablespace_name "TS Name",
sum(bytes) / 1048576 "Sum MB",
max(bytes) / 1048576 "Max MB"
from sys.dba_free_space
group by tablespace_name
;
spool off
set termout on
set feedback on
exit

Rgds
alexander M. Ermes

[Yogeeraj_1]

hi,

first of all,

why are you rebuilding indexes?

what is the goal behind that?

do you have metrics that prove you are doing more good than harm?

did you know that rebuilding indexes can have a massive negative impact on your performance?

are you using locally managed tablespaces (LMT)?

regards
Yogeeraj