/* ---------------------------------------------------------------------------
Filename: create_awr_report_for_database.sql
CR/TR# :
Purpose : In directory defined with v_dir, create awr reports for ALL instances (RAC)
time for analyse is 07-18, so put in crontab to run at 18:16 on daily basis
BEGIN
-- get database id
SELECT dbid, name
INTO v_dbid, v_dbname
FROM v$database;
-- get end snapshot id
SELECT MAX(snap_id)
INTO v_end
FROM dba_hist_snapshot
WHERE to_char(begin_interval_time,'HH24') = '18';
dbms_output.put_line('end snap_id '||v_end);
-- get start snapshot id
SELECT MAX(snap_id)
INTO v_begin
FROM dba_hist_snapshot
WHERE to_char(begin_interval_time,'HH24') = '07'
AND snap_id < v_end;
dbms_output.put_line('begin snap_id '||v_begin);
SELECT to_char(begin_interval_time,'YYMMDD_HH24MI')
INTO v_start_date
FROM dba_hist_snapshot
WHERE snap_id = v_begin
AND instance_number = v_inst_num
;
dbms_output.put_line('v_start_date '||v_start_date);
SELECT to_char(begin_interval_time,'HH24MI')
INTO v_end_date
FROM dba_hist_snapshot
WHERE snap_id = v_end
AND instance_number = v_inst_num
;
dbms_output.put_line('v_end_date '||v_end_date);
-- Thanx to Yu Denis Sun - we must have directory defined as v_dir value!
execute immediate('create or replace directory xx_some_temp_dir as '''||v_dir||'''');
-- let's go to real work...write awrs to files...
FOR v_instance IN c_instance LOOP
dbms_output.put_line('v_instance.instance_name:'||v_instance.instance_name);
v_file := UTL_FILE.fopen('XX_SOME_TEMP_DIR', 'awr_' || v_instance.instance_name ||'_'|| v_instance.instance_number || '_' || v_start_date || '_' || v_end_date || '.html', 'w', 32767);
FOR c_report IN (
SELECT output
FROM TABLE(dbms_workload_repository.awr_report_html( v_dbid,
v_instance.instance_number,
v_begin,
v_end,
v_options
)
)
) LOOP
UTL_FILE.PUT_LINE(v_file, c_report.output);
END LOOP;
UTL_FILE.fclose(v_file);
END LOOP;
execute immediate('drop directory xx_some_temp_dir');
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLERRM);
IF UTL_FILE.is_open(v_file) THEN
UTL_FILE.fclose(v_file);
END IF;
BEGIN
execute immediate('drop directory xx_some_temp_dir');
EXCEPTION
WHEN OTHERS THEN
null;
END;
END;
/