Re: empty block vs no. freelist blocks

"Ben" <balvey_at_comcast.net> wrote in message news:1143123493.986144.158010_at_j33g2000cwa.googlegroups.com...
> Running 9.2.0.5 in OEM. If you look at a table on the statistics tab it
> lists out 'Empty Blocks:' and 'No. of Freelist Blks:'. What is the
> difference in these two values? I've got a table with 9795 empty blocks
> but only 5 freelist blocks.
> Are the empty blocks ones that were grabbed when the table extended but
> have never had data in them, and the freelist blocks have data but are
> still available for inserts?

    Pretty close.

    Empty blocks are the blocks which have been reserved     for use, but have not yet been put into play.

    Oracle operates a 'high-water mark' for each object,     making a few of the empty blocks available for use by     moving a marker along the reserved space - typically     5 blocks at a time.

    Freelist blocks are blocks which either:

        have just become available because the highwater mark
        has been moved up
    or
        have previously had some data in them, but enough data
        has been deleted from them to make them available for
        new data to be inserted.  (See PCTUSED for further
        details).

    There have been lots of changes in interpretation and use     since ASSM (automatic segment space management)     appeared, though, so plenty of details I have omitted.

> Where can I find these values outside of OEM? dba_tables?
> Thanks for any help.
>

select

    blocks, empty_blocks, num_freelist_blocks from

    user_tables
where

    table_name = 'CHILD'
;

These figures will only be accurate immediately after you have collected statistics for a table, though. (The same applies to the OEM report, probably).

There is a dbms_space package that you can use to get accurate figures even when the statistics have not been collected.

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 Received on Thu Mar 23 2006 - 08:30:00 CST