Скрипт для периодического получения отчетов AWR в файл : Oracle

/* ---------------------------------------------------------------------------
 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
           
 Date    : 09.09.2009.
 Author  : Damir Vadas
 
 Remarks : Run as privileged user
 
 Changes (DD.MM.YYYY, Name, CR/TR#):
 --------------------------------------------------------------------------- */
 
set serveroutput on
set linesize 166
set pagesize 600
set trimout on
 
DECLARE
  cursor c_instance is
    SELECT instance_number, instance_name
    FROM   gv$instance
    ORDER BY 1
  ;
 
  c_dir         CONSTANT VARCHAR2(256) := '/home/oracle';
  v_dir         VARCHAR2(256) ;
   
  v_dbid        v$database.dbid%TYPE;
  v_dbname      v$database.name%TYPE;
  v_inst_num    v$instance.instance_number%TYPE := 1;
  v_begin       NUMBER;
  v_end         NUMBER;
  v_start_date  VARCHAR2(20);
  v_end_date    VARCHAR2(20);
  v_options     NUMBER := 8; -- 0=no options, 8=enable addm feature
  v_file        UTL_FILE.file_type;
  v_file_name   VARCHAR(50);
 
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;
/