SELECT tablespace_name, max_blocks, count_blocks, sum_free_blocks
, to_char(100*sum_free_blocks/sum_alloc_blocks, '99.99') || '%'
AS pct_free
FROM ( SELECT tablespace_name
, sum(blocks) AS sum_alloc_blocks
FROM dba_data_files
GROUP BY tablespace_name
)
, ( SELECT tablespace_name AS fs_ts_name
, max(blocks) AS max_blocks
, count(blocks) AS count_blocks
, sum(blocks) AS sum_free_blocks
FROM dba_free_space
GROUP BY tablespace_name )
WHERE tablespace_name = fs_ts_name
SQL> /
TABLESPACE_NAME MAX_BLOCKS COUNT_BLOCKS SUM_FREE_BLOCKS PCT_FRE
------------------------------ ---------- ------------ --------------- -------
TBSDATA 105475 104 1368832 76.39%
TBSINDEX 101860 21 750965 65.19%
RBS 41899 262 109518 85.56%
SYSTEM 16795 8 16866 65.88%
TEMP 33099 54 60394 94.37%
For this query, below which level should the PCT_FRE(for all the tablespaces) get on a production system for any one to be alarmed?
