Re: Ora-1654 Unable to extend index on tablespace

[Mitchell]

Title: RE: Ora-1654 Unable to extend index on tablespace

Hi DBAs   Whenever I have the ora-1654, I will  1. alter index/table name deallocate unused 2. alter tablespace name coalescs; 3. run querys to check dba_free_space and dba_data_files   There are total 140 indexes on this tablespace with setting init 1024k and next 1024k. I got confused now that for message 'unable to extend by 256'. What is mean for 256 here? The free space(byte)  must be over 1024k here to avoid ora-1654  for each of 140 index segments?   Thanks in advance.   Mitchell       This the query I run today.  I only take first few lines and last few lines.   compute sum LABEL 'TOTAL of SEGMENTS' of totalofsegments on report select tablespace_name, bytes free_space, count(bytes) segcount,        (bytes * count(bytes)) totalofsegments   from dba_free_space  where tablespace_name=UPPER('&1')  group by tablespace_name, bytes  order by tablespace_name, bytes;   TABLESPACE_NAME                      FREE_SPACE SEGCOUNT  TOTALOFSEGMENTS ------------------------------ ---------------- -------- ---------------- IDX_FINC_C70614                           4,096        1            4,096 IDX_FINC_C70614                          24,576        6          147,456 IDX_FINC_C70614                          28,672        1           28,672 IDX_FINC_C70614                         364,544        1          364,544 IDX_FINC_C70614                         368,640        2          737,280 IDX_FINC_C70614                       1,396,736        1        1,396,736 IDX_FINC_C70614                       2,801,664        1        2,801,664                                                          ---------------- TOTAL of SEGMENTS                                             913,092,608       ----- Original Message ----- From: Koivu, Lisa To: '[EMAIL PROTECTED]' ; '[EMAIL PROTECTED]' Sent: Tuesday, June 26, 2001 8:08 AM Subject: RE: Ora-1654 Unable to extend index on tablespace Mitchell have you tried coalescing your tablespace?  How big are your extents? -----Original Message----- From:   Mitchell [SMTP:[EMAIL PROTECTED]] Sent:   Monday, June 25, 2001 5:28 PM To:     Multiple recipients of list ORACLE-L Subject:        Re:  Ora-1654 Unable to extend index on tablespace     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).