Search

Join this Blog for direct reference of any post into your inbox. To join just click on "Join this Site" under "Follower" then Login with your Email.*** DECLARATION: I Maintain this Blog for Helping Myself While at Work and Welcome any body Needing Help!!!.*** CAUTION: Using any of the script from this Blog may contain at Own Risk. These scripts May or May not have been Tested.***

Sunday, 13 May 2012

Script: To Automate Generation AWR for a day

set serveroutput on
set feedback off
spool rpt.sql
set termout off
set linesize 200
set trimspool on
DECLARE
dbid v$database.dbid%TYPE;
dbname v$database.name%TYPE;
-- snap history get snap ids
CURSOR l_cur IS
SELECT snap_id, begin_interval_time, end_interval_time FROM dba_hist_snapshot
WHERE begin_interval_time >= trunc(SYSDATE) – 1 and end_interval_time <= trunc(SYSDATE) + 1/24
ORDER BY begin_interval_time;
l_rec l_cur%ROWTYPE;
l_prev_rec l_cur%ROWTYPE;
l_begin_date VARCHAR2(30);
l_end_date VARCHAR2(30);
BEGIN
SELECT dbid, name INTO dbid, dbname FROM v$database;

OPEN l_cur;
FETCH l_cur INTO l_rec;
WHILE l_cur%FOUND
LOOP
l_prev_rec := l_rec;
FETCH l_cur INTO l_rec;
IF l_cur%found THEN
l_begin_date := to_char(l_prev_rec.BEGIN_INTERVAL_TIME, 'YYYYMMDD_HH24MI');
l_end_date := to_char(l_prev_rec.end_interval_time, 'YYYYMMDD_HH24MI');
dbms_output.put_line('-- ' || l_begin_date
|| ' – ' || l_end_date);
dbms_output.put_line('define inst_num = 1;');
dbms_output.put_line('define num_days = 0;');
dbms_output.put_line('define inst_name = ''' || dbname || ''';');
dbms_output.put_line('define db_name = ''' || dbname || ''';');
dbms_output.put_line('define dbid = ' || dbid || ';');
dbms_output.put_line('define report_type = ''html'';');
dbms_output.put_line('define begin_snap = ' || l_prev_rec.snap_id);
dbms_output.put_line('define end_snap = ' || l_rec.snap_id);
dbms_output.put_line('define report_name = /tmp/awr_' || dbname
|| '_' || l_begin_date || '_' || l_end_date || '.html');
dbms_output.put_line('@?/rdbms/admin/awrrpti');
END IF;
END LOOP;
CLOSE l_cur;
END;
/

0 comments:

Post a Comment