-- Query to analyze database tablespace size

SELECT
  COALESCE(q_ddf.tablespace_name,q_dfs.tablespace_name,'UNKNOWN') tablespace,
  q_ddf.mb_alloc allocated_tablespace,
  ROUND((q_ddf.mb_alloc-NVL(q_dfs.mb_free,0))/1024,3) used_gb,
  ROUND(q_dfs.mb_free/1024,3)  free_gb,
  ROUND((1-q_dfs.mb_free/q_ddf.mb_alloc)*100,3) "Used_%",
  NVL(q_dfs.max_mb,0)  largest_ts_mb
FROM
 (SELECT
    tablespace_name,
    SUM(bytes)/1024/1024 mb_free,
    MAX(bytes)/1024/1024 max_mb
  FROM  sys.dba_free_space
  GROUP BY  tablespace_name
 ) q_dfs,
 (SELECT
    tablespace_name,
    SUM(bytes)/1024/1024 mb_alloc
  FROM  sys.dba_data_files
  GROUP BY  tablespace_name
 ) q_ddf
WHERE q_ddf.tablespace_name = q_dfs.tablespace_name (+)
ORDER BY
  COALESCE(q_ddf.tablespace_name,q_dfs.tablespace_name,'UNKNOWN')
;

-- Query to analyze database size as sum of 
-- redolog+dbfile_size+temp_file_size+control_files_size


SELECT
  sys.database_name||' Size: '||
   ROUND(SUM(db_file)/1024/1024/1024,3)||' GB'
    database_size
FROM
 (SELECT SUM(bytes)  db_file  FROM dba_data_files   UNION
  SELECT SUM(bytes)  redo_log  FROM v$log           UNION
  SELECT SUM(bytes)  temp_file FROM dba_temp_files  UNION
  SELECT SUM(block_size*file_size_blks)  ctl_files
    FROM v$controlfile
);