send alert when TBS is full

CREATE OR REPLACE PROCEDURE xxxt_tbs_capacity_control (oran IN NUMBER)

AS

CURSOR cur_tbs

IS

SELECT a.tablespace_name, a.total,

(a.total - ROUND (b.free, 2)) used, ROUND (b.free, 2) free,

c.max_total,

ROUND ((a.total - ROUND (b.free, 2)) / a.total * 100, 2)

USING,

ROUND ((a.total - ROUND (b.free, 2)) / c.max_total * 100,

2

) max_using

FROM (SELECT tablespace_name, SUM (BYTES / 1024 / 1024) total

FROM dba_data_files

GROUP BY tablespace_name) a,

(SELECT tablespace_name, SUM (BYTES / 1024 / 1024) free

FROM dba_free_space

GROUP BY tablespace_name) b,

(SELECT tablespace_name,

SUM (maxbytes / 1024 / 1024) max_total

FROM dba_data_files

GROUP BY tablespace_name

HAVING (SUM (maxbytes / 1024 / 1024)) > 0) c

WHERE a.tablespace_name = b.tablespace_name

AND b.tablespace_name = c.tablespace_name(+)

and a.tablespace_name not in(select tablespace_name from dba_tablespaces where contents in('UNDO','TEMPORARY'))

ORDER BY ROUND ((a.total - ROUND (b.free, 2)) / a.total * 100, 2) DESC;

hata VARCHAR2 (30000);

BEGIN

FOR cur_tbs_rec IN cur_tbs

LOOP

IF cur_tbs_rec.USING > oran

THEN

hata :=

hata

|| UTL_TCP.crlf

|| 'Tablespace:'

|| cur_tbs_rec.tablespace_name

|| UTL_TCP.crlf

|| '===================='

|| UTL_TCP.crlf

|| 'Tot.:'

|| cur_tbs_rec.total

|| ' '

|| 'Use:'

|| cur_tbs_rec.used

|| ' '

|| 'Max Tot:'

|| cur_tbs_rec.max_total

|| ' '

|| 'Used%:'

|| cur_tbs_rec.USING

|| ' '

|| 'Max Used%:'

|| cur_tbs_rec.max_using

|| UTL_TCP.crlf;

END IF;

END LOOP;

IF LENGTH (hata) > 10

THEN

xxxt_sendmail ('frito_sd@intl.fritolay.com',

'FRITO-SD',

'TABLESPACE CAPACITY FULL',

'dba@experteam.com.tr',

hata,

'165.198.192.210',

25

);

INSERT INTO xtdba.xxxt_tbs_capacity

VALUES (SYSDATE, hata);

COMMIT;

END IF;

END;