1.双击awr.cmd，通过cmd窗口运行awr.sql
cmd.exe /c sqlplus lcam_1230/zcpzg1z_1230@54_orcl @awr.sql
awr.cmd
2.awr.sql，定义需要生成的AWR报告的时间区间,调用myawr.sql
@@D:\AWR\myawr.sql 0 8
@@D:\AWR\myawr.sql 8 10
@@D:\AWR\myawr.sql 10 12
@@D:\AWR\myawr.sql 12 14
@@D:\AWR\myawr.sql 14 16
@@D:\AWR\myawr.sql 16 18
@@D:\AWR\myawr.sql 20 24
exit
2.awr.sql
3.myawr.sql，获取AWR报告数据
set echo off;
set veri off;
set feedback off;
set termout on;
set heading off;
set linesize 500; variable dbid number;
variable inst_num number;
variable bid number;
variable eid number;
variable bhour number;
variable ehour number;
begin
:bhour := &1;
:ehour := &2;
end;
/ begin
select min(t.snap_id) + :bhour - 1 into :bid from sys.dba_hist_snapshot t where to_char(t.begin_interval_time,'yyyy-mm-dd') = to_char(sysdate,'yyyy-mm-dd');
select min(t.snap_id) + :ehour - 1 into :eid from sys.dba_hist_snapshot t where to_char(t.begin_interval_time,'yyyy-mm-dd') = to_char(sysdate,'yyyy-mm-dd');
select dbid into :dbid from v$database;
select instance_number into :inst_num from v$instance;
end;
/
set termout off;
column report_name new_value report_name noprint;
select 'awrrpt_'||to_char(sysdate,'yyyymmdd')||'_'||:bhour||'-'||:ehour||'.html' report_name from dual;
set termout on;
spool &report_name;
select output from table(dbms_workload_repository.awr_report_html(:dbid,:inst_num,:bid,:eid,0 ));
spool off; begin
select min(t.snap_id) + :bhour - 1 into :bid from sys.dba_hist_snapshot t where to_char(t.begin_interval_time,'yyyy-mm-dd') = to_char(sysdate-1,'yyyy-mm-dd');
select min(t.snap_id) + :ehour - 1 into :eid from sys.dba_hist_snapshot t where to_char(t.begin_interval_time,'yyyy-mm-dd') = to_char(sysdate-1,'yyyy-mm-dd');
select dbid into :dbid from v$database;
select instance_number into :inst_num from v$instance;
end;
/
set termout off;
column report_name new_value report_name noprint;
select 'awrrpt_'||to_char(sysdate-1,'yyyymmdd')||'_'||:bhour||'-'||:ehour||'.html' report_name from dual;
set termout on;
spool &report_name;
select output from table(dbms_workload_repository.awr_report_html(:dbid,:inst_num,:bid,:eid,0 ));
spool off; set termout on;
clear columns sql;
ttitle off;
btitle off;
repfooter off;
undefine report_name
3.myawr.sql