Thursday, June 15, 2006

Find database size and send mail

CREATE OR REPLACE PROCEDURE test_db_size
IS
debug_no number(10):=0;
conn UTL_SMTP.CONNECTION;
v_sp number(10);
l number(10);
v_mesg_header LONG:='';
v_mesg_sub_header LONG:='';
v_data LONG:='';
v_message LONG:='';
v_details LONG:='';
v_alert number(10):=0;
v_instance VARCHAR2(16);
v_instance_name VARCHAR2(16);
v_hostname VARCHAR2(64);
v_carriagereturn VARCHAR2( 2 ):= CHR( 13 ) || CHR( 10 );
v_count1 NUMBER(7):=0;
v_count2 NUMBER(7):=0;
v_count3 NUMBER(7):=0;
v_sender VARCHAR2(64);
v_subject VARCHAR2(64);
v_db_link VARCHAR2(128);
l_db_link VARCHAR2(128);
v_tablespace_name VARCHAR2(30);
v_pct_free NUMBER(5);
v_owner VARCHAR2(30);
v_segment_name VARCHAR2(81);
v_segment_type VARCHAR2(18);
v_next_extent NUMBER(20);
v_max_free NUMBER(20);
v_extents NUMBER(20);
v_max_extents NUMBER(20);

TYPE x IS REF CURSOR;

Type space_type is REF CURSOR;
inst_detail space_type;

space1 space_type;
space2 x;

sqlstmt1 varchar2(5000);
sqlstmt2 varchar2(5000);

v_errnum number :=0;
v_errmsg varchar2(255);
v_all_err_msgs LONG:='';
v_tot_err number :=0;
CURSOR cur_instance IS SELECT instance_name
from dba_repository.instance
WHERE environment like 'DEVELO%' and dbms like 'ORACLE%';


BEGIN



/* Open connection */
conn:= utl_smtp.open_connection( 'Mailhost.My_company.com', 25 );
/* Hand Shake */
utl_smtp.helo( conn, 'Mailhost.My_company.com' );
v_sender:='admin@yahoo.co.in';
v_mesg_header:=v_mesg_header||v_carriagereturn||v_carriagereturn||' Please give attention to the following Space issues in Developmet Databases'||v_carriagereturn;
v_mesg_header:=v_mesg_header||'================================================================================';
OPEN cur_instance;
LOOP
BEGIN
FETCH cur_instance INTO v_instance;

EXIT WHEN cur_instance%NOTFOUND;
debug_no:=1;




IF cur_instance%rowcount>=1 THEN

v_db_link:=v_instance||'.My_company.com';

sqlstmt1:='SELECT upper(instance_name),upper(host_name) FROM V$INSTANCE@'||v_db_link;

debug_no:=2;

sqlstmt2:='select sum(round((bytes/1048576000),2)) from dba_data_files@'||v_db_link;


debug_no:=3;

open space2 for sqlstmt2;
loop
fetch space2 into v_sp;
--dbms_output.put_line(v_sp);

exit when space2%NOTFOUND;


end loop;
close space2;

open inst_detail for sqlstmt1;
v_count1:=0;
v_count2:=0;
v_count3:=0;
fetch inst_detail INTO v_instance_name,v_hostname;


if inst_detail%FOUND then
v_mesg_sub_header:=v_carriagereturn||v_carriagereturn||' Instance: ' ||v_instance_name||'; Machine: '||v_hostname||'; Date/time: '||to_char(sysdate,'Day,Mon dd,RRRR HH:MI AM') ||v_carriagereturn;
v_mesg_sub_header:=v_mesg_sub_header||'********************************************************************************';

end if;

debug_no:=4;



open space1 for sqlstmt1;
loop
fetch space1 into v_owner,v_segment_name,v_segment_type,v_tablespace_name,v_extents,v_max_extents;
IF space1%rowcount<1>=1 THEN
v_count1:=v_count1+1;
v_details:=v_details||v_carriagereturn||'The '||lower(v_segment_type)||' '||v_segment_name||' owned by '||v_owner||' in tablespace '||v_tablespace_name||' is going to reach the MAXEXTENTS of '||v_max_extents||'.';
end if;
end loop;
close space1;

debug_no:=5;

/*
open space2 for sqlstmt2;
loop
fetch space2 into v_sp;
dbms_output.put_line(v_sp);
--IF space2%rowcount<1>0 then
v_data:=v_data||v_mesg_sub_header||v_details;
v_alert:=v_alert+1;
end if;
v_details:='';
close inst_detail;

debug_no:=6;

END IF;




EXCEPTION
WHEN OTHERS THEN
v_tot_err:=1;
v_errnum:= SQLCODE;
v_errmsg:= SUBSTR(SQLERRM,1,255);
--dbms_output.put_line(v_mesg_sub_header);
--dbms_output.put_line(v_errmsg);
v_all_err_msgs :=v_all_err_msgs||'In '||v_instance||'-Dev : ORA-'||to_char(v_errnum)||'Error-'||v_errmsg||v_carriagereturn||v_carriagereturn;
--dbms_output.put_line('#'||debug_no||'#'||substr(v_errmsg,1,100)||'*'||v_sp||'.');
v_mesg_sub_header:=v_carriagereturn||v_carriagereturn||' Instance: ' ||v_instance_name||'; Machine: '||v_hostname||'; Date/time: '||to_char(sysdate,'Day,Mon dd,RRRR HH:MI AM')||' Space count '||v_sp||v_carriagereturn;
v_mesg_sub_header:=v_mesg_sub_header||'********************************************************************************';
--dbms_output.put_line(v_mesg_sub_header);
END;
END LOOP;
CLOSE cur_instance;

--

v_data:=v_data||v_carriagereturn;
v_data:=v_data||v_carriagereturn||'*************************************END OF REPORT******************************'||v_carriagereturn||v_carriagereturn;

IF v_alert=0 then
v_data:=v_carriagereturn||v_carriagereturn||' ***** No Space Alerts in Development Environment. ! ***** ';
v_subject:='Checking:Space Alert-DevelopmentDatabases';
v_message:='Subject:'||v_subject||chr(10)||v_data;
utl_smtp.mail( conn,v_sender);

utl_smtp.rcpt( conn,'saptarshi.biswas@stl.My_company.com');
utl_smtp.data( conn,v_message);

else
v_subject:='Emergency:Space Alert-DevelopmentDatabases';
v_message:='Subject:'||v_subject||chr(10)||v_mesg_header||v_data;
utl_smtp.mail( conn,v_sender);


utl_smtp.rcpt( conn,'saptarshi.biswas@stl.My_company.com');
utl_smtp.data( conn,v_message);

end if;
if v_tot_err=1 then
v_subject:='Errors-Devl';
--v_message:='Subject:'||v_subject||chr(10)||v_all_err_msgs||v_mesg_sub_header;
v_message:='Subject:'||v_subject||chr(10)||v_mesg_sub_header;
utl_smtp.mail( conn,v_sender);
utl_smtp.rcpt( conn,'saptarshi.biswas@My_company.com');
utl_smtp.rcpt( conn,'saptarshi.biswas@gmail.com');

utl_smtp.data( conn,v_message);

utl_smtp.quit( conn );
else
utl_smtp.quit( conn );
end if;
--

END test_db_size;


No comments:

Post a Comment