生成awr报告

create or replace procedure j1_dw.generate_multiple_awr_reports (l_snap_start varchar2, --格式yyyyMMdd hh24:mi（如20130603 15:15）
                                                            l_snap_end   varchar2 --格式yyyyMMdd hh24:mi（如20130603 15:15）
                                                            ) is
-----------------------------------------------------------------
--生成awr报告
--在j1_ldm或j1_dw用户下执行
--在sys用户下执行
--grant select on v_$database to j1_ldm(或j1_dw);
--grant select on dba_hist_snapshot to j1_ldm(或j1_dw);
--GRANT EXECUTE ON dbms_workload_repository TO j1_ldm(或j1_dw);
--此过程用rac库的第一个节点跑，建立directory目录为/goldengate/awr（此目录为报告生成目录）
--输入参数格式yyyyMMdd hh24:mi（如20130603 15:15）
-----------------------------------------------------------------

  l_dir              VARCHAR2(50) := 'AWR_REPORTS_DIR';
  l_last_snap_1        NUMBER := NULL;
  l_dbid               v$database.dbid%TYPE;
  l_file_1             UTL_FILE.file_type;
  l_file_name          VARCHAR(50);
  l_last_snap_2        NUMBER := NULL;
  l_file_2             UTL_FILE.file_type;

BEGIN
  SELECT dbid
  INTO   l_dbid
  FROM   v$database;

  FOR cur_snap_1 IN (SELECT snap_id,to_char(begin_interval_time,'yyyyMMdd hh24:mi') start_time_1,
                            to_char(end_interval_time,'yyyyMMdd hh24:mi') end_time_1
                   FROM   dba_hist_snapshot
                   WHERE  instance_number = 1
                   AND    to_char(begin_interval_time,'yyyyMMdd hh24:mi') >= l_snap_start AND to_char(end_interval_time,'yyyyMMdd hh24:mi') <= l_snap_end
                   ORDER BY snap_id)
  LOOP
    IF l_last_snap_1 IS NOT NULL THEN
      l_file_1 := UTL_FILE.fopen(l_dir, 'awr_' || cur_snap_1.start_time_1 || '_' || cur_snap_1.end_time_1 || '_1.htm', 'w', 32767);

      FOR cur_rep_1 IN (SELECT output
                      FROM   TABLE(DBMS_WORKLOAD_REPOSITORY.awr_report_html(l_dbid, 1, l_last_snap_1, cur_snap_1.snap_id)))
      LOOP
        UTL_FILE.put_line(l_file_1, cur_rep_1.output);
      END LOOP;
      UTL_FILE.fclose(l_file_1);
    END IF;
    l_last_snap_1 := cur_snap_1.snap_id;
  END LOOP;

  FOR cur_snap_2 IN (SELECT snap_id,to_char(begin_interval_time,'yyyyMMdd hh24:mi') start_time_2,
                            to_char(end_interval_time,'yyyyMMdd hh24:mi') end_time_2
                   FROM   dba_hist_snapshot
                   WHERE  instance_number = 2
                   AND    to_char(begin_interval_time,'yyyyMMdd hh24:mi') >= l_snap_start AND to_char(end_interval_time,'yyyyMMdd hh24:mi') <= l_snap_end
                   ORDER BY snap_id)
  LOOP
    IF l_last_snap_2 IS NOT NULL THEN
      l_file_2 := UTL_FILE.fopen(l_dir, 'awr_' || cur_snap_2.start_time_2 || '_' || cur_snap_2.end_time_2 || '_2.htm', 'w', 32767);

      FOR cur_rep_2 IN (SELECT output
                      FROM   TABLE(DBMS_WORKLOAD_REPOSITORY.awr_report_html(l_dbid, 2, l_last_snap_2, cur_snap_2.snap_id)))
      LOOP
        UTL_FILE.put_line(l_file_2, cur_rep_2.output);
      END LOOP;
      UTL_FILE.fclose(l_file_2);
    END IF;
    l_last_snap_2 := cur_snap_2.snap_id;
  END LOOP;

EXCEPTION
  WHEN OTHERS THEN
    IF UTL_FILE.is_open(l_file_1) THEN
      UTL_FILE.fclose(l_file_1);
    END IF;
    RAISE;
END;
/

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26613085/viewspace-1149174/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26613085/viewspace-1149174/