Proracle’s Weblog

February 5, 2008

ssas_monitorextents

Filed under: useful — proracle @ 9:33 am
Tags:
create or replace PROCEDURE ssas_monitorextents AS
db_name_var varchar2(2000);
area1 number;
area2 number;
ts_name sys.dba_tablespaces.tablespace_name%TYPE;
alert1 boolean;
alert2 boolean;
next number;
error_type varchar2(9);
error_message varchar2(255);
required_fix varchar2(255);
status varchar2(20);
--
cursor db is select global_name from global_name;
--
cursor ts is
select tablespace_name
from sys.dba_tablespaces
where tablespace_name not in ('TEMP','RBS','ROLLBACK')
order by tablespace_name;
--
cursor areas is
select bytes
from dba_free_space
where tablespace_name = ts_name
order by bytes desc;
--
cursor segs is
select owner, segment_name, segment_type, next_extent,extents,max_extents
from dba_segments
where segment_type not in ('CACHE','ROLLBACK')
--owner not in ('SYSTEM','SYS','OUTLN')
 and tablespace_name = ts_name;
-- and segment_name not like '%$%';
--
BEGIN
--
open db;
fetch db into db_name_var;
close db;
--
-- Loop for all tablespaces, get 2 largest free areas, get all segs in tablespace
--
for ts_rec in ts loop
-- dbms_output.put_line('ts = '||ts_rec.tablespace_name);
ts_name := ts_rec.tablespace_name;
open areas;
fetch areas into area1;
-- dbms_output.put_line('area1 = '||area1);
fetch areas into area2;
-- dbms_output.put_line('area2 = '||area2);
close areas;
--
for seg_rec in segs loop
-- dbms_output.put_line('seg = '||seg_rec.segment_name||', next = '||seg_rec.next_extent);
alert1 := FALSE;
alert2 := FALSE;
next := seg_rec.next_extent;
if ((2*next > area1) and (next > area2)) then
alert1 := TRUE;
dbms_output.put_line('******************** ALERT 1 ****************');
dbms_output.put_line('No room for 2 additional extents for '||seg_rec.segment_name);
end if;
if (next > area1) then
alert2 := TRUE;
dbms_output.put_line('******************** ALERT 2 ****************');
dbms_output.put_line('No room for next extent for '||seg_rec.segment_name);
end if;
--this section added so if 2 alerts occur at the same time, both are
--signalled.
if (alert1) then
status := 'ALERT1';
end if;
if (alert2) then
status := 'ALERT2';
end if;
if (alert1 or alert2) then
dbms_output.put_line('owner=' || seg_rec.owner ||', Object='||seg_rec.segment_name ||', type='||seg_rec.segment_type);
--dbms_output.put_line('Next extent size = '||next);
end if;
alert1 := FALSE;
alert2 := FALSE;
--end of added section
--
if (seg_rec.extents >= seg_rec.max_extents*0.9) then
alert1 := TRUE;
dbms_output.put_line('******************** ALERT 3 ****************');
dbms_output.put_line('90% + extents used by ' || seg_rec.segment_type || '-' || seg_rec.owner || '.' || seg_rec.segment_name);
end if;
if (seg_rec.extents = seg_rec.max_extents) then
alert2 := TRUE;
dbms_output.put_line('******************** ALERT 4 ****************');
dbms_output.put_line('Max extents reached ' || seg_rec.segment_type || '-' || seg_rec.owner || '.' || seg_rec.segment_name);
end if;
if (alert1) then
status := 'ALERT1';
end if;
if (alert2) then
status := 'ALERT2';
end if;
if (alert1 or alert2) then
dbms_output.put_line('owner=' || seg_rec.owner ||', Object='||seg_rec.segment_name ||', type='||seg_rec.segment_type);
--dbms_output.put_line('Next extent size = '||next);
end if;
end loop;
end loop;
---
END ssas_monitorextents;
/

Blog at WordPress.com.