Returns the percentage usage of disk space of an Oracle XE 11g database that has 11GB max space available

gen_xe_space_percent.fnc

  CREATE OR REPLACE FUNCTION gen_xe_space_percent RETURN NUMBER IS
    --
    l_return      NUMBER := 0;
    l_xe_max_size NUMBER := 11811160064;
    l_used_size   NUMBER := 0;
    --
    CURSOR l_cur_dba_ts_size IS
      SELECT a.tablespace_name ts,
             a.file_id,
             SUM(b.bytes) / COUNT(*) - SUM(a.bytes) used
        FROM dba_free_space a,
             dba_data_files b
       WHERE a.tablespace_name = b.tablespace_name
         AND a.file_id = b.file_id
         AND a.tablespace_name NOT IN ('SYSTEM',
                                       'SYSAUX',
                                       'UNDOTBS1')
       GROUP BY a.tablespace_name,
                a.file_id
       ORDER BY 1;
    --
  BEGIN
    --
    FOR l_rec IN l_cur_dba_ts_size LOOP
      l_used_size := l_used_size + l_rec.used;
    END LOOP;
    --
    l_return := round(l_used_size / l_xe_max_size * 100,
                      2);
    --
    RETURN l_return;
    --
  END gen_xe_space_percent;