Re: Is space released after deletes?

Yuri Ludevig wrote:
>
> I can't get a definite answer if Oracle reuses space after deletes. I ran
> analyze before and after deletes (I deleted all rows form the table) and
> statistics didn't seem to change (number of empty blocks, extents, bytes
> used, etc). Anybody out there knows for sure if you have to do
> export/import or Oracle takes care of all the holes?
> Thanks,
> Yuri in NY

For a table, Oracle will only reuse the deleted row space once the blocks freespace/percent used has fallen below the "pctused" value. Once it does the blocks address is inserted into the beginning of the freelist chain for the table so that it will be reused before a new/never used block is.

For indexes, Oracle will only reuse deleted space if all the index values stored in a leaf block are deleted. I don't know about branch blocks being reused but I quess it would live by the same rule.

As always dropping and recreating tables/indexes will reuse space since the deleted rows are not carried along in these operations.

As to why the analyze command shows invalid information, I have not had this happen. It is always wise to include your O/S platform and Oracle version information in any posting, along with cut/copy/paste examples of what you are seeing.

-- 
                       \\|//
                       (0-0)
           +-----oOO----(_)-----------+
           | Brian P. Mac Lean        |
           | Database Analyst         |
           | brian.maclean_at_teldta.com |
           | http://www.teldta.com    |
           +-------------------oOO----+
                      |__|__|
                       || ||
                      ooO Ooo