RE: ORA 28604 - table is too fragmented

  • From: genegurevich@xxxxxxxxxxxxxxxxxxxxx
  • To: "Jacques Kilchoer" <Jacques.Kilchoer@xxxxxxxxx>
  • Date: Mon, 21 May 2007 11:23:30 -0500


The reason I am executing these commands is to force the bitmap index to
complete. I am not sure how that
works internally with oracle. My goal is not specifically to minimize
records_per_block, though

thank you

Gene Gurevich

             <Jacques.Kilchoer                                          To 
   >               <genegurevich@xxxxxxxxxxxxxxxxxxxxx 
                                       >, <oracle-l@xxxxxxxxxxxxx>         
             05/18/2007 07:52                                           cc 
                                       RE: ORA 28604 - table is too        

This is pure guesswork on my part, but if you want to minimize the number
of records per block, could you do it be re-creating the table with a high

-----Message d'origine-----
De : oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
De la part de genegurevich@xxxxxxxxxxxxxxxxxxxxx
Envoyé : vendredi, 18. mai 2007 14:43
À : oracle-l@xxxxxxxxxxxxx
Objet : RE: ORA 28604 - table is too fragmented

I did  a little bit more testing and here is what I have now:

- a partitioned table called T1 and an exchange table XCHG_T1
- alter table XCHG_T1 minimize records_per_block fixes the problem with
building the bitmap indices
- this however changes the value of the hakan factor on XCHG_T1 table and
now it is different from
   the value of hakan factor on the T1 table
- now alter table t1 exchange partition with table xchg_t1 fails
- alter table t1 minimize records_per_block resets the hakan factor on this
table to the value different
   from the value on the XCHG_T1 table, therefore my exchange command still

I don't know whether there is a way to fix both of these two issues -
bitmap index build and exchange.

thank you

Gene Gurevich

             "Bobak, Mark"
   >                                                To
             Sent by:                  <genegurevich@xxxxxxxxxxxxxxxxxxxxx
             oracle-l-bounce@f         >, <oracle-l@xxxxxxxxxxxxx>

             05/18/2007 12:33          RE: ORA 28604 - table is too
             PM                        fragmented

             Please respond to


Not tested, and I'm not sure there's a quick, easy test case for this one,
but, after moving data out of the problem table, you may try this:
1.)  Figure how many rows per block you want.
2.)  Insert that number of rows into the table.  insert into problem_table
select * from temp_table where rownum <= number_of_desired_rows_per_block;
3.)  Verify that all those rows are in the same block.
4.)  alter table problem_table minimize records_per_block;  --This sets the
Hakan factor, a limit on the max. number of rows/block the table will ever
5.)  delete from problem_table;  --don't truncate, that will reset the
Hakan factor.
6.)  insert into problem_table select * from temp_table;

Mark J. Bobak
Senior Oracle Architect

"There are 10 types of people in the world:  Those who understand binary,
and those who don't."

-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of
Sent: Friday, May 18, 2007 12:03 PM
To: oracle-l@xxxxxxxxxxxxx
Subject: ORA 28604 - table is too fragmented

Hi all:

I am running oracle and am getting the following error when
building bitmap indices:


I have found the note 119674 and applied the first solution that was
recommended - rebuild the table through export/import - I have copied the
data from the table into another table, dropped and recreate my table and
inserted the data back. After that I tried to rebuild the indices and
received the same error. I looked at the second solution and it looks like
a reduced version of the first one.

I have temporarily rebuilt all bitmap indices as non-bitmap to allow the
user testing to go on, but I would still like to have the bitmap indices
instead. Does anyone have a better solution for this?

thank you

Gene Gurevich





Other related posts: