Tuesday, May 21, 2013

Oracle 10g:Get Stale statistics info in mail

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>::Stale Table statistics info::</b></td></tr>';
--for table statistics
for tbl in
(select
      owner,TABLE_NAME,(num_rows*avg_row_len)/1024/1024 sizeinmb 
from dba_tab_statistics
where STALE_STATS='YES' and owner='PIN'
)
loop
t:=t+1;
vsql:='EXEC DBMS_STATS.GATHER_TABLE_STATS(ownname=>'''||tbl.owner||''',tabname=>'''||tbl.TABLE_NAME||''',CASCADE=>TRUE,DEGREE=>1,estimate_percent =>100);';
vmsg:=vmsg||'<tr><td>--'||tbl.TABLE_NAME||'::'||to_char(tbl.sizeinmb,'9,999')||' Mbs</td></tr>';
vmsg:=vmsg||'<tr><td>'||vsql||'</tr></td>';
end loop;
vmsg:=vmsg||'</table></html>';

if (i >0 or t>0) THEN
UTL_MAIL.SEND(sender=>'DatabaseExperts@de.com', recipients=>'List of Comma separated email', cc =>'' , subject=>'DatabaseName Stale statistics info.', message => vmsg,  mime_type => 'text/html');
End if;
end;

2.Schedule this procedure by DBMS_Scheduler.

BEGIN
    sys.DBMS_SCHEDULER.CREATE_SCHEDULE (
          
        repeat_interval  => 'FREQ=HOURLY;INTERVAL=4',    
        comments => 'This is job wil mail stale table/indexes info',
        schedule_name  => '"Stale_Schedule"');
       
END;

3.Create DBMS_JOB.

BEGIN
    SYS.DBMS_SCHEDULER.CREATE_JOB (
            job_name => '"SYSTEM"."Stale_info_job"',
            schedule_name => '"SYSTEM"."Stale_Schedule"',
            job_type => 'STORED_PROCEDURE',
            job_action => '"SYSTEM"."USP_STALE_STATS_INFO"',
            number_of_arguments => 0,
            job_class => '"SYS"."DEFAULT_JOB_CLASS"',
            enabled => FALSE,
            auto_drop => FALSE,
            comments => 'This job will mail table/index stale statistics info');

    SYS.DBMS_SCHEDULER.SET_ATTRIBUTE(
             name => '"SYSTEM"."Stale_info_job"',
             attribute => 'logging_level', value => DBMS_SCHEDULER.LOGGING_RUNS);
 
    SYS.DBMS_SCHEDULER.enable(
             name => '"SYSTEM".""');
END;
/

4.Run the sql statement.

::Stale index statistics info::
EXEC dbms_stats.gather_index_stats('***','*****' , estimate_percent=>100);

::Stale Table statistics info::
--***_T:: 0 Mbs
EXEC DBMS_STATS.GATHER_TABLE_STATS(ownname=>'***',tabname=>'**_T',CASCADE=>TRUE,DEGREE=>1,estimate_percent =>100);

Oracle 10g:Get RMAN Backup Completion Mail

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; ';
}