Script to calculate exact free space in a tablespace considering maxsize clause

Many a times we receive false alerts for low free space on the oracle tablespaces, as our traditional tablespace monitoring scripts does not consider maxsize clause while calculating free space in the tablespace.

Please find below the tablespace monitoring script – which gives you exact free space in tablespace:

 


WITH NEW_TBS AS
 (
 SELECT FILE_ID, TABLESPACE_NAME, FILE_NAME,
 DECODE (AUTOEXTENSIBLE,
 'YES', GREATEST (BYTES, MAXBYTES),
 BYTES
 ) MYSIZE,
 DECODE (AUTOEXTENSIBLE,
 'YES', CASE
 WHEN (MAXBYTES > BYTES)
 THEN (MAXBYTES - BYTES)
 ELSE 0
 END,
 0
 ) GROWTH
 FROM DBA_DATA_FILES)
 SELECT NEW_TBS.TABLESPACE_NAME,
 ROUND (SUM (NEW_TBS.MYSIZE) / (1024 * 1024)) TOTSIZE,
 ROUND (SUM (GROWTH) / (1024 * 1024)) GROWTH,
 ROUND ((SUM (NVL (FREEBYTES, 0))) / (1024 * 1024)) DFS,
 ROUND ((SUM (NVL (FREEBYTES, 0)) + SUM (GROWTH)) / (1024 * 1024)
 ) TOTFREE,
 ROUND ( (SUM (NVL (FREEBYTES, 0)) + SUM (GROWTH))
 / SUM (NEW_TBS.MYSIZE)
 * 100
 ) PERC
 FROM NEW_TBS, (SELECT FILE_ID, SUM (BYTES) FREEBYTES
 FROM DBA_FREE_SPACE
 GROUP BY FILE_ID) DFS
 WHERE NEW_TBS.FILE_ID = DFS.FILE_ID(+)
 AND NEW_TBS.TABLESPACE_NAME NOT LIKE '%UNDOTB%'
 GROUP BY NEW_TBS.TABLESPACE_NAME
 ORDER BY 6
 ;

Also please find below more simpler scripts which just gives u tablespace details which matches following criteria:

Free space < 20 GB  & Free percentage < 20 %


WITH NEW_TBS AS
 (
 SELECT FILE_ID, TABLESPACE_NAME, FILE_NAME,
 DECODE (AUTOEXTENSIBLE,
 'YES', GREATEST (BYTES, MAXBYTES),
 BYTES
 ) MYSIZE,
 DECODE (AUTOEXTENSIBLE,
 'YES', CASE
 WHEN (MAXBYTES > BYTES)
 THEN (MAXBYTES - BYTES)
 ELSE 0
 END,
 0
 ) GROWTH
 FROM DBA_DATA_FILES)
 SELECT * FROM
 (SELECT 'TABLESPACE NAME:',NEW_TBS.TABLESPACE_NAME,
 'FREE MB:', ROUND ((SUM (NVL (FREEBYTES, 0)) + SUM (GROWTH)) / (1024 * 1024)) AS TOTFREE,
 'FREE PERCENT:', ROUND ( (SUM (NVL (FREEBYTES, 0)) + SUM (GROWTH))/ SUM (NEW_TBS.MYSIZE)* 100) AS PERC
 FROM NEW_TBS, (SELECT FILE_ID, SUM (BYTES) FREEBYTES
 FROM DBA_FREE_SPACE
 GROUP BY FILE_ID) DFS
 WHERE NEW_TBS.FILE_ID = DFS.FILE_ID(+)
 AND NEW_TBS.TABLESPACE_NAME NOT LIKE '%UNDOTB%'
 GROUP BY NEW_TBS.TABLESPACE_NAME) A
 WHERE A.PERC < 20 AND A.TOTFREE < 20480
 ORDER BY 6;

 

Hope so this will be very useful for you 🙂

Cheers…

Regards,

Adityanath

 

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s