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 a12COLUMN tablespace_name format a22COLUMN segment_name format a30COLUMN "SIZE(M)" format 999,999COLUMN "COMMENTS" format a35SET numformat 999,999,999,999.99SELECT 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_spaceGROUP BY tablespace_name) free_size,(SELECT tablespace_name, SUM (BYTES) "SIZE"FROM dba_data_filesGROUP BY tablespace_name) phy_size,(SELECT tablespace_name, MAX (next_extent) next_extentFROM dba_segmentsGROUP BY tablespace_name) seg_max_next,(SELECT ts.tablespace_name, SUM (ts_size) max_extendFROM dba_tablespaces ts,(SELECT tablespace_name, autoextensible,DECODE (maxbytes, 0, BYTES, maxbytes) "TS_SIZE"FROM dba_data_files) dfWHERE df.tablespace_name = ts.tablespace_nameGROUP BY ts.tablespace_name) max_sizeWHERE max_size.tablespace_name = seg_max_next.tablespace_nameAND max_size.tablespace_name = phy_size.tablespace_nameAND max_size.tablespace_name = free_size.tablespace_nameAND seg_max_next.next_extent * 20 >((max_size.max_extend - "SIZE") +(free_size.free))ORDER BY max_size.tablespace_name/OUTPUT: Tue Mar 04Tablespaces 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.
