SQL & PL/SQL

-- Hello All

We have a scenario like this :

Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE	11.2.0.2.0	Production
TNS for Linux: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production

Tables involved in the scenario:

-> hugetbl (apprx 1800000000 rows and 256 columns) -- partitioned by a date column -- month wise range partition, has apprx 60+ bitmap indexes
-> hugetblswap (temporary swap table which is always truncated and loaded in the process)

Step 1: Get rows from various source tables and insert (append hint) into hugetblswap table, calculate bitmap indexes on them (60+ bitmap indexes) -- Please note that this table is always truncated and inserted in the process.

Step 2: Swap the monthly partition of hugetbl with that of hugeswaptable INCLUDING INDEXES without validation (using exchange partitioning)

-- Going well till date..

But there was a new requirement to add few columns (15 more columns) to this hugetbl table.(which is also done on the swap table) and step 1 failed while creating bitmap index with an error saying :

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

I referred our own forum and fixed the error by altering the table and minimize rows_per_block.

So bitmap indexes were created now after fixing that .

Now due to this fix, hakan factor changed for my swap table and it can no longer be used for exchange partitioning with hugetbl
It shows error : ORA-14642: Bitmap index mismatch for tables in ALTER TABLE EXCHANGE PARTITION

I am pretty sure, this has been dealt by some of you before.. Expecting way out of this..... Any idea or suggestion would be well appreciated..

THANKS in advance!!!!

:)

Cheers,
Manik.