RE: Ora-1654 Unable to extend index on tablespace

From: Christopher Spence <cspence_at_FuelSpot.com>
Date: Mon, 25 Jun 2001 14:08:28 -0700
Message-ID: <F001.00337564.20010625140233@fatcity.com>

Check dba_free_space for the largest chunk available. Also, note when it says temp segment, it has nothing to do with sorting or temporary segments. This merely refers to the segment it is creating prior to making it permanent segment.

"Walking on water and developing software from a specification are easy if both are frozen."

Christopher R. Spence
Oracle DBA
Fuelspot

-----Original Message-----
Sent: Monday, June 25, 2001 5:28 PM
To: Multiple recipients of list ORACLE-L

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: Christopher Spence
INET: cspence_at_FuelSpot.com

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