RE: Grab a series of AWR reports

You could start with the following SQL script and write a .bat file to call it. 
 You can obviously wrap NVL functions around each of the parameters to default 
to certain values.  This script was obviously written for a RAC env.

SET FEEDBACK off HEADING off LINESIZE 1500

VARIABLE DBID NUMBER;
BEGIN
   SELECT dbid INTO :DBID FROM v$database;
END;
/

SET ECHO on
DEFINE INSTANCE_ID=&1;
DEFINE BEGIN_DATETIME='&2';
DEFINE END_DATETIME='&3';
SET ECHO off

SELECT *
  FROM TABLE(dbms_workload_repository.awr_report_html(
             (:DBID), &INSTANCE_ID,
             (SELECT MIN(LEAST(snap_id))
                FROM dba_hist_snapshot
               WHERE end_interval_time >=
                     TO_DATE('&BEGIN_DATETIME', 'DD-MON-YY HH24:MI:SS')
                 AND dbid = :DBID),
             (SELECT MAX(GREATEST(snap_id))
                FROM dba_hist_snapshot
               WHERE end_interval_time <=
                     TO_DATE('&END_DATETIME', 'DD-MON-YY HH24:MI:SS')
                 AND dbid = :DBID), 8));

DAVID HERRING
DBA
Acxiom Corporation
EML   dave.herring@xxxxxxxxxx
TEL    630.944.4762
MBL   630.430.5988 
1501 Opus Pl, Downers Grove, IL 60515, USA
WWW.ACXIOM.COM  
________________________________________
The information contained in this communication is confidential, is intended 
only for the use of the recipient named above, and may be legally privileged. 
If the reader of this message is not the intended recipient, you are hereby 
notified that any dissemination, distribution or copying of this communication 
is strictly prohibited. If you have received this communication in error, 
please resend this communication to the sender and delete the original message 
or any copy of it from your computer system. Thank you.

From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On 
Behalf Of Uzzell, Stephan
Sent: Friday, August 12, 2011 12:26 PM
To: oracle-l@xxxxxxxxxxxxx
Subject: Grab a series of AWR reports

Hi Listers,

Asking here before I try to re-invent the wheel. Does anyone (using Windows - 
so no cron or sh scripts please) have a script that will allow the generation 
of a sequence of AWR reports? I want to collect a series of snapshots every 
morning.

10.2.0.3 on Windows if that matters.

Thanks,
stephan

Stephan Uzzell

--
http://www.freelists.org/webpage/oracle-l

Other related posts: