Re: empty block vs no. freelist blocks

"Ben" <balvey_at_comcast.net> wrote in message news:1143137627.938299.25770_at_i40g2000cwc.googlegroups.com...
>I should have mentioned this is on a DMT, I guess that is evident from
> even posing the question though. I now have more concerns as I am
> finding tables with 4 Gig of blocks on the freelist and a couple
> hundred Meg as empty blocks. I know the correct answer is migrate to
> LMT with ASSM, but that is a little easier said than done on a 400G
> production database. For the time being I need to fix the problems at
> hand. ugnh.
>
> More questions though.
>
> What is the relationship between the segment hwm and the free space
> that shows in dba_free_space?
>
> Are empty blocks above or below segment hwm?
>
> Does the freelist include empty blocks?
>
> What would cause such a high number of blocks on the freelist? Large
> deletes?
>

Switching to ASSM is not automatically the correct answer. But if you want to do so, bear in mind that you need only move one table at a time, you don't have to "fix" the whole database.

Create one LMT, then move one table (and you'll have to rebuild its indexes - so you might want to add LMTs for rebuilding indexes as you move tables, of course).

It is possible that ASSM might have eliminated this odd empty space - but it is possible that it is a relic of a historic event such as a massive delete. If your subsequent work is a continuous cycle of inserts and deletes thereafter, it may be that the table simply never needs anything like that 4GB of available space, and a one-off rebuild may be sufficient to deal with the issue.

-- 
Regards

Jonathan Lewis
http://www.oracle.com/technology/community/oracle_ace/ace1.html#lewis

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html