Re[2]: Ora-1654 Unable to extend index on tablespace

From: <dgoulet_at_vicr.com>
Date: Mon, 25 Jun 2001 13:56:08 -0700
Message-ID: <F001.0033757E.20010625140234@fatcity.com>

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" <mitchell_at_comnet.ca>
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: mitchell_at_comnet.ca

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: ListGuru_at_fatcity.com (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: dgoulet_at_vicr.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------