Problem: ======== You are trying to create a bitmap index on a table and you receive the following error message: ORA-28604 table too fragmented to build bitmap index (%s,%s,%s) Solution: ========= There are three possible solutions: 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.
In order to find the table name, you can apply the following workaround :
suppose you have : ORA-28604: table too fragmented to build bitmap index (197169946,136,136)
SQL> SELECT dbms_utility.data_block_address_block(197169946) "BLOCK", dbms_utility.data_block_address_file(197169946) "FILE" FROM dual; BLOCK FILE ---------- ---------- 37658 47 SQL> SELECT tablespace_name, segment_type, owner, segment_name FROM dba_extents WHERE file_id = 47 and 37658 between block_id AND block_id + blocks - 1;
now you have the table name.
Explanation: ============ This message is raised when some blocks exceed the internal limit of max rowslots.