RE: ORA 28604 - table is too fragmented
- From: genegurevich@xxxxxxxxxxxxxxxxxxxxx
- To: "Jacques Kilchoer" <Jacques.Kilchoer@xxxxxxxxx>
- Date: Tue, 22 May 2007 11:16:16 -0500
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@xxxxxxxxxxxxxxxxxxxxx
>
05/21/2007 03:54 cc
PM <oracle-l@xxxxxxxxxxxxx>
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@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
Other related posts:
