An article on this site flagged as new entitled

LSS "How To" Series: Calculating Tablespace and Table Byte Sizes by David Nishimoto:

Has an incorrect piece of SQL.

The author suggests in this article that to find Tablespace byte sizes use the following SQL

Tablespace Byte Sizes

select distinct a.tablespace_name,
sum(a.bytes) as free_bytes,
sum(b.BYTES) as Total_Bytes
from dba_free_space a, dba_data_files b
Where a.tablespace_name = b.tablespace_name group by a.tablespace_name


Ohh no, big mistake.

Are these articles moderated, if so who moderates them because that is a query nearly all DBA's should know about.

Example:

SALES tablespace is made up of two datafiles both of 5Mbytes.
The SALES tablespace has some free space one 2Mbytes the other 3 Mbytes.

Correctly we would have 5Mbytes of free space and 10Mbytes total space.

The query above would report 10Mbytes of free space and 20Mbytes of total space.

You can't join dba_free_space and dba_data_files as above.

Try the following:

SELECT dfs.tablespace_name tablespace_name,
ddf.total_size total_size,
ddf.total_size - dfs.total_free total_used,
dfs.total_free total_free,
(ddf.total_size - dfs.total_free) / ddf.total_size * 100 cap,
dfs.total_chunks total_chunks,
dfs.largest_chunk largest_chunk
FROM (SELECT a.tablespace_name,
SUM(a.bytes) / 1024 / 1024 total_free,
COUNT(a.bytes) total_chunks,
MAX(a.bytes) / 1024 / 1024 largest_chunk
FROM dba_free_space a
GROUP BY a.tablespace_name) dfs,
(SELECT b.tablespace_name,
SUM(b.bytes) / 1024 / 1024 total_size
FROM dba_data_files b
GROUP BY b.tablespace_name) ddf
WHERE dfs.tablespace_name = ddf.tablespace_name
ORDER BY dfs.tablespace_name

Did look to see if I could report this error to someone but couldn't find a feedback e-mail address, anyone know of one?

David Nashimoto, shame on you.