Re: empty block vs no. freelist blocks

>If I move/rebuild these tables and indexes, would that space be
>released to the tablespace or back to the datafile as unused space?
>What steps would be needed to get that space back as actual OS file
>space?

The tablespace consists of datafiles, the datafiles won't shrink unless you tell them to with the RESIZE command, the tablespace will have more space available. To get the OS space back, you need to shrink the datafiles - Oracle won't let you shrink to where something exists in the space. RESIZE is part of the ALTER DATABASE DATAFILE command.

OEM has a display option called "Show Tablespace Map" which I've found useful for visualizing where stuff ends in the tablespace/datafile. Databases --> your database --> storage --> tablespaces --> right click on the tablespace and select the Show Tablespace Map. When it gets around to coming up, you can hover over segments to get more information. Once you see how much free space there is at the end of the desired datafiles, you can expand the "datafiles" (under storage, not under the tablespaces) and put in the desired file size. Since I'm one who believes the job isn't done right unless it is repeatable (and I often do things like this on test db's first), notice you can show the SQL it is using and you can cut and past that into scripts without actually doing it. Also note what gets written into the alert log.

Alternatively, you can figure out the SQL to create a segment list (highly recommended as a learning experience for new DBA's), or just be a neandertal and put in random resize commands in sqlplus until it shrinks.

Oh yeah, be sure you have good backups before doing any of this. All manner of klutziness can be overlooked with quick restores.

jg

--
@home.com is bogus.
The business model of the new millennium:
http://www.signonsandiego.com/uniontrib/20060324/news_1b24patriot.html