ORA-28604

ORA-28604 Error Occurs When Creating a Bitmap Index on a Table [ID 119674.1]

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.