Database Experts

Posts

Showing posts from May 21, 2013

Oracle 10g:Get Stale statistics info in mail

May 21, 2013

We need to update the statistics of those statistics stale. For this,Schedule one procedure that mail the info of stale statistics in oracle. 1.Create Procedure for mail the info. CREATE OR REPLACE PROCEDURE USP_STALE_STATS_INFO as vmsg varchar2(10000); vsql varchar2(5000); i int; t int; begin vmsg:='<html><table><tr><td ><b>::Stale index statistics info::</b></td></tr>'; i:=0; t:=0; --index statistics.. for ind in (select owner,table_name,index_name from dba_ind_statistics where STALE_STATS='YES' and owner='PIN' ) loop i:=i+1; vsql:='EXEC dbms_stats.gather_index_stats('''||ind.owner||''','''||ind.index_name||''' , estimate_percent=>100);'; vmsg:=vmsg||'<tr><td>'||vsql||'</tr></td>'; end loop; vmsg:=vmsg||'</table><br />'; vmsg:=vmsg||'<table><tr><td><b>:...

Oracle 10g:Get RMAN Backup Completion Mail

May 21, 2013

Follow this steps to get rman backup completion mail. 1.Create Procedure to for mail. CREATE OR REPLACE PROCEDURE usp_rman_backup_info as Begin UTL_MAIL.SEND(sender=>'DatabaseExperts@de.com', recipients=>'Comma separated email list',subject=>'DatabaseName RMAN Backup info', message => 'RMAN Backup completed @'||to_char(sysdate,'hh24:mi:ss')); end; 2.Update RMAN Script. SQL ' BEGIN SYS.usp_rman_backup_info; END; '; at end of script. delete noprompt obsolete; release channel ch1; release channel ch2; release channel ch3; release channel ch4; SQL ' BEGIN SYS.usp_rman_backup_info; END; '; }