SET COLSEP '|'
SET VERIFY off
SET serveroutput ON SIZE 1000000
BREAK ON report

COLUMN tablespace_name format a30 heading 'TABLESPACE'
COLUMN sizegb   format 9999999999D9 heading 'SIZE-Gb'
COLUMN usedproc format 999D99 heading 'USED-%'
COLUMN status format a10 heading 'STATUS'
COMPUTE SUM LABEL 'Total size:' OF sizegb ON report

SELECT b.tablespace_name ,
       b.bytes/1024/1024/1024 AS sizegb ,
       NVL(100-((a.bytes/b.bytes)*100), 100) usedproc,
       REPLACE(c.status,' ','_') status
FROM
     ( SELECT tablespace_name,
              SUM(bytes) bytes
       FROM dba_free_space
       GROUP BY tablespace_name
     ) a ,
     ( SELECT tablespace_name,
              SUM(bytes) bytes
       FROM dba_data_files
       GROUP BY tablespace_name
     ) b ,
       dba_tablespaces c
WHERE b.tablespace_name = a.tablespace_name (+)
AND   b.tablespace_name = c.tablespace_name
UNION
SELECT  f.TABLESPACE_NAME,
        f.TOTAL_MB/1024 sizegb,
        NVL( (u.USED_MB/f.TOTAL_MB)*100, 0 ) usedproc,
        'TEMPORARY' status
FROM
(
 SELECT f1.TABLESPACE_NAME,SUM( f1.BYTES/1024/1024 ) TOTAL_MB
 FROM (
  SELECT  TABLESPACE_NAME,BYTES
  FROM dba_temp_files
  UNION ALL
  SELECT  TABLESPACE_NAME,BYTES
  FROM dba_data_files
  WHERE TABLESPACE_NAME IN (
        SELECT TABLESPACE_NAME
        FROM dba_tablespaces
        WHERE CONTENTS='TEMPORARY'
        )
 ) f1
 GROUP BY f1.TABLESPACE_NAME
) f,
(
 SELECT u1.TABLESPACE,
 SUM(u1.blocks) * MAX((SELECT VALUE FROM v$parameter WHERE name='db_block_size')/1024/1024)  USED_MB
 FROM v$sort_usage u1
 GROUP BY u1.TABLESPACE
) u
WHERE f.TABLESPACE_NAME = u.TABLESPACE (+)
ORDER BY 1;