批量抓取AWR报告

批量抓取AWR报告

set serveroutput on
set feedback off
set trims on

spool sp_temp.sql

declare
  cursor cursnap(sdate varchar2) is
    select snap_id, to_char(end_interval_time, 'hh24') snap_time
      from dba_hist_snapshot
     where end_interval_time between
           to_date(sdate || '0830', 'yyyymmddhh24mi') and
           to_date(sdate || '1830', 'yyyymmddhh24mi')
     order by snap_id;
  rs       cursnap%rowtype;
  s_date   varchar2(8);
  d_date   number;
  tmp_time varchar2(2);
  tmp_id   number;

begin
  --select to_char(sysdate,'yyyymmdd') into s_date from dual;
  tmp_time := 'no';
  tmp_id   := 0;
  s_date   := '20100111';
  select to_char(to_date(s_date, 'yyyymmdd') - 1, 'd')
    into d_date
    from dual;
  if d_date <= 5 then
    open cursnap(s_date);
    loop
      fetch cursnap
        into rs;
      exit when cursnap%notfound;
      if tmp_time != 'no' and tmp_id != 0 then
        dbms_output.put_line('define report_type=html');
        dbms_output.put_line('define num_days=3.14');
        dbms_output.put_line('define begin_snap=' || tmp_id);
        dbms_output.put_line('define end_snap=' || rs.snap_id);
        dbms_output.put_line('define report_name=' ||
                             'Z:\dbashell\monitor\cas_' ||
                             substr(s_date, 5) || '_' || tmp_time ||
                             rs.snap_time || '.html');
        dbms_output.put_line('@?\rdbms\admin\awrrpt.sql');
        dbms_output.put_line('undefine num_days');
        dbms_output.put_line('undefine report_type');
        dbms_output.put_line('undefine report_name');
        dbms_output.put_line('undefine begin_snap');
        dbms_output.put_line('undefine end_snap');
      end if;
      tmp_time := rs.snap_time;
      tmp_id   := rs.snap_id;
    end loop;
    close cursnap;
  end if;
  dbms_output.put_line('exit');
end;
/

spool off;
exit;
Trackback

no comment untill now

Add your comment now

切换到手机版