Re: empty block vs no. freelist blocks

Ben wrote:
> yes, I forgot to mention the datafile resize, thank you.
>
> I am still a little confused as to where the space is released to
> though. I thought even if I exported/dropped/imported the table that
> would just release the free space to be used by other objects within
> the tablespace. And if that space was in the middle of the datafile
> with other used segments surrounding it, the you can't necessarily
> resize the datafile to regain that OS space. You could only resize the
> datafile down to the hwm.

That is correct. There can be "holes" in between objects. That's why I like the tablespace map function so much, you can see right away what you must move around to get rid of the holes. The holes aren't so bad since Oracle implemented Locally Managed Tablespaces, since something else can fill them, and there generally aren't any worthwhile performance gains to be had by worrying about where things are in the tablespace. Jonathan and others demonstrate one minor fragmentation exception here:
http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:5549302357655

But really, unless you have some heavily batch oriented system or extremely volatile tables or major data migrations or way off defaults or old Dictionary Managed Tablespaces laying about, there isn't much to worry about if you just let Oracle handle everything. This is pretty informative: http://dizwell.com/main/content/view/64/87/

jg

--
@home.com is bogus.
'Make a sign and hang it above your monitor so you see it every day
"The job of the dba is not to back up the database, but to recover the
database." (To paraphrase Tim Gorman)' - Daniel Fink