ORA-28604 table too fragmented to build bitmap index

One of my data loading got delayed by 4 hours in data warehouse. Application team reported they have found some errors in their log. When i checked the application log I found that 6-7 bitmap index creation was failed due to error “ORA-28604 – table too fragmented to build bitmap index”. Error message clearly pointing towards the table fragmentation. I was searching this error on metalink I found the document which tells us that there are three possible solutions to this problem.

==============================================================

1)  Rebuild the table:
– Export/Import
– Create Table As Select

— OR —

2)  1- Identify the block (or blocks) that exceed the internal limit
of max rowslots and delete the rows that exceed the max rowslot.
2- Create the bitmap index.
3- Insert the rows again.

This second solution is slightly complicated.  You must:

1. select max(substr(rowid,10,4)) from <table>;
* This gives the current highest slot number found in the table.

2. select rowid from <table> where substr(rowid,10,4) = <result from (1)>;
* Returns all rowids that have this maximum; you can then use
various tools to determine the file/block that holds these rowids.
* Tools that may be used are odba/WebIV or
dbms_utility.make_data_block_address().

3. Extract the rows in this block to a temporary table and delete the
rows from the problem table.
* The block may contain a chained or migrated row piece.  If this
is the case (it should become evident if the create index still
fails), then you need to dump the block, determine where the
head rowpiece is, extract this to a temporary table, and
delete it.
* If the application stores rowids in user tables, then these
also need to be manually updated to keep application consistency.

4. Attempt to create the index.  If this still fails, then:
a) Check that the block is COMPLETELY empty by dumping it with the
BLOCKDUMP event.  If it is not, then you have missed some rows
(perhaps migrated, etc).
b) If the problem persists and all blocks that were returned by
step (2) have been verified as having no rows stored in them,
then it might be that the second highest slot number still
exceeds the internal limit; so go back to step (1).
* If you hit step 4(b) more than twice, then it might be worth
raising a bug to verify that the internal limit really is the
problem.

— OR —

3)  A workaround would be to import the table, run “alter table minimize records_per_block;”
and then recreate the index.

==============================================================

Before going to any of the above mentioned solution, I thought to first test with normal move command for table and then try to create the bitmap index.

alter table <table_name> move;

Post performing this table move activity, Bitmap index gets created.

Hope this will help you. 🙂

ORA-28604 table too fragmented to build bitmap index
Tagged on:

Leave a Reply