ora_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

spool temp_file.txt

SELECT tablespace_name, file_name, ROUND(bytes/1024/1024) AS Mb, ROUND(maxbytes/1024/1024) AS MAX_MB

  FROM DBA_TEMP_FILES;

spool off

spool invalid_indexes.txt

col owner for A30

col INDEX_NAME for A30

SET LINESIZE 120

SELECT owner, index_name, status 

  FROM DBA_INDEXES 

 WHERE status <> 'VALID' AND status <> 'N/A';

spool memory_params.txt

col name for A30

col value for A30

SELECT name, value 

  FROM V$PARAMETER 

 WHERE name IN ('memory_target', 'memory_max_target')

ORDER BY name;

spool off

exit;

Fast alternative to Tablespace Calculation - same as ora_stats.sql results!

SELECT 

    m.tablespace_name,

    -- Current Used Space in MB

    ROUND(m.used_space * t.block_size / 1024 / 1024) AS used_mb,

    -- Maximum Possible Size in MB (Accounts for Autoextend)

    ROUND(m.tablespace_size * t.block_size / 1024 / 1024) AS max_size_mb,

    -- Remaining Available Space in MB

    ROUND((m.tablespace_size - m.used_space) * t.block_size / 1024 / 1024) AS free_mb,

    -- Usage Percentage

    100-ROUND(m.used_percent) AS pct_free

FROM DBA_TABLESPACE_USAGE_METRICS M

JOIN DBA_TABLESPACES T 

  ON m.tablespace_name = t.tablespace_name

ORDER BY pct_free ASC;