Call (800) 766-1884 for Oracle support & training
Free Oracle Tips

Oracle Consulting Support
Oracle Upgrades
Use New Oracle Features
Oracle Replication Support
Oracle Training
Remote Oracle DBA
System Documentation
Oracle Tips
Oracle Performance

Free Oracle Tips



BC Oracle tuning

Oracle training

Oracle support

Remote Oracle


Donald K. Burleson

Oracle Tips

Predict indexes for rebuilding


By John Palinski


One of the DBA's chores is to monitor tablespace sizes to ensure that adequate space is available for the objects that reside within the tablespace.  A second task is to monitor index sizes and rebuild the indexes when they get too large or fragmented.  See the latest consensus on index rebuilding.


Indexes have a tendency to grow as they are being updated in a different proportion to the referenced data. This is especially true of bitmap indexes that can grow very rapidly when updated.  Besides the space savings, smaller sized indexes increase performance by reducing the disk area needed to identify rows.  For these reasons it is a good habit to monitor the size of tablespaces and the indexes rebuilding them when necessary. 


Indexes are also often kept in their own tablespaces segregated from the tables they reference.  This allows the DBA to monitor growth separately from the data.  If you have tables that grow consistently, but at a slow steady space, you may want to keep a smaller amount of free space than you would with a tablespace populated by indexes.  Placing indexes into their own tablespace also allows you to target them based upon the object need. 


Normally, when I rebuild a tablespaces index I generate a DDL script using SQL*Plus and the following script:


select 'alter index '||owner||'.'||index_name||' rebuild;'

from dba_indexes

where tablespace_name = 'INDEX_TABLESPACE';


This statement produces a list of DDL commands that rebuild the indexes in place.  Normally, the generated script works exceedingly well. However, in some occasions the target index is larger that the tablespace free space.  When this occurs, you will get the following error:


ORA-1652: unable to extend temp segment by 3168 in tablespace



This error occurs because Oracle must build the new index before the old one is removed and the tablespace does not have the free space for a new version and an old version.  The DBA then has two choices:


   1. Drop the existing index and recreate it.

   2. Rebuild the index in a different tablespace with adequate free space and rebuilding it again in the original tablespace after the original index version is dropped.


The former option of dropping the index may cause applications and users response problems.  It also requires the DBA to create a new DDL index statement.  Not a difficult task, but not one a busy DBA relishes.  For these two reasons I generally choose option 2. 


The only drawback to this option is that the index is created twice requiring extra server work.  Having the computer perform a little more work is far superior then me performing some work.  However, I don't like having to re-execute commands because they failed.  For this reason I developed a simple script for rebuilding all tablespaces indexes that eliminates the Ora-1652 error. 


It performs the following:


  1. Prompts the user for the target tablespace

  2. Determines the tablespace's free space available for the rebuilt index

  3. Determines the tablespace's indexes

  4. Determines the size of each index

  5. Compares the index size to the available free space.  If the index size is less than the tablespace size, the index is rebuilt in the same tablespace. If the index is larger than the available free space the index is first rebuilt in a development tablespace called Reorg_index_area.  The index is then rebuilt in the original tablespace. The Reorg_index_area tablespace is an empty tablespace created by the DBA to temporarily hold rebuilt indexes.


  6. Messages displaying the original tablespace free space, the indexes

rebuilt, and the final tablespace free space.


The script that rebuilds the indexes is displayed below.  It is an anonymous block procedure that will prompt you for the target tablespace name.


set serveroutput on


cursor freespace_cursor is select sum(bytes)/1024/1024 freespace

                           from dba_free_space

                           where tablespace_name =


                           group by tablespace_name;

  freespace_value     number; 

  q                   varchar2(60);      


  open freespace_cursor;

  fetch freespace_cursor into freespace_value;

  close freespace_cursor;

  dbms_output.put_line ('Tablespace '||'&&target_tablespace'||'

freespace is now '||freespace_value); for z in (select a.owner,

index_name, segment_name, 'Index', index_type, uniqueness, compression,

                 a.tablespace_name, mbytes_used

          from dba_indexes a

            join (select owner, segment_name, sum(bytes)/1024/1024


                  from dba_extents

            where tablespace_name = upper('&target_tablespace')

            group by owner, segment_name) b on (a.owner = b.owner

                                      and a.index_name = b.segment_name)

    order by mbytes_used)


 if z.mbytes_used < freespace_value then

    execute immediate 'alter index '||z.owner||'.'||z.segment_name||'

rebuild ';  else

    execute immediate 'alter index '||z.owner||'.'||z.segment_name||'

rebuild tablespace reorg_index_area';

    dbms_output.put_line ('Rebuild of index in reorg_index_area

'||z.owner||'.'||z.segment_name||' is complete');

    execute immediate 'alter index '||z.owner||'.'||z.segment_name||'

rebuild tablespace '||z.tablespace_name;

    dbms_output.put_line ('Rebuild of index in original tablespace is

'||z.owner||'.'||z.segment_name||' is complete');

  end if;

  dbms_output.put_line ('Rebuild of index

'||z.owner||'.'||z.segment_name||' is complete');

  open freespace_cursor;

  fetch freespace_cursor into freespace_value;

  close freespace_cursor;

  dbms_output.put_line ('Tablespace '||'&&target_tablespace'||'

freespace is now '||freespace_value); end loop; end; / undefine



The code block prompts the user for the name of the target tablespace, Index_tablespace.  Also notice that the script built several indexes directly into the Index_tablespace tablespace.  Two of the indexes were too large for the free space and were rebuilt into the Reorg_index_area tablespace and again into the Index_tablespace tablespace. 


The net effect is rebuilt indexes with no DBA intervention. The only thing the DBA needs to do is launch the script and identify the tablespace.  Everything else is automatic.


See these related notes on Oracle index rebuilding:



Oracle Index rebuilding - Why when how


Oracle index rebuilding


Important Oracle 10g bug in index rebuilding


Oracle myth busters index rebuild disk io wait event analysis


Identifying which Oracle Indexes to Rebuild


Predict Oracle table index benefit from rebuild reorganization


Oracle tuning: Blocksize and index tree structures


Oracle index rebuild command


Oracle indexes rebuild distinct keys


Identifying Which Indexes to Rebuild Table of Contents





Oracle Consulting


Oracle performance tuning software 

Oracle performance tuning book


Oracle performance Tuning 10g reference poster
Oracle training in Linux commands
Oracle training Excel
Oracle training & performance tuning books

Copyright © 1996 -  2014 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.