Re: empty block vs no. freelist blocks

On 23 Mar 2006 10:13:48 -0800, "Ben" <balvey_at_comcast.net> wrote:

>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?

1 free space in dba_free_space are the blocks in the *tablespace* which are unused by any segment

2 Untouched blocks are above the HWM

3 Anything touched below the HWM which gets below PCTUSED

4 Yes. However, likely they are not going to be re-used EVER, as Oracle doesn't keep searching for the most 'empty' block. This is resolved with ASSM.

--
Sybrand Bakker, Senior Oracle DBA