用儲存過程封裝awrrpt指令碼（五）

做了一個儲存過程，封裝了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