做了一个存储过程，封装了awr的report的功能。
根据11.2.0.3的TYPE的变化，修改了定义，新增一个变量控制是否生成RAC的GLOBAL报告，默认不生成全局报告。将DBNAME输入改变为DBID，解决DBNAME同名问题。
用存储过程封装awrrpt脚本（一）：http://yangtingkun.itpub.net/post/468/515180
用存储过程封装awrrpt脚本（二）：http://yangtingkun.itpub.net/post/468/515229
用存储过程封装awrrpt脚本（三）：http://yangtingkun.itpub.net/post/468/515271
用存储过程封装awrrpt脚本（四）：http://yangtingkun.itpub.net/post/468/517487
修改后的过程如下：
SQL> CREATE OR REPLACE PROCEDURE P_AWR_REPORT ( 2 P_BEGIN IN VARCHAR2, 3 P_END IN VARCHAR2, 4 P_DIR IN VARCHAR2, 5 P_DBID IN NUMBER DEFAULT NULL, 6 P_PERINTERVAL IN BOOLEAN DEFAULT FALSE, 7 P_GLOBAL IN BOOLEAN DEFAULT FALSE) AS 8 TYPE T_VARCHAR IS TABLE OF VARCHAR2(8000 CHAR) INDEX BY BINARY_INTEGER; 9 V_REPORT T_VARCHAR; 10 V_FILE UTL_FILE.FILE_TYPE; 11 TYPE T_NUM IS TABLE OF NUMBER INDEX BY BINARY_INTEGER; 12 TYPE T_DATE IS TABLE OF DATE INDEX BY BINARY_INTEGER; 13 V_INSTANCE T_NUM; 14 V_SNAP_BEG T_NUM; 15 V_SNAP_END T_NUM; 16 V_BEGIN_DATE T_DATE; 17 V_END_DATE T_DATE; 18 V_BEGIN NUMBER; 19 V_END NUMBER; 20 V_DBID NUMBER; 21 V_DBNAME V$DATABASE.NAME%TYPE; 22 V_VERSION V$VERSION.BANNER%TYPE; 23 BEGIN 24 25 IF P_DBID IS NOT NULL THEN 26 SELECT DISTINCT DBID, DB_NAME 27 INTO V_DBID, V_DBNAME 28 FROM DBA_HIST_DATABASE_INSTANCE 29 WHERE DBID = P_DBID; 30 ELSE 31 SELECT DBID, NAME 32 INTO V_DBID, V_DBNAME 33 FROM V$DATABASE; 34 END IF; 35 36 SELECT DISTINCT INSTANCE_NUMBER, 37 FIRST_VALUE(SNAP_ID) OVER(PARTITION BY INSTANCE_NUMBER ORDER BY ABS(CAST (END_INTERVAL_TIME AS DATE) - TO_DATE(P_BEGIN, 'YYYYMMDDHH24MISS'))), 38 FIRST_VALUE(END_INTERVAL_TIME) OVER(PARTITION BY INSTANCE_NUMBER ORDER BY ABS(CAST (END_INTERVAL_TIME AS DATE) - TO_DATE(P_BEGIN, 'YYYYMMDDHH24MISS'))) 39 BULK COLLECT INTO V_INSTANCE, V_SNAP_BEG, V_BEGIN_DATE 40 FROM DBA_HIST_SNAPSHOT 41 WHERE DBID = V_DBID 42 ORDER BY INSTANCE_NUMBER; 43 44 FOR I IN V_INSTANCE.FIRST..V_INSTANCE.LAST LOOP 45 BEGIN 46 SELECT DISTINCT FIRST_VALUE(SNAP_ID) OVER(ORDER BY ABS(CAST (END_INTERVAL_TIME AS DATE) - TO_DATE(P_END, 'YYYYMMDDHH24MISS'))), 47 FIRST_VALUE(END_INTERVAL_TIME) OVER(ORDER BY ABS(CAST (END_INTERVAL_TIME AS DATE) - TO_DATE(P_END, 'YYYYMMDDHH24MISS'))) 48 INTO V_SNAP_END(I), V_END_DATE(I) 49 FROM DBA_HIST_SNAPSHOT 50 WHERE SNAP_ID > V_SNAP_BEG(I) 51 AND DBID = V_DBID 52 AND INSTANCE_NUMBER = V_INSTANCE(I); 53 EXCEPTION 54 WHEN NO_DATA_FOUND THEN 55 V_INSTANCE.DELETE(I); 56 END; 57 END LOOP; 58 59 IF V_INSTANCE.COUNT = 1 60 THEN 61 V_BEGIN := V_SNAP_BEG(1); 62 IF P_PERINTERVAL = FALSE 63 THEN 64 V_END := V_SNAP_END(1); 65 ELSE 66 V_END := V_SNAP_BEG(1) + 1; 67 SELECT END_INTERVAL_TIME 68 INTO V_END_DATE(1) 69 FROM DBA_HIST_SNAPSHOT 70 WHERE DBID = V_DBID 71 AND INSTANCE_NUMBER = 1 72 AND SNAP_ID = V_END; 73 END IF; 74 WHILE(V_END <= V_SNAP_END(1)) LOOP 75 V_FILE := UTL_FILE.FOPEN( 76 P_DIR, 77 'awr_' || V_DBNAME || '_' || V_INSTANCE(1) || '_' 78 || TO_CHAR(V_BEGIN_DATE(1), 'YYYYMMDD_HH24MISS') || '_' 79 || TO_CHAR(V_END_DATE(1), 'YYYYMMDD_HH24MISS') || '.html', 80 'w', 81 32767); 82 83 SELECT OUTPUT 84 BULK COLLECT INTO V_REPORT 85 FROM TABLE( 86 DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_HTML( 87 V_DBID, 88 V_INSTANCE(1), 89 V_BEGIN, 90 V_END, 91 0)); 92 FOR I IN 1..V_REPORT.COUNT LOOP 93 UTL_FILE.PUT_LINE(V_FILE, V_REPORT(I)); 94 END LOOP; 95 UTL_FILE.FCLOSE(V_FILE); 96 V_BEGIN := V_BEGIN + 1; 97 V_END := V_END + 1; 98 V_BEGIN_DATE(1) := V_END_DATE(1); 99 SELECT MAX(END_INTERVAL_TIME) 100 INTO V_END_DATE(1) 101 FROM DBA_HIST_SNAPSHOT 102 WHERE DBID = V_DBID 103 AND INSTANCE_NUMBER = 1 104 AND SNAP_ID = V_END; 105 END LOOP; 106 ELSE 107 SELECT BANNER 108 INTO V_VERSION 109 FROM V$VERSION 110 WHERE BANNER LIKE 'CORE%'; 111 IF (TO_NUMBER(LTRIM(SUBSTR(V_VERSION, 5, INSTR(V_VERSION, '.', 1) - 5), CHR(9))) > 11 112 OR (TO_NUMBER(LTRIM(SUBSTR(V_VERSION, 5, INSTR(V_VERSION, '.', 1) - 5), CHR(9))) = 11 113 AND TO_NUMBER(SUBSTR(V_VERSION, INSTR(V_VERSION, '.', 1) + 1, INSTR(V_VERSION, '.', 1, 2) - INSTR(V_VERSION, '.', 1))) = 2)) 114 AND P_GLOBAL 115 THEN 116 V_BEGIN := V_SNAP_BEG(1); 117 IF P_PERINTERVAL = FALSE 118 THEN 119 V_END := V_SNAP_END(1); 120 ELSE 121 V_END := V_SNAP_BEG(1) + 1; 122 SELECT END_INTERVAL_TIME 123 INTO V_END_DATE(1) 124 FROM DBA_HIST_SNAPSHOT 125 WHERE DBID = V_DBID 126 AND INSTANCE_NUMBER = 1 127 AND SNAP_ID = V_END; 128 END IF; 129 WHILE(V_END <= V_SNAP_END(1)) LOOP 130 V_FILE := UTL_FILE.FOPEN( 131 P_DIR, 132 'awr_' || V_DBNAME || '_' || 'RAC' || '_' 133 || TO_CHAR(V_BEGIN_DATE(1), 'YYYYMMDD_HH24MISS') || '_' 134 || TO_CHAR(V_END_DATE(1), 'YYYYMMDD_HH24MISS') || '.html', 135 'w', 136 32767); 137 138 EXECUTE IMMEDIATE 139 'SELECT OUTPUT FROM TABLE(DBMS_WORKLOAD_REPOSITORY.AWR_GLOBAL_REPORT_HTML(:DBID, '''', :BEGIN, :END, 0))' 140 BULK COLLECT INTO V_REPORT 141 USING V_DBID, V_BEGIN, V_END; 142 143 FOR I IN 1..V_REPORT.COUNT LOOP 144 UTL_FILE.PUT_LINE(V_FILE, V_REPORT(I)); 145 END LOOP; 146 UTL_FILE.FCLOSE(V_FILE); 147 V_BEGIN := V_BEGIN + 1; 148 V_END := V_END + 1; 149 V_BEGIN_DATE(1) := V_END_DATE(1); 150 SELECT MAX(END_INTERVAL_TIME) 151 INTO V_END_DATE(1) 152 FROM DBA_HIST_SNAPSHOT 153 WHERE DBID = V_DBID 154 AND INSTANCE_NUMBER = 1 155 AND SNAP_ID = V_END; 156 END LOOP; 157 ELSE 158 FOR I IN V_INSTANCE.FIRST..V_INSTANCE.LAST LOOP 159 V_BEGIN := V_SNAP_BEG(I); 160 IF P_PERINTERVAL = FALSE 161 THEN 162 V_END := V_SNAP_END(I); 163 ELSE 164 V_END := V_SNAP_BEG(I) + 1; 165 SELECT END_INTERVAL_TIME 166 INTO V_END_DATE(I) 167 FROM DBA_HIST_SNAPSHOT 168 WHERE DBID = V_DBID 169 AND INSTANCE_NUMBER = V_INSTANCE(I) 170 AND SNAP_ID = V_END; 171 END IF; 172 WHILE(V_END <= V_SNAP_END(I)) LOOP 173 V_FILE := UTL_FILE.FOPEN( 174 P_DIR, 175 'awr_' || V_DBNAME || '_' || V_INSTANCE(I) || '_' 176 || TO_CHAR(V_BEGIN_DATE(I), 'YYYYMMDD_HH24MISS') || '_' 177 || TO_CHAR(V_END_DATE(I), 'YYYYMMDD_HH24MISS') || '.html', 178 'w', 179 32767); 180 181 SELECT OUTPUT 182 BULK COLLECT INTO V_REPORT 183 FROM TABLE( 184 DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_HTML( 185 V_DBID, 186 V_INSTANCE(I), 187 V_BEGIN, 188 V_END, 189 0)); 190 FOR I IN 1..V_REPORT.COUNT LOOP 191 UTL_FILE.PUT_LINE(V_FILE, V_REPORT(I)); 192 END LOOP; 193 UTL_FILE.FCLOSE(V_FILE); 194 V_BEGIN := V_BEGIN + 1; 195 V_END := V_END + 1; 196 V_BEGIN_DATE(I) := V_END_DATE(I); 197 SELECT MAX(END_INTERVAL_TIME) 198 INTO V_END_DATE(I) 199 FROM DBA_HIST_SNAPSHOT 200 WHERE DBID = V_DBID 201 AND INSTANCE_NUMBER = V_INSTANCE(I) 202 AND SNAP_ID = V_END; 203 END LOOP; 204 END LOOP; 205 END IF; 206 END IF; 207 END; 208 / PROCEDURE created. |
运行过程：
SQL> EXEC p_awr_report('20120205100000', '20120205120000', 'ORA11G_DIR', 1972453558, FALSE, TRUE) PL/SQL PROCEDURE successfully completed. SQL> host ls -l *20120205*.html -rw-r--r-- 1 ora11g oinstall 564770 Feb 10 16:19 awr_ECARD_RAC_20120205_100035_20120205_120002.html SQL> EXEC p_awr_report('20120205100000', '20120205120000', 'ORA11G_DIR', 1972453558) PL/SQL PROCEDURE successfully completed. SQL> host ls -l *20120205*.html -rw-r--r-- 1 ora11g oinstall 642609 Feb 10 16:20 awr_ECARD_1_20120205_100035_20120205_120002.html -rw-r--r-- 1 ora11g oinstall 619464 Feb 10 16:20 awr_ECARD_2_20120205_100035_20120205_120002.html -rw-r--r-- 1 ora11g oinstall 564770 Feb 10 16:19 awr_ECARD_RAC_20120205_100035_20120205_120002.html |