Re: freelists and dba_free_space

On 13 Feb 2006 13:41:22 -0800, "Ben" <balvey_at_comcast.net> wrote:

>we are running Oracle 9.2.0.5 and I am fairly new at this DBA stuff. I
>need some clarification on space management.
>
>1) Where can I see the number of available blocks (or bytes) from the
>freelist?

you would need to use the dbms_space package for that. Tom Kyte probably has an example on http://asktom.oracle.com

>2) Is dba_free_space a view for the freelist?
No, it isn't. It contains the blocks that aren't allocated to any segment (ie table or index or cluster or whatever)
>3) When and how does the freelist get updated?
As soon as a block falls below pctused, the block is added to the freelist
>4) When and how does dba_free_space reflect blocks that become free?
dba_free_space contains only unallocated blocks. It would be updated when you drop a table, or truncate it.
>5) within dba_tables what does the avg_row_len tell me? bytes, used
>columns, characters?

bytes

>
>We just purged 2.5 million records from a table this weekend, and I
>can't seem to find that free space reflected anywhere. I guess I could
>see that possibly enough records from any one block were not removed to
>drop the blocks below pctused, I just don't think that is the case
>though. The data that was purged was data from 4 years ago, I would
>think it would have been located closely enough on the datafiles that
>quite a few blocks would have been freed.

They might have been put on the freelist, but 'purging' or deleting doesn't deallocate blocks. You would either create table temp as select * from source truncate table source
insert into source select * from temp
or
alter table source move tablespace .....

>
>Thanks for any info,
>Ben

--
Sybrand Bakker, Senior Oracle DBA