This tip comes from Jayant Kulkarni, DBA at Rapidigm, Inc., in Houston, TX.
Have you ever trIEd to write a script to give a report of all the tablespaces that may run out of after "n" number of extents? This was fairly easy until the autoextend feature in datafiles was implemented.
All of a sudden the old scripts started to generate false alarms of "tablespaces exhausted" even though they were set to autoextend 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 that takes care of the MAXSIZE feature and avoids generating false-alarms for the "tablespace alert". Ideally this script can be embedded in a cron'd shell script to email or page the DBA in case a tablespace is going to run out of extents, without having to worry about the false alarms.
I have set a limit of 20 extents for the alert report and can be changed to the appropriate number for respective shops.
TTITLE 'Tablespaces That Will Exhaust After Less Than 20 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
AND seg_max_next.next_extent * 20 >
(
(max_size.max_extend - "SIZE") +
(free_size.free)
)
ORDER BY max_size.tablespace_name
/
OUTPUT:
Tue Mar 04
Tablespaces That Will Exhaust After Less Than 20 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.
