Re: Is space released after deletes?

Brian P. Mac Lean wrote:
>
> 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.

Let's talk about this. The text above is right according to the manual. But I have many customers where it's not happening.

For example, I have many tables where I do:

select count(*)
from table;

and it returns 0 (zero). It stands that the table has now rows. But this select takes minuts to run and the table uses many blocks of my database. After doing "truncate table", the same select starts running very quickly. (When I did the testes I was the only user connect to the Oracle).

It's happening since Oracle 6.0.33 until 7.1.6 (the last version I noticed this - I can't say anything on later versions).

Does anybody here know anything about this? Any bug? All pctused are Ok, etc. Nothing trivial.

Please, answer Cc; cnetto_at_softdes.com.br.

Regards,

-- 
Name: Carlos Netto                       e-mail: cnetto_at_softdes.com.br
Software Design Informatica                   Oracle System Integrator
Oracle BAP member. Softex/2000 member.     Campinas, Sao Paulo, Brazil