| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: ORA 28604 - table is too fragmented
Jacques,
This seems to work. Thank you very much ! I have been able to build all indices without problems and run exchange with a partitioned table with no issues (so far) after increasing the pctfree parameter
thank you
Gene Gurevich
"Jacques
Kilchoer"
<Jacques.Kilchoer To
@quest.com> <genegurevich_at_discoverfinancial.com
>
05/21/2007 03:54 cc
PM <oracle-l_at_freelists.org>
Subject
RE: ORA 28604 - table is too
fragmented
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_at_discoverfinancial.com
[mailto:genegurevich_at_discoverfinancial.com]
Envoyé : lundi, 21. mai 2007 09:24
À : Jacques Kilchoer
Cc : oracle-l_at_freelists.org
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_at_discoverfinancial.com
>, <oracle-l_at_freelists.org>
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_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org]
De la part de genegurevich_at_discoverfinancial.com
Envoyé : vendredi, 18. mai 2007 14:43
À : oracle-l_at_freelists.org
Objet : RE: ORA 28604 - table is too fragmented
I did a little bit more testing and here is what I have now:
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_at_il.pr
oquest.com> To
Sent by: <genegurevich_at_discoverfinancial.com
oracle-l-bounce_at_f >, <oracle-l_at_freelists.org>
reelists.org cc
Subject
05/18/2007 12:33 RE: ORA 28604 - table is too
PM fragmented
Please respond to
Mark.Bobak_at_il.pro
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_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of genegurevich_at_discoverfinancial.com Sent: Friday, May 18, 2007 12:03 PM To: oracle-l_at_freelists.org 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 -- http://www.freelists.org/webpage/oracle-l -- http://www.freelists.org/webpage/oracle-l -- http://www.freelists.org/webpage/oracle-l -- http://www.freelists.org/webpage/oracle-lReceived on Tue May 22 2007 - 11:16:16 CDT
 |
 |