批量生成AWR报告脚本

--####################################################

--create by shitaipeng

--version 1.0

--titie: Get AWR reports

--####################################################

set echo off

set feedback off

set heading off

set pagesize 0

set termout off

set trimout on

set trimspool on

 

set serveroutput on size 1000000

declare

  v_awr_command    varchar2(100) := '@?/rdbms/admin/awrrpt.sql';

  v_report_type    varchar2(10)  := 'HTML';

  v_db_name        varchar2(32)  := '&db_name';

  v_days           number        := 10;

  v_inst_id        number        := &inst_id;

  v_awr_begin_time date          := to_date('&BeginDate', 'YYYY-MM-DD HH24:MI:SS');

  v_awr_end_time   date          := to_date('&EndDate', 'YYYY-MM-DD HH24:MI:SS');

  v_br             varchar2(10)  := chr(10) ;

 

  v_begin_snap_id  dba_hist_snapshot.snap_id%type;

  v_end_snap_id    dba_hist_snapshot.snap_id%type;

  v_begin_time     dba_hist_snapshot.begin_interval_time%type;

  v_end_time       dba_hist_snapshot.begin_interval_time%type;

  v_report_name    varchar2(256);

begin

  for v_snap_list in (select SNAP_ID, BEGIN_INTERVAL_TIME

                        from dba_hist_snapshot b

                       where Instance_number = v_inst_id

                         and BEGIN_INTERVAL_TIME between v_awr_begin_time

                                                     and v_awr_end_time

                       order by 1)

  loop

    --设置snapID

    v_begin_snap_id := v_end_snap_id;

    v_end_snap_id   := v_snap_list.SNAP_ID;

   

    --设置报表时间

    v_begin_time    := v_end_time;

    v_end_time      := v_snap_list.BEGIN_INTERVAL_TIME;

 

    --生成AWR报表名称

    v_report_name   := 'AWR_' || v_db_name || to_char(v_inst_id) || '_'

                    || to_char(v_begin_time, 'YYYYMMDDHH24MI_')

                    || to_char(v_end_time,   'YYYYMMDDHH24MI')

                    || '.html';

                   

    --如果v_begin_snap_id 和 v_end_snap_id 都不为空时才执行

    if (v_begin_snap_id is not null) and (v_end_snap_id is not null) then

      dbms_output.put_line(v_awr_command);

      dbms_output.put_line(v_report_type);

      dbms_output.put_line(to_char(v_days)); 

      dbms_output.put_line(v_begin_snap_id);

      dbms_output.put_line(v_end_snap_id);

      dbms_output.put_line(v_report_name);

      dbms_output.put_line(v_br);

    end if;

 

  end loop;  

end;

/

执行完毕以后,复制输出结果贴到SQLPLUS中批量执行即可

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/469356/viewspace-758911/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/469356/viewspace-758911/