Mitchll,
The index was trying to extend according to the next_extend parameter in
dba_segments for that object. From your message I can infer that the largest of
the available free space chunks in that tablespace was short 256 bytes. Try the
following query to tell you what is going on in the tablespace:
select file_id, block_id, bytes
from dba_free_space
where tablespace_name = '<your tablespace>'
order by 1,2;
This will give you a list of the available free segments that the index can use.
If none of those extents is equal to or greater than the next_extent parameter
for the index then it will not extend. Setting auto extend on only allowed the
file to extend itself until it either ran out of disk space or filed the
requirement.
Dick Goulet
____________________Reply Separator____________________
Author: "Mitchell" <[EMAIL PROTECTED]>
Date: 6/25/2001 1:28 PM
Dear DBAs
I have a tablespace for index with 5 file with different size from
500mb - 2000 mb.
Total tablespace size is 6g and used 5317mb abote 86.13% usage.
I got the error today.
ora-1654 unable to extend indx sechma.indexname by 256 in tablespace
tablespacename.
The following is the query I got for the tablespace .
We can see the index takes 92 extents and maxextends setting is 8192.
I then set autoextend on a datafile then error is gone.
What is the reason to cause ora-1654 even there are 700mb space avai. I also
checked the tablespace and index setting with both have next extend 1024k,
maxextend 8092.
Mitchll
SEGMENT TYP
BYTES NEXT_EXTENT EXTENTS MAX_EXTENTS
----------------------------------------------------------------------- ---
---------------- ------------ -------- -------------
8,192
C70614.FINC_INFO_ATTRIBUTE_080101_PK IND
94,269,440 1,048,576 92 8,192
C70614.FINC_INFO_ATTRIBUTE_090101_PK IND
52,457,472 1,048,576 51 8,192
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Mitchell
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author:
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
