收集awr

select max(snap_id) from dba_hist_snapshot;

begin
dbms_workload_repository.create_snapshot();
end;
/

@?/rdbms/admin/awrrpt.sql

set serveroutput on;
set feedback off;
set linesize 300;
prompt ***************************************************************;
prompt usage:
prompt 1.noninteractive : SQL>@awrrpt_batch.sql dbid instance_num start_snap end_snap;
prompt 2.interactive : SQL>@awrrpt_batch.sql;
prompt author : Darren_Guo
prompt ***************************************************************;
pause press enter to continue or ctrl-c to exit.;
col snap_id for 999999999;
col snap dbid 9999999999;
col startup_time for a30;
col begin_interval_time for a30;
col end_interval_time for a30;
select dbid,snap_id,instance_number,startup_time,begin_interval_time,end_interval_time from dba_hist_snapshot order by dbid,instance_number,snap_id;
exec dbms_output.put_line(chr(13)||chr(10)||'please enter dbid,inst_number,start and end snap_id:');
declare
v_dbid number;
v_instance number;
v_b_id number;
v_e_id number;
v_code number;
v_errm varchar2(300);
v_sql varchar2(300);
v_html varchar2(20000);
cur_awrrpt_html SYS_REFCURSOR;
cur_snapshot SYS_REFCURSOR;
fileID utl_file.file_type;
v_filename varchar2(30);
v_snap_id number;
v_startup_time timestamp(3);
v_begin_snap_time timestamp(3);
v_end_snap_time timestamp(3);
v_dpath varchar2(60);
begin
v_dbid:=&1;
v_instance:=&2;
v_b_id:=&3;
v_e_id:=&4;
dbms_output.put_line(chr(13)||chr(10)||'awrrpt report files:');
for k in v_b_id..v_e_id-1 loop
v_filename:='pmdb_'||k||'_'||(k+1)||'.html'; 
fileID:=utl_file.fopen('DATA_PUMP_DIR',v_filename,'a',32767);
v_sql:='select output from table(dbms_workload_repository.awr_report_html('||v_dbid||','||v_instance||','||k||','||(k+1)||',8))';
open cur_awrrpt_html for v_sql;
loop
exit when cur_awrrpt_html%notfound;
fetch cur_awrrpt_html into v_html;
utl_file.put_line(fileID,v_html);
end loop;
utl_file.fclose(fileID);
execute immediate 'select directory_path from dba_directories where directory_name=:dname' into v_dpath using 'DATA_PUMP_DIR'; 
dbms_output.put_line(v_dpath||v_filename);
end loop;
exception
when others then
v_code:=SQLCODE;
v_errm:=SQLERRM;
dbms_output.put_line('ERROR CODE'||v_code||':'||v_errm);
end;
/

usage:
1.noninteractive : SQL>@awrrpt_batch.sql dbid instance_num start_snap end_snap;
2.interactive : SQL>@awrrpt_batch.sql;
author : Darren_Guo
***************************************************************;
pause press enter to continue or ctrl-c to exit.;