1 PACKAGE dbms_undo_adv IS
2 FUNCTION undo_info(table_space_name OUT VARCHAR2,
3 table_space_size OUT NUMBER,
4 auto_extend OUT BOOLEAN,
5 undo_retention OUT NUMBER,
6 retention_guarantee OUT BOOLEAN) RETURN BOOLEAN;
7 -- This function is used to get information about undo tablespace of the
8 -- current instnace. The output parameters are meaningful only when
9 -- return value is TRUE.
10
11 -- Input Parameters:
12 -- None.
13
14 -- Output Parameters:
15 -- table_space_name: Name of the current undo tablespace the instance
16 -- is using.
17 -- table_space_size: If the undo tablespace is fixed-sized, it is
18 -- the size of the undo tablespace in MB. If the
19 -- tablespace is auto_extensiable, it is the max
20 -- possible size of the undo tablespace in MB.
21 -- auto_extend: TRUE if the undo tablespace is extensiable. FALSE otherwise.
22 -- undo_retention: The value of init.ora parameter "undo_retention".
23 -- retention_guarantee: TRUE if undo tablespace has guaranteed retention.
24 -- FALSE otherwise.
25
26 -- RETURN:
27 -- TRUE if init.ora parameters undo_managment is auto.
28 -- FALSE otherwise or some information not available.
29
30 -- EXCEPTIONS:
31 -- None.
32
33 FUNCTION undo_autotune(autotune_enabled OUT BOOLEAN) RETURN BOOLEAN;
34 -- This function is used to find out if auto tuning of undo retention
35 -- is enabled for the current undo tablespace. The output parameter is
36 -- meaningful only when return value is TRUE.
37
38 -- Input Parameters:
39 -- None.
40
41 -- Output Parameters:
42 -- autotune_enabled: TRUE if auto tuning of undo retention is enabled
43 -- FALSE otherwise.
44
45 -- RETURN:
46 -- TRUE if autotune_enabled output parameter has been set up properly.
47 -- FALSE otherwise or some information is not available.
48
49 -- EXCEPTIONS:
50 -- None.
51
52 FUNCTION longest_query(starttime IN DATE, endtime IN DATE) RETURN NUMBER;
53 FUNCTION longest_query RETURN NUMBER;
54 FUNCTION longest_query(s1 IN NUMBER, s2 IN NUMBER) RETURN NUMBER;
55 -- This function returns the length of the longest query for a given period.
56 -- If the information about the required period is not available, 0 will
57 -- be returned.
58
59 -- Input Parameters:
60 -- starttime: Start time of the required period.
61 -- endtime: End time of the requied period.
62 -- s1: Begin snap shot id, if want to get information from SWRF.
63 -- s2: End snap shot id, if want to get information from SWRF.
64
65 -- Output Parameters:
66 -- None.
67
68 -- Return:
69 -- length of the longest query in seconds. 0 if information about required
70 -- period not available.
71
72 -- Exceptions:
73 -- None.
74
75 FUNCTION required_retention(starttime IN DATE, endtime IN DATE)
76 RETURN NUMBER;
77 FUNCTION required_retention RETURN NUMBER;
78 FUNCTION required_retention(s1 IN NUMBER, s2 IN NUMBER)
79 RETURN NUMBER;
80 -- This function returns required value for init.ora parameter undo_retention
81 -- in order to prevent snap-shot-too-old error based on historical
82 -- information of given period. 0 will be returned if information about
83 -- the given period not available.
84
85 -- Input Parameters:
86 -- starttime: Start time of the given period.
87 -- endtime: End time of the given period.
88 -- s1: Begin snap shot id, if want to get information from SWRF.
89 -- s2: End snap shot id, if want to get information from SWRF.
90
91 -- Output Parameters:
92 -- None.
93
94 -- Return:
95 -- required value for init.ora parameter undo_retention. 0 if information
96 -- about the given period not available.
97
98 -- Exceptions:
99 -- None.
100
101 FUNCTION best_possible_retention(starttime IN DATE, endtime IN DATE)
102 RETURN NUMBER;
103 FUNCTION best_possible_retention RETURN NUMBER;
104 FUNCTION best_possible_retention(s1 IN NUMBER, s2 IN NUMBER)
105 RETURN NUMBER;
106 -- This function returns best possible value for init.ora parameter
107 -- undo_retention in order to maxmize the usage of current undo tablespace
108 -- based on historical information of given period. 0 will be returned if
109 -- information about the given period not available.
110
111 -- Input Parameters:
112 -- starttime: Start time of the given period.
113 -- endtime: End time of the given period.
114 -- s1: Begin snap shot id, if want to get information from SWRF.
115 -- s2: End snap shot id, if want to get information from SWRF.
116
117 -- Output Parameters:
118 -- None.
119
120 -- Return:
121 -- Best possible value for init.ora parameter undo_retention. 0 if
122 -- information about the given period not available.
123
124 -- Exceptions:
125 -- None.
126
127 -- Note:
128 -- For auto-extensiable undo tablespace, the best possible retention value
129 -- is based on the max size the undo tablespace can grow to. You may not
130 -- want your undo tablespace to grow to that size.
131
132 FUNCTION required_undo_size(retention IN NUMBER,
133 starttime IN DATE, endtime IN DATE)
134 RETURN NUMBER;
135 FUNCTION required_undo_size(retention IN NUMBER) RETURN NUMBER;
136 FUNCTION required_undo_size(retention IN NUMBER,
137 s1 IN NUMBER, s2 IN NUMBER)
138 RETURN NUMBER;
139 -- This function returns the required undo tablespace size in order to
140 -- satisfy undo_retention based on historical information of given period.
141 -- 0 will be returned if information about the given period not available.
142
143 -- Input Parameters:
144 -- retention: retention value you want to set for init.ora parameter
145 -- undo_retention.
146 -- starttime: Start time of the given period.
147 -- endtime: End time of the given period.
148 -- s1: Begin snap shot id, if want to get information from SWRF.
149 -- s2: End snap shot id, if want to get information from SWRF.
150
151 -- Output Parameters:
152 -- None.
153
154 -- Return:
155 -- Required size of undo tablespace in MB. 0 if
156 -- information about the given period not available.
157
158 -- Exceptions:
159 -- None.
160
161 FUNCTION required_undo_size(retention IN dbms_uadv_arr,
162 utbsize IN OUT dbms_uadv_arr,
163 starttime IN DATE, endtime IN DATE)
164 RETURN NUMBER;
165 FUNCTION required_undo_size(retention IN dbms_uadv_arr,
166 utbsize IN OUT dbms_uadv_arr)
167 RETURN NUMBER;
168 FUNCTION required_undo_size(retention IN dbms_uadv_arr,
169 utbsize IN OUT dbms_uadv_arr,
170 s1 IN NUMBER, s2 IN NUMBER)
171 RETURN NUMBER;
172 -- This function returns the required undo tablespace size given
173 -- undo retention value. 0 will be returned if information about
174 -- the given period not available. Both retention and utbsize
175 -- are varray type. It is caller's responsibility to initialize
176 -- utbsize array. This function simply appends results to utbsize
177 -- array.
178
179 -- Input Parameters:
180 -- retention: retention value you want to set for init.ora parameter
181 -- undo_retention.
182 -- starttime: Start time of the given period.
183 -- endtime: End time of the given period.
184 -- s1: Begin snap shot id, if want to get information from SWRF.
185 -- s2: End snap shot id, if want to get information from SWRF.
186
187 -- Output Parameters:
188 -- utbsize: Required size of undo tablespace in MB.
189
190 -- Return:
191 -- 0 if information about the given period not available.
192
193 -- Exceptions:
194 -- None.
195
196
197 FUNCTION undo_health(problem OUT VARCHAR2,
198 recommendation OUT VARCHAR2,
199 rationale OUT VARCHAR2,
200 retention OUT NUMBER,
201 utbsize OUT NUMBER) RETURN NUMBER;
202 FUNCTION undo_health(starttime IN DATE,
203 endtime IN DATE,
204 problem OUT VARCHAR2,
205 recommendation OUT VARCHAR2,
206 rationale OUT VARCHAR2,
207 retention OUT NUMBER,
208 utbsize OUT NUMBER) RETURN NUMBER;
209 FUNCTION undo_health(s1 IN NUMBER,
210 s2 IN NUMBER,
211 problem OUT VARCHAR2,
212 recommendation OUT VARCHAR2,
213 rationale OUT VARCHAR2,
214 retention OUT NUMBER,
215 utbsize OUT NUMBER) RETURN NUMBER;
216 -- This function is used to check if there is any problem with the current
217 -- setting of undo_retention and undo tablespace size based on historical
218 -- information of a given period. If the return value is 0, no problem is
219 -- found. Otherwise, parameter "problem" and "recommendation" are the
220 -- problem and recommendation on fixing the problem.
221
222 -- Input Parameters:
223 -- starttime: Start time of the given period.
224 -- endtime: End time of the given period.
225 -- s1: Begin snap shot id, if want to get information from SWRF.
226 -- s2: End snap shot id, if want to get information from SWRF.
227
228 -- Output Parameters:
229 -- problem: problem of the system. It can be:
230 -- "long running query may fail" or "undo tablespace cannot
231 -- support undo_retention".
232 -- recommendation: recommendation on fixing the problem found.
233 -- rationale: rationale for the recommendation.
234 -- retention: numerical value of retention if recommendation is to change
235 -- retention.
236 -- utbsize: numberical value of undo tablespace size in MB if
237 -- recommendation is to change undo tablespace size.
238
239 -- Return:
240 -- If return value is 0, undo tablespace is OK.
241 -- If return value is 1:
242 -- problem: Undo tablespace cannot support the specified undo_retention
243 -- or Undo tablespace cannot support auto tuning undo retention
244 -- recommendation: Size undo tablespace to utbsize MB;
245 -- If return value is 2:
246 -- problem: Long running queries may fail
247 -- recommendation: Set undo_retention to retention
248 -- If return value is 3:
249 -- problem: Undo tablespace cannot support the longest query
250 -- recommendation: Set undo_retention to retention and
251 -- Size undo tablespace to utbsize MB
252 -- If return value is 4:
253 -- problem: System does not have an online undo tablespace
254 -- recommendation: Online undo tablespace with size utbsize MB
255
256 -- Exceptions:
257 -- None.
258
259 FUNCTION undo_advisor(starttime IN DATE, endtime IN DATE, instance IN NUMBER)
260 RETURN VARCHAR2;
261 FUNCTION undo_advisor(instance IN NUMBER) RETURN VARCHAR2;
262 FUNCTION undo_advisor(s1 IN NUMBER, s2 IN NUMBER, instance IN NUMBER)
263 RETURN VARCHAR2;
264 -- This function uses advisor frame work to check if there is any problem
265 -- with the current instance. This function should be used when
266 -- undo_management is auto.
267
268 -- Input Parameters:
269 -- starttime: Start time of the given period.
270 -- endtime: End time of the given period.
271 -- s1: Begin snap shot id, if want to get information from SWRF.
272 -- s2: End snap shot id, if want to get information from SWRF.
273 -- instance: For now, please provide the instance id of the current
274 -- instance.
275
276 -- Output Parameters:
277 -- None.
278
279 -- Return:
280 -- Problems found in the current instance along with 1 or 2 recommendations
281 -- on fixing the problems. For each recommendation, there may be 1 or 2
282 -- actions.
283
284 -- Exceptions:
285 -- ORA-13516 SWRF not available
286 -- ORA-13618 invalid value for parameter instance
287 -- ORA-30014 system is running in non-AUM mode.
288 -- ORA-30029 no active undo tablespace.
289
290 FUNCTION rbu_migration(starttime IN DATE, endtime IN DATE) RETURN NUMBER;
291 FUNCTION rbu_migration RETURN NUMBER;
292 -- This functin returns required undo tablespace size if users want to
293 -- migrate from rbu to aum. This function should be called only when
294 -- undo management is manual.
295
296 -- Input Parameters:
297 -- starttime: Start time of the given period.
298 -- endtime: End time of the given period.
299
300 -- Output Parameters:
301 -- None.
302
303 -- Return:
304 -- Size of undo tablespace in MB.
305
306 -- Exceptions:
307 -- None.
308
309 END;
[Home] [Help]