Yes, I understood that, but if one way to allow the creation of the bitmap index is having less records per block (wasn't that the whole point of resetting the hakan factor?), you can accomplish that goal with pctfree. -----Message d'origine----- De : genegurevich@xxxxxxxxxxxxxxxxxxxxx [mailto:genegurevich@xxxxxxxxxxxxxxxxxxxxx] Envoyé : lundi, 21. mai 2007 09:24 À : Jacques Kilchoer Cc : oracle-l@xxxxxxxxxxxxx Objet : RE: ORA 28604 - table is too fragmented Jacques, 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" <Jacques.Kilchoer To @quest.com> <genegurevich@xxxxxxxxxxxxxxxxxxxxx >, <oracle-l@xxxxxxxxxxxxx> 05/18/2007 07:52 cc PM Subject RE: ORA 28604 - table is too fragmented 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 PCTFREE? -----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 works. 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" <Mark.Bobak@xxxxx oquest.com> To Sent by: <genegurevich@xxxxxxxxxxxxxxxxxxxxx oracle-l-bounce@f >, <oracle-l@xxxxxxxxxxxxx> reelists.org cc Subject 05/18/2007 12:33 RE: ORA 28604 - table is too PM fragmented Please respond to Mark.Bobak@xxxxxx quest.com Gene, 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 have. 5.) delete from problem_table; --don't truncate, that will reset the Hakan factor. 6.) insert into problem_table select * from temp_table; -Mark -- Mark J. Bobak Senior Oracle Architect ProQuest/CSA "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 genegurevich@xxxxxxxxxxxxxxxxxxxxx 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 9.2.0.4 and am getting the following error when building bitmap indices: ORA-28604: TABLE TOO FRAGMENTED TO BUILD BITMAP INDEX (25206487,40,40) 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 -- //www.freelists.org/webpage/oracle-l -- //www.freelists.org/webpage/oracle-l -- //www.freelists.org/webpage/oracle-l -- //www.freelists.org/webpage/oracle-l