快捷搜索:

表空间耗尽警告脚本 - 空间

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.

您可能还会对下面的文章感兴趣: