Sin duda alguna el pegarse con los tablespaces es la tarea más básica y la mas habitual de un DBA.

Para los que siguen teniendo los tablespaces en modo «manual» aqui hay varias consultas útiles
Tamaño y porcentaje de ocupación

select
 t.tablespace_name,
 round(t.kbtotal/1024,2) "Total MB",
 round(l.kblibre/1024,2) "MB Libres",
 100-(round(l.kblibre/t.kbtotal,2)*100)||'%' Ocupado
  from
  (select tablespace_name, sum(bytes)/1024 kbtotal
  from dba_data_files group by tablespace_name) t ,
  (select tablespace_name, sum(bytes)/1024 kblibre
  from dba_free_space group by tablespace_name) l
  where  t.tablespace_name=l.tablespace_name
 order by "OCUPADO" desc;

Uso de tablespaces temporal

select
 t2."TempTotal" "TempTotal (Mb)",
 t1."TempUsed" "TempUsed (Mb)",
 t2."TempTotal" - t1."TempUsed" "TempFree (Mb)"
 from (select nvl(
         round(sum(tu.blocks * tf.block_size)
                  / 1024 / 1024, 2), 0) "TempUsed"
      from v$tempseg_usage tu, dba_tablespaces tf
          where tu.TABLESPACE = tf.tablespace_name) t1,
          (select round(sum(bytes)
                     / 1024 / 1024, 2) "TempTotal"
           from dba_temp_files) t2
 

Tablespaces a mas del 80%  o con menos de 2 Gb libres no extensibles ni UNDOS

select  *  
  from
 (select 100-(round(l.kblibre/t.kbtotal,2)*100) Ocupado ,
         to_char(round(l.kblibre/1024,0)) Mb ,
           l.tablespace_name
      from 
   (select tablespace_name, sum(bytes)/1024 kbtotal 
             from dba_data_files
               group by tablespace_name) t ,
   (select tablespace_name, sum(bytes)/1024 kblibre
          from dba_free_space 
          group by tablespace_name) l where 
          (t.tablespace_name=l.tablespace_name
           and l.tablespace_name not in 
               (select distinct(tablespace_NAME)
                   from dba_data_files
                     where autoextensible='YES')
   and l.tablespace_name not like 'UNDOT%'
   and to_char(round(l.kblibre/1024,0))< 2000 )
  )
     where  ocupado > 80
    order by ocupado desc

Datafiles de los que consta cada Tablespace  (he incluido los gestionados localmente)

select 
tablespace_name,
file_name,
round(bytes/1024/1024,2) "Total MB",
 status
  from dba_data_files
union
      select 
           p.name tablespace,
           t.name fichero,
           t.bytes/1024/1024 Mb,
           t.status
          from v$tempfile t, v$tablespace p 
                where t.ts#=p.ts#
order by tablespace_name;

Datafiles autoextensibles

select
   file_name,
   tablespace_name,
   bytes/1024/1024  Mb,
   FILE_ID  
from dba_data_files 
where AUTOEXTENSIBLE='YES'

Datafiles temporales autoextensibles

select 
   file_name,
   tablespace_name,
   bytes/1024/1024  Mb  
from dba_temp_files 
  where AUTOEXTENSIBLE='YES'