RE: Ora-1654 Unable to extend index on tablespace

From: Mohan, Ross <MohanR_at_STARS-SMI.com>
Date: Tue, 26 Jun 2001 12:12:31 -0700
Message-ID: <F001.00338AA9.20010626110652@fatcity.com>

you
have 4K blocksize.

Check
your indexes. How often do they extend? how big are they? the NEXT EXTENT size
may
not be appropriate. there is no pro forma way of knowing.... 
hth

Hannibal
-----Original
Message-----From: Mitchell
[mailto:mitchell_at_comnet.ca]Sent: Tuesday, June 26, 2001 11:42 AMTo: Multiple recipients of list ORACLE-LSubject: Re: Ora-1654 Unable to extend index on tablespace

  Hi DBAs

  Whenever I have the ora-1654, I
  will

alter index/table name deallocate unused
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 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="PADDING-RIGHT: 0px; PADDING-LEFT: 5px; MARGIN-LEFT: 5px; BORDER-LEFT: #000000 2px solid; MARGIN-RIGHT: 0px"> <DIV style="BACKGROUND: #e4e4e4; FONT: 10pt arial; font-color: black">From: <A title=lisa.koivu_at_efairfield.com href="mailto:lisa.koivu_at_efairfield.com">Koivu, Lisa To: <A title=ORACLE-L_at_fatcity.com href="mailto:'ORACLE-L_at_fatcity.com'">'ORACLE-L_at_fatcity.com' ; <A title=mitchell_at_comnet.ca href="mailto:'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 
      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 target=_blank 
      href="http://www.orafaq.com">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 - 14:12:31 CDT