1)   select a.tablespace_name,
       a.size_in_mb,
       (a.size_in_mb - b.free_in_mb) used_in_mb,
       b.free_in_mb,
       round(((a.size_in_mb - b.free_in_mb)*100/a.size_in_mb), 0) PCTUSED,
       b.max_free, c.max_next
from   ( select tablespace_name,
                round(sum(bytes)/1024/1024, 0) size_in_mb
         from   dba_data_files
         group  by tablespace_name ) a,
       ( select tablespace_name,
                round(sum(bytes)/1024/1024, 0) free_in_mb,
                max( bytes) / ( 1024 * 1024 ) max_free
         from   dba_free_space
         group  by tablespace_name ) b,
       ( select tablespace_name, max(next_extent) / ( 1024 * 1024 ) max_next
         from   dba_segments
         group  by tablespace_name ) c
where  a.tablespace_name = b.tablespace_name
and    a.tablespace_name = c.tablespace_name
order by pctused desc;


2)   select ddf.tablespace_name,round(total/1024/1024,3) ttlSize,round((total-nvl(freespace,0))/1024/1024,3) used, round((total-nvl(freespace,0))/total*100,2) usedpct
from
(select tablespace_name,sum(bytes) total from dba_data_files group by tablespace_name) ddf,
(select tablespace_name,sum(bytes) freespace from dba_free_space group by tablespace_name) dfs
where ddf.tablespace_name=dfs.tablespace_name(+)
order by  usedpct asc;