Title: Creating A Tablespace Summary Report

Author: Renauld Chapellier, a software engineer for SES in Betzdorf, Luxembourg.

Here's an improvement to an earlier code sample, Creating a Tablespace Summary Report for Any Oracle Database.

Please note that the left to right precedence requires the usage of parentheses around (1024*1024). Otherwise you divide and
then multiply by the same number.

SET ECHO OFF

SPOOL TSFreeReport.DAT

TTITLE CENTER 'ASTRA DATABASE  -- Tablespace Summary - Freespace Report'  SKIP 2

SET NEWPAGE 0

COLUMN SUM(BYTES/(1024*1024)) FORMAT 999,999,999,999 HEADING TOTAL_SIZE_IN_MegaBytes
COLUMN TABLESPACE_NAME FORMAT A21
SELECT TABLESPACE_NAME, SUM(BYTES/(1024*1024)) FROM DBA_SEGMENTS
 GROUP BY TABLESPACE_NAME;

COLUMN SUM(BYTES/(1024*1024)) FORMAT 999,999,999,999 HEADING TOTAL_FREE_SPACE_IN_MegaBytes
COLUMN MAX(BYTES/(1024*1024)) FORMAT 999,999,999,999 HEADING LARGEST_FREE_SPACE_IN_MegaBytes
COLUMN TABLESPACE_NAME FORMAT A21

SELECT TABLESPACE_NAME, SUM(BYTES/(1024*1024)), MAX(BYTES/(1024*1024))
FROM SYS.DBA_FREE_SPACE GROUP BY TABLESPACE_NAME;

CLEAR BREAK
CLEAR COLUMN

SPOOL OFF;