Server Administration :: How To Reclaim Wasted Space Thoroughly

In one of our Data warehousing DB, even though, all the tablespaces' space should keep on at least 1 month, but our leader want us to estimate how much space can free up with db method.

I have referenced

<Administrator Guide> - Reclaiming Wasted Space

[URL].....

I have several questions on reclaim space:

1. It seems that segment adviser give a really cool view to know which segment should be shrink and how much size will free up after shrink. But actually, this need a very a job or manually do this. I have once heard about some query from can estimate this :

A script from MOS, but actually I found this it's not very accurate with segment adviser. This script should report the real space the table occupy, but after shrink space, the space doesn't free up.

SELECT TABLE_NAME , (BLOCKS *8192 / 1024/1024 ) - (NUM_ROWS*AVG_ROW_LEN/1024/1024)
"Data lower than HWM in MB" FROM DBA_TABLES WHERE UPPER(owner) =UPPER('&OWNER') order by 2 desc;

Also there is a script evaluate the tablespace fragments from some people:

SELECT tablespace_name,
SQRT (MAX (blocks) / SUM (blocks))
* (100 / SQRT (SQRT (COUNT (blocks))))
FSFI
FROM dba_free_space
GROUP BY tablespace_name
ORDER BY 1;

if the value is very low, we can coalesce the tablespace. But after I coalesce the tablespace, I can't see any space free up.

From <Concepts>:
Quote:Coalescing extents is not necessary in
locally managed tablespaces, because all contiguous free space is available for
allocation to a new extent regardless of whether it was reclaimed from one or more
extents.

2. is there any good way to estimate how much space we should free up? ( After free up the space , dba_free_space should see this result)