Oracle Space Stats sql

COL tablespace_name FOR A30
COL owner FOR A30
COL segment_name FOR A30
COL USED_MB FOR 9999999999999
SET LINESIZE 140
SET PAGESIZE 200
COL tablespace_name FOR A30
COL file_name FOR A60
COL Mb FOR 9999999999999
COL MAX_MB FOR 9999999999999

spool tbs.txt

SELECT TABLESPACE_NAME, 
  (MAX(MAX_SPACE)-MAX(USED_SPACE)  )AS DBA_FREE_SPACE_MB,                                   
  MAX(USED_SPACE) AS USED_SPACE_MB, 
  MAX(MAX_SPACE) AS MAX_SPACE_MB, 
  ROUND(( (MAX(MAX_SPACE)-MAX(USED_SPACE)  ) /MAX(MAX_SPACE) ) *100) as FREE_PCT, 
  CASE WHEN (ROUND(( (MAX(MAX_SPACE)-MAX(USED_SPACE)  ) /MAX(MAX_SPACE) ) *100)<15)  THEN 'Y' ELSE 'N' END AS ADD_MORE_SPACE 
FROM ( 
SELECT tablespace_name,  
   ROUND(SUM(bytes)/1024/2014) AS FREE_SPACE, 
   0 AS MAX_SPACE, 
   0 AS USED_SPACE   
 FROM DBA_FREE_SPACE
 WHERE 1=1
   --AND tablespace_name LIKE '%IGT%' OR tablespace_name LIKE '%DWH%' OR tablespace_name LIKE '%REPORTS%' OR tablespace_name = 'SYSTEM'  OR tablespace_name = 'SYSAUX' 
 GROUP BY tablespace_name  
 UNION ALL 
 SELECT tablespace_name, 
   0 AS FREE_SPACE, 
   ROUND( SUM(CASE WHEN (bytes>maxbytes)  THEN bytes ELSE maxbytes END)/1024/1024) AS MAX_SPACE, 
     0 AS USED_SPACE 
 FROM DBA_DATA_FILES
  WHERE 1=1
   --AND tablespace_name LIKE '%IGT%' OR tablespace_name LIKE '%DWH%' OR tablespace_name LIKE '%REPORTS%' OR tablespace_name = 'SYSTEM'  OR tablespace_name = 'SYSAUX' 
 GROUP BY tablespace_name 
 UNION ALL 
 SELECT tablespace_name, 
     0 AS FREE_SPACE, 
     0 AS MAX_SPACE, 
     ROUND(SUM(bytes/1024/1024)) AS USED_SPACE 
  FROM DBA_SEGMENTS
 WHERE 1=1
   --AND tablespace_name LIKE '%IGT%' OR tablespace_name LIKE '%DWH%' OR tablespace_name LIKE '%REPORTS%' OR tablespace_name = 'SYSTEM'  OR tablespace_name = 'SYSAUX' 
GROUP BY tablespace_name 
) 
GROUP BY tablespace_name;

spool off



spool segments.txt

SELECT owner, tablespace_name, segment_name, USED_MB 
   FROM ( 
  SELECT owner, tablespace_name, segment_name, ROUND(SUM(bytes)/1024/1024)AS USED_MB FROM DBA_SEGMENTS
   WHERE tablespace_name LIKE '%TABLE%' 
  GROUP BY owner, tablespace_name,segment_name 
  ORDER BY ROUND(SUM(bytes)/1024/1024) DESC 
) WHERE ROWNUM < 11;


SELECT owner, tablespace_name, segment_name, USED_MB 
   FROM ( 
  SELECT owner, tablespace_name, segment_name, ROUND(SUM(bytes)/1024/1024)AS USED_MB FROM DBA_SEGMENTS
   WHERE tablespace_name LIKE '%INDEX%' 
  GROUP BY owner, tablespace_name,segment_name 
  ORDER BY ROUND(SUM(bytes)/1024/1024) DESC 
) WHERE ROWNUM < 11;

SELECT owner, tablespace_name, segment_name, USED_MB 
   FROM ( 
  SELECT owner, tablespace_name, segment_name, ROUND(SUM(bytes)/1024/1024)AS USED_MB FROM DBA_SEGMENTS
   WHERE tablespace_name LIKE '%SYSTEM%' 
  GROUP BY owner, tablespace_name,segment_name 
  ORDER BY ROUND(SUM(bytes)/1024/1024) DESC 
) WHERE ROWNUM < 11;


SELECT owner, tablespace_name, segment_name, USED_MB 
   FROM ( 
  SELECT owner, tablespace_name, segment_name, ROUND(SUM(bytes)/1024/1024)AS USED_MB FROM DBA_SEGMENTS
   WHERE tablespace_name LIKE '%SYSAUX%' 
  GROUP BY owner, tablespace_name,segment_name 
  ORDER BY ROUND(SUM(bytes)/1024/1024) DESC 
) WHERE ROWNUM < 11;

spool off


spool datafile.txt
SELECT tablespace_name, file_name, ROUND(bytes/1024/1024) AS Mb, ROUND(maxbytes/1024/1024) AS MAX_MB
  FROM DBA_DATA_FILES;

spool off