Script to generate AWR report from remote sql client

花菜土豆粉發表於2014-12-27
REMSQLclient

通常我們都是通過登入伺服器，然後執行$ORACLE_HOME/rdbms/admin/awrrpt.sql指令碼來生成awr報告，同時Oracle還提供了DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_HTML儲存過程，方便我們在不方便登入伺服器的情況下獲取AWR報告。
通過如下指令碼，可以簡單的在dbms_output中獲取到AWR報告的內容：

sqlDECLARE
  DBID           NUMBER;
  INST_ID        NUMBER;
  BID            NUMBER;
  EID            NUMBER;
  DB_UNIQUE_NAME VARCHAR2(30);
  STARTTIME      DATE;
  ENDTIME        DATE;
BEGIN
  STARTTIME := TO_DATE(`2014-12-15 18:00`, `YYYY-MM-DD HH24:MI`) - 1 / 24;
  ENDTIME   := TO_DATE(`2014-12-15 19:00`, `YYYY-MM-DD HH24:MI`);

  SELECT MIN(SNAP_ID), MAX(SNAP_ID)
    INTO BID, EID
    FROM DBA_HIST_SNAPSHOT DHS
   WHERE TRUNC(DHS.BEGIN_INTERVAL_TIME, `HH24`) >= TRUNC(STARTTIME, `HH24`)
     AND TRUNC(DHS.END_INTERVAL_TIME, `HH24`) <= TRUNC(ENDTIME, `HH24`);
  SELECT DBID, INST_ID, DB_UNIQUE_NAME
    INTO DBID, INST_ID, DB_UNIQUE_NAME
    FROM GV$DATABASE;

  FOR C1_REC IN (SELECT OUTPUT
                   FROM TABLE(DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_HTML(DBID,
                                                                       INST_ID,
                                                                       BID,
                                                                       EID))) LOOP
    DBMS_OUTPUT.PUT_LINE(C1_REC.OUTPUT);
  END LOOP;

END;
/

相關文章