通过存储过程自动生成AWR报告


通过存储过程自动生成AWR报告
 
[plain] 
CREATE OR REPLACE PROCEDURE sp_auto_awrreport(reporttime varchar2 default null,  
                                              rpt_interval number default 2)   
AS  
----用途：自动生成AWR报告  
----创建人：cavlin  
----创建日期：20130210  
----reporttime 报告结束时间  
----rpt_interval 报告间隔 1:30分钟,2:60分钟  
start_snap number;  
end_snap number;  
start_time varchar2(14);  
end_time varchar2(14);  
awr_file utl_file.file_type;  
n_dbid  number;  
  
BEGIN  
  
  if reporttime is null then  
    ---查询最大的快照id  
    select max(snap_id) into end_snap from dba_hist_snapshot;  
    start_snap:=end_snap-rpt_interval;  
  else  
    ---取输入时间对应的snap_id  
    select snap_id  
    into end_snap  
    from dba_hist_snapshot  
   where to_char(end_interval_time, 'yyyymmddhh24mi') = reporttime  
     and instance_number = 1;  
    start_snap:=end_snap-rpt_interval;  
  end if;  
  
---格式化快照时间  
  select dbid,to_char(end_interval_time-rpt_interval/48, 'yyyymmddhh24mi'),to_char(end_interval_time, 'yyyymmddhh24mi')  
        into n_dbid,start_time,end_time  
    from dba_hist_snapshot  
   where snap_id = end_snap and instance_number=1;  
  
---实例1  
  awr_file:=utl_file.fopen('DIR_AWRREPORT','AWR_NODE1_'||start_time||'_'||end_time||'.html', 'a',32767);  
  for awr_info in (select output from table(dbms_workload_repository.awr_report_html(n_dbid,1,start_snap,end_snap,0))) loop  
  UTL_FILE.put_line(awr_file,awr_info.output);  
  end loop;  
  utl_file.fclose(awr_file);  
  
---实例2  
  awr_file:=utl_file.fopen('DIR_AWRREPORT','AWR_NODE2_'||start_time||'_'||end_time||'.html', 'a',32767);  
  for awr_info in (select output from table(dbms_workload_repository.awr_report_html(n_dbid,2,start_snap,end_snap,0))) loop  
  UTL_FILE.put_line(awr_file,awr_info.output);  
  end loop;  
  utl_file.fclose(awr_file);  
  
END sp_auto_awrreport;