RE: ORA 28604 - table is too fragmented
- From: genegurevich@xxxxxxxxxxxxxxxxxxxxx
- To: oracle-l@xxxxxxxxxxxxx
- Date: Fri, 18 May 2007 16:42:55 -0500
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
Other related posts:
