Home » SQL & PL/SQL » SQL & PL/SQL » table too fragmented to build bitmap index (67182634,104,104) (Oracle 9i, 9.2.0.1.0, RHEL 2.1 AS)
table too fragmented to build bitmap index (67182634,104,104) [message #397614] Mon, 13 April 2009 00:53 Go to next message
jimit_shaili
Messages: 231
Registered: June 2006
Location: India, Ahmedabad
Senior Member
Dear Friends

I have a table with partitions, when i create a bitmap index on partitioned table i got the following error.

"table too fragmented to build bitmap index (67182634,104,104)"

i have tried to export/import that table but the problem is not solved.table is too big to rebuild.Is there any other solution please provide me.

Regards

Jimit
Re: table too fragmented to build bitmap index (67182634,104,104) [message #397615 is a reply to message #397614] Mon, 13 April 2009 00:55 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Please, please, please Read & Follow Posting Guidelines above.
Go to the section labeled "Practice" & do as directed.

So what are you doing (SQL) & what are actual Oracle responses?
Re: table too fragmented to build bitmap index (67182634,104,104) [message #397617 is a reply to message #397614] Mon, 13 April 2009 01:03 Go to previous messageGo to next message
jimit_shaili
Messages: 231
Registered: June 2006
Location: India, Ahmedabad
Senior Member
Here i'm posting what i have done.

CREATE BITMAP INDEX PBL.BTMP_PBLWEIGHTSHEET_1 ON PBL.PBLWEIGHTSHEET
(COMPCODE, FINYEAR)
NOLOGGING
TABLESPACE INDX
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            MINEXTENTS       1
            MAXEXTENTS       2147483645
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
LOCAL           
NOPARALLEL;


and i got the following error

"ERROR at line 1:
ORA-28604: table too fragmented to build bitmap index (67182634,104,104)
"

hope this is enough to solve/understand the problem.

Regards

Jimit
Re: table too fragmented to build bitmap index (67182634,104,104) [message #397618 is a reply to message #397614] Mon, 13 April 2009 01:09 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>(Oracle 9i, 9.2.0.1.0, RHEL 2.1 AS)
Both are obsoleted & you might have better success at more current versions.

ENJOY!
Re: table too fragmented to build bitmap index (67182634,104,104) [message #397619 is a reply to message #397614] Mon, 13 April 2009 01:14 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
ORA-28604: table too fragmented to build bitmap index (%s,%s,%s)
 *Cause:  The table has one or more blocks that exceed the maximum number
          of rows expected when creating a bitmap index. This is probably
          due to deleted rows. The values in the message are:
          (data block address, slot number found, maximum slot allowed)
 *Action: Defragment the table or block(s). Use the values in the message
          to determine the FIRST block affected. (There may be others).

Try to export the table, drop it, reacreate it with a bigger PCTFREE and reimport the data.

Regards
Michel
Re: table too fragmented to build bitmap index (67182634,104,104) [message #397620 is a reply to message #397614] Mon, 13 April 2009 01:15 Go to previous messageGo to next message
jimit_shaili
Messages: 231
Registered: June 2006
Location: India, Ahmedabad
Senior Member
Means there is no solution for above versions,is there any alternate solution for that,my dev server with same configuration does not seems to have this problem.
so please suggest some patch or anything else.it my prod db and i needed badly.

Thanks and Regards

Jimit
Re: table too fragmented to build bitmap index (67182634,104,104) [message #397624 is a reply to message #397614] Mon, 13 April 2009 01:20 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
An alternative is to submit a SERVICE REQUEST to Oracle
Re: table too fragmented to build bitmap index (67182634,104,104) [message #397625 is a reply to message #397620] Mon, 13 April 2009 01:22 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
jimit_shaili wrote on Mon, 13 April 2009 08:15
Means there is no solution for above versions,is there any alternate solution for that,my dev server with same configuration does not seems to have this problem.
so please suggest some patch or anything else.it my prod db and i needed badly.

Thanks and Regards

Jimit

Quote:
Try to export the table, drop it, reacreate it with a bigger PCTFREE and reimport the data.

Regards
Michel
Re: table too fragmented to build bitmap index (67182634,104,104) [message #397627 is a reply to message #397614] Mon, 13 April 2009 01:24 Go to previous messageGo to next message
jimit_shaili
Messages: 231
Registered: June 2006
Location: India, Ahmedabad
Senior Member
we does not have any access to metalink, here i'm providing some more information which may help you guys, i have partition the table on financial year wise with list partition option, and i have moved to old finyear's partition to a another tablespace with data segment compress feature. so data compression is the major fact for this problem.

Regards

Jimit
Re: table too fragmented to build bitmap index (67182634,104,104) [message #397628 is a reply to message #397614] Mon, 13 April 2009 01:31 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
Good luck!
I hope you can hold your breath while you extract your head from you A**
Re: table too fragmented to build bitmap index (67182634,104,104) [message #397629 is a reply to message #397614] Mon, 13 April 2009 01:38 Go to previous messageGo to next message
jimit_shaili
Messages: 231
Registered: June 2006
Location: India, Ahmedabad
Senior Member
Is data uncompress solve my problem, and how big pctfree i have to use.right now it is 10 and pctused is 40.

Please reply

Regards
Re: table too fragmented to build bitmap index (67182634,104,104) [message #397630 is a reply to message #397614] Mon, 13 April 2009 01:42 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>Is data uncompress solve my problem, and how big pctfree i have to use.right now it is 10 and pctused is 40.
42

Re: table too fragmented to build bitmap index (67182634,104,104) [message #397631 is a reply to message #397627] Mon, 13 April 2009 01:43 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Without Metalink access you can't even get patches.
I recommend you to move to 11g (and contract a valid license).

Quote:
i have partition the table on financial year wise with list partition option

You can try to exchange the data of the offending partition with a table then do what I said above and reexchange the table and partition but if the problem comes from data compression you are out of luck.

Regards
Michel

[Updated on: Mon, 13 April 2009 01:44]

Report message to a moderator

Re: table too fragmented to build bitmap index (67182634,104,104) [message #397632 is a reply to message #397629] Mon, 13 April 2009 01:46 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
jimit_shaili wrote on Mon, 13 April 2009 08:38
Is data uncompress solve my problem, and how big pctfree i have to use.right now it is 10 and pctused is 40.

Please reply

Regards

Enough to no more have the error => trials and errors method.

Regards
Michel

[Updated on: Mon, 13 April 2009 02:14]

Report message to a moderator

Re: table too fragmented to build bitmap index (67182634,104,104) [message #397639 is a reply to message #397614] Mon, 13 April 2009 02:12 Go to previous message
jimit_shaili
Messages: 231
Registered: June 2006
Location: India, Ahmedabad
Senior Member
Thanks Michel/Blackswan for your valuable support,i import the two days old export and that will solve my problem.i reinsert the latest data from duplicate table.
further should i compress any more table with data segment compress feature or leave it to oracle.

Thanks

Regards
Previous Topic: order by month name
Next Topic: please help to optimize my sql
Goto Forum:
  


Current Time: Fri Dec 09 23:31:20 CST 2016

Total time taken to generate the page: 0.20166 seconds