OraFAQ Forum: Server Administration » Fragmented table list

Home » RDBMS Server » Server Administration » Fragmented table list (10.2.0.4 , Solaris 10)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Fragmented table list [message #458236]

Fri, 28 May 2010 01:07

lalitm_2003
Messages: 86
Registered: May 2005
Location: delhi

Member

Hi experts,
Last week, i was facing some problem with a report that is using 4 tables and doing some joins. On debugging, i found that one of the table was fragmented and that causes the performance issue for that report.
I came to know this when i try to build bitmap index on one of the column of that fragmented table. That happens with me early on too.
I resolved the issue by moving the table to another tablespace.

1) Can i find out the list of the tables that are fragmented in the schema using any sql.
2) Why bitmap indexes are throwing fragmented table error while the normal index on the same column can be created without any error.

Great thanx in advance for your input and knowledge sharing.

Regards
Lalit

Report message to a moderator

Re: Fragmented table list [message #458240 is a reply to message #458236]

Fri, 28 May 2010 01:21

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

1) Define "fragmented"
2) Post actual error

Regards
Michel

Report message to a moderator

Re: Fragmented table list [message #458244 is a reply to message #458240]

Fri, 28 May 2010 01:39

lalitm_2003
Messages: 86
Registered: May 2005
Location: delhi

Member

Thanks Michel for the prompt reply,
I got the below error when i try to create bitmap index.

CREATE BITMAP INDEX EP75.psAvoucher_line ON EP75.PS_VOUCHER_LINE(Business_unit);

ORA-28604: table too fragmented to build bitmap index (37782412,24,24)

I moved the table to some other tablespace and then i was able to create the bitmap index on the same column.
Meanwhile i was able to create the table normal index before moving the table to another tablespace.

Regards
Lalit

Report message to a moderator

Re: Fragmented table list [message #458249 is a reply to message #458244]

Fri, 28 May 2010 01:59

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

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).

B*Tree and Bitmap indexes are 2 different structures and so different requirements and restrictions, you cannot compare them.

Regards
Michel

Report message to a moderator

Re: Fragmented table list [message #458256 is a reply to message #458236]

Fri, 28 May 2010 04:39

lalitm_2003
Messages: 86
Registered: May 2005
Location: delhi

Member

Michel,
I already got this message/information on google. But my question still there.
Is there any query which generate list of the tables that are having this issue?
Because until unless we try to create bitmap index, we are not able to see any issue/information about this fragmentation.

Regards
Lalit

Report message to a moderator

Re: Fragmented table list [message #458260 is a reply to message #458256]

Fri, 28 May 2010 04:52

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

No, there is not.

Regards
Michel

Report message to a moderator

Re: Fragmented table list [message #458290 is a reply to message #458244]

Fri, 28 May 2010 08:27

joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA

Senior Member

lalitm_2003 wrote on Fri, 28 May 2010 02:39

Thanks Michel for the prompt reply,
I got the below error when i try to create bitmap index.
CREATE BITMAP INDEX EP75.psAvoucher_line ON EP75.PS_VOUCHER_LINE(Business_unit);

ORA-28604: table too fragmented to build bitmap index (37782412,24,24)

Wow, I'll have to admit that in all my years using Oracle I have never seen this error. And now that I just said that I am sure it will happen to me today.

Report message to a moderator

Re: Fragmented table list [message #458293 is a reply to message #458290]

Fri, 28 May 2010 08:40

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

That is having more than 24 different rows (regarding the index key) (in the OP's example) in a single block is quite rare.

Regards
Michel

Report message to a moderator

Previous Topic:	Question about viewing "tree tablespace"
Next Topic:	Oracle not available

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Dec 02 23:19:43 CST 2024