Table Space Exhaustion Warning script

This tip is comes from Jayant Kulkarni and DBA at Rapidigm, Inc., in Houston, TX.

Have you ever tried to write a script to give a, all of the tablespaces this may run out of "n" Number of Exte Nts? This is fairly easy until the autoextend feature in Datafiles is implemented.

All of a sudden the scripts started to generate false alarms of "tablespaces exhausted" even though they were set to a Utoextend for a particular size, and the segments could have spanned many more extents before exhausting the tablespace.

I have attempted to write a SQL script this takes care of the MAXSIZE feature and avoids generating for the " Tablespace alert ". Ideally this script can being embedded in a cron's D shell script to email or page The DBA in case a tablespace are going to run Out of extents, without has to worry about the false alarms.

I have set a limit of extents for the alert and can is changed to the appropriate number for respective shops.

Ttitle ' tablespaces that'll exhaust after less Than extents |

____________________________________________'
COLUMN owner Format A12
COLUMN tablespace_name Format A22
COLUMN segment_name Format A30
COLUMN "SIZE (M)" Format 999,999
COLUMN "COMMENTS" format A35
SET Numformat 999,999,999,999.99
Select Max_size.tablespace_name, "Size"/(1024*1024) "Size (MB)",
(Max_size.max_extend-"size") + (Free_size.free) "Free",
Free_size.biggest, Free_size.smallest,
Max_size.max_extend/(1024 * 1024) "max_size (MB)",
TRUNC ((Max_size.max_extend-"size" + Free)
/seg_max_next.next_extent
) "Exhaust_after"
From
(
SELECT Tablespace_name,
SUM (BYTES) "Free",
MAX (BYTES) "biggest",
MIN (BYTES) "Smallest"
From Dba_free_space
GROUP by Tablespace_name
) Free_size,
(
Select Tablespace_name, SUM (BYTES) "SIZE"
From Dba_data_files
GROUP by Tablespace_name
) Phy_size,
(
SELECT Tablespace_name, MAX (next_extent) next_extent
From Dba_segments
GROUP by Tablespace_name
) Seg_max_next,
(
SELECT Ts.tablespace_name, SUM (ts_size) max_extend
From Dba_tablespaces ts,
(
SELECT Tablespace_name, Autoextensible,
DECODE (maxbytes, 0, BYTES, maxbytes) "Ts_size"
From Dba_data_files
) DF
WHERE Df.tablespace_name = Ts.tablespace_name
GROUP by Ts.tablespace_name
) max_size
WHERE Max_size.tablespace_name = Seg_max_next.tablespace_name
and max_size.tablespace_name = Phy_size.tablespace_name
and max_size.tablespace_name = Free_size.tablespace_name
> Seg_max_next.next_extent *
(
(Max_size.max_extend-"size") +
(Free_size.free)
)
ORDER BY Max_size.tablespace_name
/
OUTPUT:
Tue Mar 04
Tablespaces that'll exhaust after less Than extents
____________________________________________

Tablespace_name | SIZE (MB) | free| Biggest| Smallest| Max_size (MB) | Exhaust_after

______________________|___________________|___________________|_________ __________|___________________|________ ___________|___________________

mas_128m_ix05 | 39,040.00| 2,147,483,648.00| 134,217,728.00| 134,217,728.00| 40,960.00| 16.00

mas_128m_ix06 | 39,040.00| 2,147,483,648.00| 134,217,728.00| 134,217,728.00| 40,960.00| 16.00

mas_128m_ix07 | 39,040.00| 2,147,483,648.00| 134,217,728.00| 134,217,728.00| 40,960.00| 16.00

3 Rows selected.