SELECT  v.ts#, 
    v.name, 
    f.files, 
    DECODE(b.contents,'TEMPORARY','TEMP',b.contents) CONTYPE,
    TO_CHAR(round(f.fsize,0))  || ' Mb' filesize,
    TO_CHAR(round(f.flimit,0)) || ' Mb' filelimit, 
    TO_CHAR(round(f.flimit,0) - round(f.fsize,0)) || ' Mb' fileavail, 
    TO_CHAR(round ( round (NVL(s.FRBYTES,0) / 1048576, 0) / round(f.fsize,0) , 2 ) * 100) || '%' ftotalb,
    b.STATUS,
    (CASE WHEN b.contents='TEMPORARY' THEN '{temp}' WHEN b.contents='UNDO' THEN '{undo}' ELSE NVL(to_char(z.SEGMENTS),'EMPTY!!!') END) SGMTS
    FROM   dba_tablespaces b,
      v$tablespace v,
      (SELECT tablespace_name, SUM(BYTES) "FRBYTES"  FROM dba_free_space GROUP BY tablespace_name) s,  
      (SELECT tablespace_name, 
                  SUM( CASE WHEN autoextensible = 'YES' THEN 
                      maxbytes
                    ELSE 
                      BYTES
                    END
                    ) / 1048576 "FLIMIT",
                  SUM(BYTES) / 1048576 "FSIZE",
                  COUNT (file_name) "FILES"
              FROM dba_data_files
              GROUP BY tablespace_name
              UNION
              SELECT tablespace_name, 
                  SUM( CASE WHEN autoextensible = 'YES' THEN 
                      maxbytes
                    ELSE 
                      BYTES
                    END
                    ) / 1048576 "FLIMIT",
                  SUM(BYTES) / 1048576 "FSIZE",
                  COUNT (file_name) "FILES"
              FROM dba_temp_files
              GROUP BY tablespace_name) f,
      (select count(1) SEGMENTS, tablespace_name from dba_segments group by tablespace_name) z
WHERE s.tablespace_name (+) = v.name  
AND f.tablespace_name (+) = v.name  
AND z.tablespace_name (+) = v.name  
AND b.tablespace_name = v.name
ORDER BY v.ts#;