--1 生成awr报告目录(linux下修改目录即可)
create directory awr as 'd:\AWR';
--3 生成awr报告
declare
v_dbid number;
v_inst_num number;
v_bid number;
v_eid number;
handle utl_file.file_type;
buf varchar2(20000);
Cursor cur(p_dbid number, p_inst_num number, p_bid number, p_eid number) is
select output
from table(dbms_workload_repository.awr_report_html(v_dbid,
v_inst_num,
v_bid,
v_eid,
0));
begin
select dbid into v_dbid from v$database;
select INSTANCE_NUMBER into v_inst_num from v$instance;
begin
select min(a.snap_id), max(a.snap_id)
into v_bid, v_eid
from dba_hist_snapshot a
where a.begin_interval_time >= trunc(sysdate - 1)
and a.end_interval_time < trunc(sysdate - 1) + 1
and a.instance_number = v_inst_num;
exception
when others then
v_bid := 0;
v_eid := 0;
end;
if v_bid > 0 and v_eid > 0 then
handle := utl_file.fopen('AWR',
'Inst1_awr' || '_' ||
to_char(sysdate - 1, 'yyyymmdd') || '.html',
'w',
10000);
for cur_data in cur(v_dbid, v_inst_num, v_bid, v_eid) loop
buf := cur_data.output;
utl_file.put(handle, buf);
utl_file.new_line(handle);
end loop;
utl_file.fclose(handle);
end if;
end;
create directory awr as 'd:\AWR';
--3 生成awr报告
declare
v_dbid number;
v_inst_num number;
v_bid number;
v_eid number;
handle utl_file.file_type;
buf varchar2(20000);
Cursor cur(p_dbid number, p_inst_num number, p_bid number, p_eid number) is
select output
from table(dbms_workload_repository.awr_report_html(v_dbid,
v_inst_num,
v_bid,
v_eid,
0));
begin
select dbid into v_dbid from v$database;
select INSTANCE_NUMBER into v_inst_num from v$instance;
begin
select min(a.snap_id), max(a.snap_id)
into v_bid, v_eid
from dba_hist_snapshot a
where a.begin_interval_time >= trunc(sysdate - 1)
and a.end_interval_time < trunc(sysdate - 1) + 1
and a.instance_number = v_inst_num;
exception
when others then
v_bid := 0;
v_eid := 0;
end;
if v_bid > 0 and v_eid > 0 then
handle := utl_file.fopen('AWR',
'Inst1_awr' || '_' ||
to_char(sysdate - 1, 'yyyymmdd') || '.html',
'w',
10000);
for cur_data in cur(v_dbid, v_inst_num, v_bid, v_eid) loop
buf := cur_data.output;
utl_file.put(handle, buf);
utl_file.new_line(handle);
end loop;
utl_file.fclose(handle);
end if;
end;
3 新建一个JOB,使用上述脚本每天按时生成AWR报告