Re: Ora-1654 Unable to extend index on tablespace

From: Mitchell <mitchell_at_comnet.ca>
Date: Tue, 26 Jun 2001 10:43:19 -0700
Message-ID: <F001.003383F7.20010626074139@fatcity.com>

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 <FONT face=Arial size=2>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 reportselect 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;
 

<FONT face=Arial

size=2>TABLESPACE_NAME                      
FREE_SPACE SEGCOUNT  TOTALOFSEGMENTS------------------------------ 
---------------- -------- 
----------------IDX_FINC_C70614                           

4,096       
1           

4,096IDX_FINC_C70614                          

24,576       
6         

147,456IDX_FINC_C70614                          

28,672       
1          
28,672<FONT face=Arial

size=2>IDX_FINC_C70614                         

364,544       
1         

364,544IDX_FINC_C70614                         

368,640       
2          737,280
<FONT face=Arial

size=2>IDX_FINC_C70614                       

1,396,736       
1       

1,396,736IDX_FINC_C70614                       

2,801,664       
1       

2,801,664                                                         
----------------TOTAL of 
SEGMENTS                                             

913,092,608
 
 
 

----- Original Message -----
<BLOCKQUOTE
style="BORDER-LEFT: #000000 2px solid; MARGIN-LEFT: 5px; MARGIN-RIGHT: 0px; PADDING-LEFT: 5px; PADDING-RIGHT: 0px">   <DIV
  style="BACKGROUND: #e4e4e4; FONT: 10pt arial; font-color: black">From:   <A href="mailto:lisa.koivu_at_efairfield.com"   title=lisa.koivu_at_efairfield.com>Koivu, Lisa   To: <A
  href="mailto:'ORACLE-L_at_fatcity.com'"
  title=ORACLE-L_at_fatcity.com>'ORACLE-L_at_fatcity.com' ; <A   href="mailto:'mitchell_at_comnet.ca'"
  title=mitchell_at_comnet.ca>'mitchell_at_comnet.ca'   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----- <FONT

    face=Arial size=1>From:   <FONT face=Arial 
    size=1>Mitchell [<A 
    href="mailto:SMTP:mitchell_at_comnet.ca">SMTP:mitchell_at_comnet.ca] 

    Sent:   <FONT face=Arial

    size=1>Monday, June 25, 2001 5:28 PM <FONT face=Arial 
    size=1>To:     <FONT face=Arial 
    size=1>Multiple recipients of list ORACLE-L <FONT face=Arial 
    size=1>Subject:        <FONT 
    face=Arial size=1>Re:  Ora-1654 Unable to extend index on 

    tablespace
        Dear DBAs
        I have a tablespace for index
    with 5 file with different size from <FONT face=Arial     size=2>500mb - 2000 mb. <FONT face=Arial     size=2>    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 <FONT face=Arial     size=2>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
    <FONT face=Arial

    size=2>SEGMENT                                                                 

    TYP BYTES  NEXT_EXTENT 
    EXTENTS    MAX_EXTENTS <FONT face=Arial

    size=2>----------------------------------------------------------------------- 
    --- ---------------- ------------ 

• ------------- 8,192 <FONT face=Arial size=2>C70614.FINC_INFO_ATTRIBUTE_080101_PK                                    IND 94,269,440    1,048,576       92          8,192 <FONT face=Arial size=2>C70614.FINC_INFO_ATTRIBUTE_090101_PK                                    IND 52,457,472    1,048,576       51          8,192
  • Please see the official ORACLE-L FAQ: <A href="http://www.orafaq.com" target=_blank>http://www.orafaq.com <FONT face=Arial size=2>-- Author: Mitchell   INET: mitchell_at_comnet.ca Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051 <FONT face=Arial size=2>San Diego, California        -- Public Internet access / Mailing Lists <FONT face=Arial size=2>-------------------------------------------------------------------- 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 <FONT face=Arial size=2>also send the HELP command for other information (like subscribing).

Received on Tue Jun 26 2001 - 12:43:19 CDT