1 PACKAGE dbms_index_utl
2 AUTHID CURRENT_USER AS
3
4 -- =========================================================================
5 -- DBMS_INDEX_UTL
6 -- =========================================================================
7 -- Meant for utility functions involving index rebuild.
8
9 -- =========================================================================
10 -- PROCEDURE: BUILD_SCHEMA_INDEXES
11 -- INPUT: list -> indexes will be rebuilt for this list of schema names.
12 -- The list should take the form:
13 -- <schema> (, <schema>)*
14 -- If the schema list is NULL, then all indexes
15 -- visible to the current user will be rebuilt.
16 -- just_unusable -> rebuild only the unusable index components? If true,
17 -- then only unusable components will be rebuilt. If
18 -- false, all components will be rebuilt.
19 -- locality -> locality of indexes to be rebuilt. If 'GLOBAL',
20 -- rebuild only global indexes. If 'LOCAL', rebuild
21 -- only local indexes. If 'ALL', rebuild both global
22 -- and local. Otherwise, error.
23 -- concurrent -> if true, use jobs to run rebuilds concurrently,
24 -- if not, individual jobs may still run in parallel
25 -- cont_after_err -> continue after errors? If true, then indexes will
26 -- be rebuilt even after errors. If false, after the
27 -- first error, processing will be halted.
28 -- max_slaves -> degree of parallelism. Maximum number of parallel
29 -- slaves to allocate during the build (across all
30 -- jobs). NULL (default) means the maximum available
31 -- in the system.
32 -- forced_degree -> degree of parallelism forced at session level
33 -- for ddl operations.
34 -- retry_online -> if true, retry online rebuild index when offline
35 -- rebuild fails with ORA-14327.
36 -- OUTPUT: num_errors -> number of failed index rebuilds (details on which
37 -- indexes failed are contained in the alert.log
38 -- USE: Implemented to provide an interface for finding and concurrently
39 -- and parallelely rebuilding all the index components (including
40 -- entire indexes, index partitions, and index subpartitions) for a
41 -- given list of schemas.
42 -- =========================================================================
43 PROCEDURE build_schema_indexes (
44 list IN VARCHAR2 DEFAULT NULL,
45 just_unusable IN BOOLEAN DEFAULT TRUE,
46 locality IN VARCHAR2 DEFAULT 'ALL',
47 concurrent IN BOOLEAN DEFAULT TRUE,
48 cont_after_err IN BOOLEAN DEFAULT FALSE,
49 max_slaves IN INT DEFAULT NULL,
50 forced_degree IN INT DEFAULT NULL,
51 retry_online IN BOOLEAN DEFAULT FALSE,
52 num_errors OUT NOCOPY PLS_INTEGER);
53
54 -- Convenience interface: num_errors is captured and removed internally
55 PROCEDURE build_schema_indexes (
56 list IN VARCHAR2 DEFAULT NULL,
57 just_unusable IN BOOLEAN DEFAULT TRUE,
58 locality IN VARCHAR2 DEFAULT 'ALL',
59 concurrent IN BOOLEAN DEFAULT TRUE,
60 cont_after_err IN BOOLEAN DEFAULT FALSE,
61 max_slaves IN INT DEFAULT NULL,
62 forced_degree IN INT DEFAULT NULL,
63 retry_online IN BOOLEAN DEFAULT FALSE);
64
65 -- =========================================================================
66 -- PROCEDURE: BUILD_TABLE_INDEXES
67 -- INPUT: list -> indexes will be rebuilt for this list of tables. The
68 -- list should take the form:
69 -- <owner>.<table> (, <owner>.<table>)*
70 -- If <owner> is left off, the table is assumed to be in
71 -- the schema of the current user.
72 -- If the table list is NULL, then all indexes
73 -- visible to the current user will be rebuilt.
74 -- just_unusable -> rebuild only the unusable index components? If true,
75 -- then only unusable components will be rebuilt. If
76 -- false, all components will be rebuilt.
77 -- locality -> locality of indexes to be rebuilt. If 'GLOBAL',
78 -- rebuild only global indexes. If 'LOCAL', rebuild
79 -- only local indexes. If 'ALL', rebuild both global
80 -- and local. Otherwise, error.
81 -- concurrent -> if true, use jobs to run rebuilds concurrently,
82 -- if not, individual jobs may still run in parallel
83 -- cont_after_err -> continue after errors? If true, then indexes will
84 -- be rebuilt even after errors. If false, after the
85 -- first error, processing will be halted.
86 -- max_slaves -> degree of parallelism. Maximum number of parallel
87 -- slaves to allocate during the build (across all
88 -- jobs). NULL (default) means the maximum available
89 -- in the system.
90 -- forced_degree -> degree of parallelism forced at session level
91 -- for ddl operations.
92 -- retry_online -> if true, retry online rebuild index when offline
93 -- rebuild fails with ORA-14327,
94 -- OUTPUT: num_errors -> number of failed index rebuilds (details on which
95 -- indexes failed are contained in the alert.log
96 -- USE: Implemented to provide an interface for finding and concurrently
97 -- and parallelely rebuilding all the index components (including
98 -- entire indexes, index partitions, and index subpartitions) for a
99 -- given list of tables.
100 -- =========================================================================
101 PROCEDURE build_table_indexes (
102 list IN VARCHAR2 DEFAULT NULL,
103 just_unusable IN BOOLEAN DEFAULT TRUE,
104 locality IN VARCHAR2 DEFAULT 'ALL',
105 concurrent IN BOOLEAN DEFAULT TRUE,
106 cont_after_err IN BOOLEAN DEFAULT FALSE,
107 max_slaves IN INT DEFAULT NULL,
108 forced_degree IN INT DEFAULT NULL,
109 retry_online IN BOOLEAN DEFAULT FALSE,
110 num_errors OUT NOCOPY PLS_INTEGER);
111
112 -- Convenience interface: num_errors is captured and removed internally
113 PROCEDURE build_table_indexes (
114 list IN VARCHAR2 DEFAULT NULL,
115 just_unusable IN BOOLEAN DEFAULT TRUE,
116 locality IN VARCHAR2 DEFAULT 'ALL',
117 concurrent IN BOOLEAN DEFAULT TRUE,
118 cont_after_err IN BOOLEAN DEFAULT FALSE,
119 max_slaves IN INT DEFAULT NULL,
120 forced_degree IN INT DEFAULT NULL,
121 retry_online IN BOOLEAN DEFAULT FALSE);
122
123 -- =========================================================================
124 -- PROCEDURE: BUILD_INDEXES
125 -- INPUT: list -> this list of indexes will be rebuilt. The list should
126 -- take the form:
127 -- <owner>.<index> (, <owner>.<index>)*
128 -- If <owner> is left off, the index is assumed to be in
129 -- the schema of the current user.
130 -- If the index list is NULL, then all indexes
131 -- visible to the current user will be rebuilt.
132 -- just_unusable -> rebuild only the unusable index components? If true,
133 -- then only unusable components will be rebuilt. If
134 -- false, all components will be rebuilt.
135 -- locality -> locality of indexes to be rebuilt. If 'GLOBAL',
136 -- rebuild only global indexes. If 'LOCAL', rebuild
137 -- only local indexes. If 'ALL', rebuild both global
138 -- and local. Otherwise, error.
139 -- concurrent -> if true, use jobs to run rebuilds concurrently,
140 -- if not, individual jobs may still run in parallel
141 -- cont_after_err -> continue after errors? If true, then indexes will
142 -- be rebuilt even after errors. If false, after the
143 -- first error, processing will be halted.
144 -- max_slaves -> degree of parallelism. Maximum number of parallel
145 -- slaves to allocate during the build (across all
146 -- jobs). NULL (default) means the maximum available
147 -- in the system.
148 -- forced_degree -> degree of parallelism forced at session level
149 -- for ddl operations.
150 -- retry_online -> if true, retry online rebuild index when offline
151 -- rebuild fails with ORA-14327,
152 -- OUTPUT: num_errors -> number of failed index rebuilds (details on which
153 -- indexes failed are contained in the alert.log
154 -- USE: Implemented to provide an interface for finding and concurrently
155 -- and parallelely rebuilding all the index components (including
156 -- entire indexes, index partitions, and index subpartitions) for a
157 -- given list of indexes.
158 -- =========================================================================
159 PROCEDURE build_indexes (
160 list IN VARCHAR2 DEFAULT NULL,
161 just_unusable IN BOOLEAN DEFAULT TRUE,
162 locality IN VARCHAR2 DEFAULT 'ALL',
163 concurrent IN BOOLEAN DEFAULT TRUE,
164 cont_after_err IN BOOLEAN DEFAULT FALSE,
165 max_slaves IN INT DEFAULT NULL,
166 forced_degree IN INT DEFAULT NULL,
167 retry_online IN BOOLEAN DEFAULT FALSE,
168 num_errors OUT NOCOPY PLS_INTEGER);
169
170 -- Convenience interface: num_errors is captured and removed internally
171 PROCEDURE build_indexes (
172 list IN VARCHAR2 DEFAULT NULL,
173 just_unusable IN BOOLEAN DEFAULT TRUE,
174 locality IN VARCHAR2 DEFAULT 'ALL',
175 concurrent IN BOOLEAN DEFAULT TRUE,
176 cont_after_err IN BOOLEAN DEFAULT FALSE,
177 max_slaves IN INT DEFAULT NULL,
178 forced_degree IN INT DEFAULT NULL,
179 retry_online IN BOOLEAN DEFAULT FALSE);
180
181 -- =========================================================================
182 -- PROCEDURE: BUILD_INDEX_COMPONENTS
183 -- INPUT: list -> this list of index components will be rebuilt.
184 -- The list should take the form:
185 -- <owner>.<index>.<comp> (, <owner>.<index>.<comp>)*
186 -- If <owner> is left off, the index is assumed to be in
187 -- the schema of the current user. <index> cannot be
188 -- left off.
189 -- For subpartitioned indexes, if the component given is
190 -- a composite partition, all the subpartitions for that
191 -- partition will be candidates for rebuild
192 -- If the index component list is NULL, then all indexes
193 -- visible to the current user will be rebuilt.
194 -- just_unusable -> rebuild only the unusable index components? If true,
195 -- then only unusable components will be rebuilt. If
196 -- false, all components will be rebuilt.
197 -- locality -> locality of indexes to be rebuilt. If 'GLOBAL',
198 -- rebuild only global indexes. If 'LOCAL', rebuild
199 -- only local indexes. If 'ALL', rebuild both global
200 -- and local. Otherwise, error.
201 -- concurrent -> if true, use jobs to run rebuilds concurrently,
202 -- if not, individual jobs may still run in parallel
203 -- cont_after_err -> continue after errors? If true, then indexes will
204 -- be rebuilt even after errors. If false, after the
205 -- first error, processing will be halted.
206 -- max_slaves -> degree of parallelism. Maximum number of parallel
207 -- slaves to allocate during the build (across all
208 -- jobs). NULL (default) means the maximum available
209 -- in the system.
210 -- forced_degree -> degree of parallelism forced at session level
211 -- for ddl operations.
212 -- retry_online -> if true, retry online rebuild index when offline
213 -- rebuild fails with ORA-14327,
214 -- OUTPUT: num_errors -> number of failed index rebuilds (details on which
215 -- indexes failed are contained in the alert.log
216 -- USE: Implemented to provide an interface for finding and concurrently
217 -- and parallelely rebuilding a given list of index components
218 -- (partitions and subparts). This will not rebuild entire indexes.
219 -- =========================================================================
220 PROCEDURE build_index_components (
221 list IN VARCHAR2 DEFAULT NULL,
222 just_unusable IN BOOLEAN DEFAULT TRUE,
223 locality IN VARCHAR2 DEFAULT 'ALL',
224 concurrent IN BOOLEAN DEFAULT TRUE,
225 cont_after_err IN BOOLEAN DEFAULT FALSE,
226 max_slaves IN INT DEFAULT NULL,
227 forced_degree IN INT DEFAULT NULL,
228 retry_online IN BOOLEAN DEFAULT FALSE,
229 num_errors OUT NOCOPY PLS_INTEGER);
230
231 -- Convenience interface: num_errors is captured and removed internally
232 PROCEDURE build_index_components (
233 list IN VARCHAR2 DEFAULT NULL,
234 just_unusable IN BOOLEAN DEFAULT TRUE,
235 locality IN VARCHAR2 DEFAULT 'ALL',
236 concurrent IN BOOLEAN DEFAULT TRUE,
237 cont_after_err IN BOOLEAN DEFAULT FALSE,
238 max_slaves IN INT DEFAULT NULL,
239 forced_degree IN INT DEFAULT NULL,
240 retry_online IN BOOLEAN DEFAULT FALSE);
241
242
243 -- =========================================================================
244 -- PROCEDURE: BUILD_TABLE_COMPONENT_INDEXES
245 -- INPUT: list -> indexes will be rebuilt for this list of table
246 -- components. The list should take the form:
247 -- <owner>.<table>.<comp> (, <owner>.<table>.<comp>)*
248 -- If <owner> is left off, the table is assumed to be in
249 -- the schema of the current user. <table> cannot be
250 -- left off.
251 -- For subpartitioned tables, if the component given is
252 -- a composite partition, all the subpartitions for that
253 -- partition will be candidates for rebuild
254 -- If the table component list is NULL, then all indexes
255 -- visible to the current user will be rebuilt.
256 -- just_unusable -> rebuild only the unusable index components? If true,
257 -- then only unusable components will be rebuilt. If
258 -- false, all components will be rebuilt.
259 -- locality -> locality of indexes to be rebuilt. If 'GLOBAL',
260 -- rebuild only global indexes. If 'LOCAL', rebuild
261 -- only local indexes. If 'ALL', rebuild both global
262 -- and local. Otherwise, error.
263 -- concurrent -> if true, use jobs to run rebuilds concurrently,
264 -- if not, individual jobs may still run in parallel
268 -- max_slaves -> degree of parallelism. Maximum number of parallel
265 -- cont_after_err -> continue after errors? If true, then indexes will
266 -- be rebuilt even after errors. If false, after the
267 -- first error, processing will be halted.
269 -- slaves to allocate during the build (across all
270 -- jobs). NULL (default) means the maximum available
271 -- in the system.
272 -- forced_degree -> degree of parallelism forced at session level
273 -- for ddl operations.
274 -- retry_online -> if true, retry online rebuild index when offline
275 -- rebuild fails with ORA-14327,
276 -- OUTPUT: num_errors -> number of failed index rebuilds (details on which
277 -- indexes failed are contained in the alert.log
278 -- USE: Implemented to provide an interface for finding and concurrently
279 -- and parallelely rebuilding all the index components (including
280 -- index partitions, and index subpartitions) for a given list of
281 -- table components.
282 -- =========================================================================
283 PROCEDURE build_table_component_indexes (
284 list IN VARCHAR2 DEFAULT NULL,
285 just_unusable IN BOOLEAN DEFAULT TRUE,
286 locality IN VARCHAR2 DEFAULT 'ALL',
287 concurrent IN BOOLEAN DEFAULT TRUE,
288 cont_after_err IN BOOLEAN DEFAULT FALSE,
289 max_slaves IN INT DEFAULT NULL,
290 forced_degree IN INT DEFAULT NULL,
291 retry_online IN BOOLEAN DEFAULT FALSE,
292 num_errors OUT NOCOPY PLS_INTEGER);
293
294 -- Convenience interface: num_errors is captured and removed internally
295 PROCEDURE build_table_component_indexes (
296 list IN VARCHAR2 DEFAULT NULL,
297 just_unusable IN BOOLEAN DEFAULT TRUE,
298 locality IN VARCHAR2 DEFAULT 'ALL',
299 concurrent IN BOOLEAN DEFAULT TRUE,
300 cont_after_err IN BOOLEAN DEFAULT FALSE,
301 max_slaves IN INT DEFAULT NULL,
302 forced_degree IN INT DEFAULT NULL,
303 retry_online IN BOOLEAN DEFAULT FALSE);
304
305 -- =========================================================================
306 -- PROCEDURE: MULTI_LEVEL_BUILD
307 -- INPUT:
308 -- schema_list -> indexes will be rebuilt for this list of schema names.
309 -- The list should take the form:
310 -- <schema> (, <schema>)*
311 -- table_list -> indexes will be rebuilt for this list of tables. The
312 -- list should take the form:
313 -- <owner>.<table> (, <owner>.<table>)*
314 -- If <owner> is left off, the table is assumed to be in
315 -- the schema of the current user.
316 -- index_list -> this list of indexes will be rebuilt. The list should
317 -- take the form:
318 -- <owner>.<index> (, <owner>.<index>)*
319 -- If <owner> is left off, the index is assumed to be in
320 -- the schema of the current user.
321 -- idx_comp_list -> this list of index components will be rebuilt.
322 -- The list should take the form:
323 -- <owner>.<index>.<comp> (, <owner>.<index>.<comp>)*
324 -- If <owner> is left off, the index is assumed to be in
325 -- the schema of the current user. <index> cannot be
326 -- left off.
327 -- tab_comp_list -> indexes will be rebuild for this list of table
328 -- components. The list should take the form:
329 -- <owner>.<table>.<comp> (, <owner>.<table>.<comp>)*
330 -- If <owner> is left off, the table is assumed to be in
331 -- the schema of the current user. <table> cannot be
332 -- left off.
333 -- NOTE: if all lists are NULL, then all indexes visible to the current
334 -- user will be rebuilt.
335 -- just_unusable -> rebuild only the unusable index components? If true,
336 -- then only unusable components will be rebuilt. If
337 -- false, all components will be rebuilt.
338 -- locality -> locality of indexes to be rebuilt. If 'GLOBAL',
339 -- rebuild only global indexes. If 'LOCAL', rebuild
340 -- only local indexes. If 'ALL', rebuild both global
341 -- and local. Otherwise, error.
342 -- concurrent -> if true, use jobs to run rebuilds concurrently,
343 -- if not, individual jobs may still run in parallel
344 -- cont_after_err -> continue after errors? If true, then indexes will
345 -- be rebuilt even after errors. If false, after the
346 -- first error, processing will be halted.
347 -- max_slaves -> degree of parallelism. Maximum number of parallel
348 -- slaves to allocate during the build (across all
349 -- jobs). NULL (default) means the maximum available
350 -- in the system.
351 -- forced_degree -> degree of parallelism forced at session level
352 -- for ddl operations.
353 -- retry_online -> if true, retry online rebuild index when offline
354 -- rebuild fails with ORA-14327,
355 -- OUTPUT: num_errors -> number of failed index rebuilds (details on which
356 -- indexes failed are contained in the alert.log
357 -- USE: Implemented to provide an interface for finding and concurrently
358 -- and parallelely rebuilding all the index components (including
359 -- index partitions, and index subpartitions) for several lists of
360 -- schema objects. This function has an advantage over others in
361 -- that it provides the maximum amount of concurrency possible by
362 -- removing serialization forced by making sequential calls to
363 -- different rebuild functions.
364 -- =========================================================================
365 PROCEDURE multi_level_build (
366 schema_list IN VARCHAR2 DEFAULT NULL,
367 table_list IN VARCHAR2 DEFAULT NULL,
368 index_list IN VARCHAR2 DEFAULT NULL,
369 idx_comp_list IN VARCHAR2 DEFAULT NULL,
370 tab_comp_list IN VARCHAR2 DEFAULT NULL,
371 just_unusable IN BOOLEAN DEFAULT TRUE,
372 locality IN VARCHAR2 DEFAULT 'ALL',
373 concurrent IN BOOLEAN DEFAULT TRUE,
374 cont_after_err IN BOOLEAN DEFAULT FALSE,
375 max_slaves IN INT DEFAULT NULL,
376 forced_degree IN INT DEFAULT NULL,
377 retry_online IN BOOLEAN DEFAULT FALSE,
378 num_errors OUT NOCOPY PLS_INTEGER);
379
380 -- Convenience interface: num_errors is captured and removed internally
381 PROCEDURE multi_level_build (
382 schema_list IN VARCHAR2 DEFAULT NULL,
383 table_list IN VARCHAR2 DEFAULT NULL,
384 index_list IN VARCHAR2 DEFAULT NULL,
385 idx_comp_list IN VARCHAR2 DEFAULT NULL,
386 tab_comp_list IN VARCHAR2 DEFAULT NULL,
387 just_unusable IN BOOLEAN DEFAULT TRUE,
388 locality IN VARCHAR2 DEFAULT 'ALL',
389 concurrent IN BOOLEAN DEFAULT TRUE,
390 cont_after_err IN BOOLEAN DEFAULT FALSE,
391 max_slaves IN INT DEFAULT NULL,
392 forced_degree IN INT DEFAULT NULL,
393 retry_online IN BOOLEAN DEFAULT FALSE);
394
395 END dbms_index_utl;
[Home] [Help]