输入时间,格式为yyyymmdd hh24:mi
set echo off;
set veri off;
set feedback off;
set serverout on;
set heading off;
set linesize 1500;
SET AUTOPRINT OFF;
variable dbid number;
variable inst_num number;
variable btim varchar2(30);
variable etim varchar2(30);
variable inst_name varchar2(30);
variable path varchar2(30);
prompt please enter begin_time,end_time and path(default current path) where you want to save reports;
prompt
prompt begin_time and end_time format : yyyymmdd hh24:mi
prompt
prompt path format for unix/linux like : /tmp/awr_report/
prompt
prompt path format for win like: e:\awr_report\
prompt
begin
:btim := '&begin_time';
:etim := '&end_time';
:path := '&path';
end;
/
prompt progressing...
set termout off
spool batch_awr_stat.sql
declare
v_sql varchar2(1000);
begin
select dbid into :dbid from v$database;
select instance_number, instance_name
into :inst_num, :inst_name
from v$instance;
for v_info in (select distinct snap_id, btim, etim
from (select snap_id,
to_char(end_interval_time, 'yyyymmddhh24mi') btim,
to_char(lead(end_interval_time, 1)
over(order by snap_id),
'yyyymmddhh24mi') etim,
lead(end_interval_time, 1) over(order by snap_id) lt
from dba_hist_snapshot
where to_char(end_interval_time, 'yyyymmdd hh24:mi') between
:btim and :etim
and instance_number = :inst_num)
where to_char(lt, 'yyyymmdd hh24:mi') between
:btim and :etim) loop
select 'spool ' || :path || :inst_name || '_' || v_info.btim || '_' ||
v_info.etim || '.html;' || chr(10) ||
'select output from table(dbms_workload_repository.awr_report_html(' ||
:dbid || ',' || :inst_num || ',' || v_info.snap_id || ',' ||
(v_info.snap_id + 1) || ',' || '0));' || chr(10) || 'spool off;'
into v_sql
from dual;
dbms_output.put_line(v_sql);
end loop;
end;
/
spool off
@batch_awr_stat.sql
set termout on
prompt
prompt script executed completely!
prompt
set veri on;
set feedback on;
set serverout off;
set heading on;
SET AUTOPRINT on;
set echo on;