[Home] [Help]
PACKAGE BODY: APPS.WF_INSTALL
Source
1 package body Wf_Install as
2 /* $Header: wfpartb.pls 120.5.12010000.3 2009/03/02 14:45:07 alsosa ship $ */
3 Procedure CreateTable (
4 partition in boolean,
5 utl_dir in varchar2,
6 tblname in varchar2,
7 tblspcname in varchar2 default null,
8 modified out nocopy boolean
9 )
10 is
11 v_tablespace_name varchar2(30);
12 v_initial number;
13 v_next number;
14 v_pctinc number;
15 v_partitioned varchar2(3);
16 v_new_table varchar2(30);
17 v_old_table varchar2(30);
18 v_sql varchar2(5000);
19 v_dummy varchar2(1);
20 v_freelist_groups varchar2(30);
21 v_initrans varchar2(30);
22 v_pctfree varchar2(30);
23 v_pctused varchar2(30);
24 v_degree varchar2(30);
25 i_file_handle utl_file.file_type;
26
27 begin
28 begin
29 if (partition) then
30 i_file_handle := utl_file.fopen(utl_dir,'wfpart.sql','a',32767);
31 SELECT TABLESPACE_NAME, INITIAL_EXTENT, NEXT_EXTENT,
32 PCT_INCREASE,PARTITIONED
33 INTO v_tablespace_name, v_initial, v_next,
34 v_pctinc, v_partitioned
35 FROM USER_TABLES
36 WHERE TABLE_NAME = tblname;
37
38 -- initial extent could be much larger than next extent
39 -- if it is greater than 10M, we made next extent at least 10M
40 -- otherwise, we choose next extent to be at least 1M.
41 if (v_initial > 10485760) then
42 v_next := greatest(v_next,10485760);
43 else
44 v_next := greatest(v_next,1048576);
45 end if;
46 else
47 i_file_handle := utl_file.fopen(utl_dir,'wfunpart.sql','a',32767);
48 SELECT NVL(UT.TABLESPACE_NAME,UPT.DEF_TABLESPACE_NAME),
49 TO_CHAR(GREATEST(NVL(UT.INITIAL_EXTENT,
50 TO_NUMBER(UPT.DEF_INITIAL_EXTENT)),65536)),
51 TO_CHAR(GREATEST(NVL(UT.NEXT_EXTENT,
52 TO_NUMBER(UPT.DEF_NEXT_EXTENT)),65536)),
53 NVL(to_char(UT.PCT_INCREASE),UPT.DEF_PCT_INCREASE),
54 UT.PARTITIONED
55 INTO v_tablespace_name, v_initial, v_next, v_pctinc, v_partitioned
56 FROM USER_PART_TABLES UPT,
57 USER_TABLES UT
58 WHERE UT.TABLE_NAME = tblname
59 AND UPT.TABLE_NAME(+) = UT.TABLE_NAME;
60
61 -- In a partitioned table, next extent is much bigger, so we use that
62 -- to calculate initial extent. We estimated unpartitioned initial
63 -- extent is 10 times of that partitioned next extent, but no less
64 -- than 1M.
65 -- the select above does not seem to get the next extent we defined
66 -- during partitioning of the table so we hard code the value later.
67 -- v_initial := greatest(v_next * 10, 1048576);
68 v_next := 1048576; -- set next extent to 1M
69
70 end if;
71 exception
72 when NO_DATA_FOUND then
73 utl_file.put_line(i_file_handle,'--Error while querying storage parameters.');
74 utl_file.put_line(i_file_handle,'-- Table '||tblname||
75 ' does not exist.');
76 utl_file.put_line(i_file_handle,'-- Please check if you login to the correct user, database,');
77 utl_file.put_line(i_file_handle,'-- and that application installation has been completed successfully.');
78 utl_file.fflush(i_file_handle);
79 utl_file.fclose(i_file_handle);
80 raise_application_error(-20001,'Missing table '||tblname);
81 when OTHERS then
82 utl_file.put_line(i_file_handle,'--Error while querying storage parameters.');
83 utl_file.fclose(i_file_handle);
84 raise;
85 end;
86
87 if (partition) then
88 if (v_partitioned = 'YES') then
89 -- already partitioned. Just exit.
90 utl_file.put_line(i_file_handle,'--Table '||tblname||
91 ' already partitioned .');
92 utl_file.fclose(i_file_handle);
93 modified := FALSE;
94 return;
95 end if;
96
97 if (tblspcname is not null) then
98 v_tablespace_name := tblspcname;
99 end if;
100
101 v_new_table := substr('WFN_'||tblname, 1, 30);
102
103 -- check if new table name exists, if so, may ask to drop it.
104 begin
105 SELECT null
106 INTO v_dummy from sys.dual
107 WHERE exists (
108 SELECT 1
109 FROM USER_OBJECTS
110 WHERE OBJECT_NAME = v_new_table
111 );
112 utl_file.put_line(i_file_handle,'--Name conflict.Please first drop '||
113 v_new_table);
114 utl_file.fclose(i_file_handle);
115 raise_application_error(-20002, v_new_table||' already exists.');
116 exception
117 when NO_DATA_FOUND then
118 null;
119 when OTHERS then
120 utl_file.put_line(i_file_handle,'--Error while checking the new table name.');
121 utl_file.fclose(i_file_handle);
122 raise;
123 end;
124
125 --v_initial := 40K; -- force it to 40k
126 --v_next := 1M; --force to 1MB
127
128
129 utl_file.put_line(i_file_handle,'--Creating new table '||v_new_table);
130 v_sql :=' create table %s'||'\n'||
131 ' pctfree 10'||'\n'||
132 ' pctused 80'||'\n'||
133 ' initrans 10'||'\n'||
134 ' tablespace %s'||'\n'||
135 ' storage (initial 40K next %s'||'\n'||
136 ' freelists 32 freelist groups 4'||'\n'||
137 ' pctincrease %s )' ||'\n'||
138 ' parallel '||'\n'||
139 ' logging'||'\n'||
140 ' partition by range (item_type) '||'\n'||
141 ' subpartition by hash (item_key) '||'\n'||
142 ' subpartitions 8 ('||'\n'||
143 ' partition wf_item1 values less than ('||''''||'A1'||''''||' ) ,' ||'\n'||
144 ' partition wf_item2 values less than ('||''''||'AM'||''''||' ) ,' ||'\n'||
145 ' partition wf_item3 values less than ('||''''||'AP'||''''||' ) ,' ||'\n'||
146 ' partition wf_item4 values less than ('||''''||'AR'||''''||' ) ,' ||'\n'||
147 ' partition wf_item5 values less than ('||''''||'AZ'||''''||' ) ,' ||'\n'||
148 ' partition wf_item6 values less than ('||''''||'BC'||''''||' ) ,' ||'\n'||
149 ' partition wf_item7 values less than ('||''''||'BD'||''''||' ) ,' ||'\n'||
150 ' partition wf_item8 values less than ('||''''||'BI'||''''||' ) ,' ||'\n'||
151 ' partition wf_item9 values less than ('||''''||'BO'||''''||' ) ,' ||'\n'||
152 ' partition wf_item10 values less than ('||''''||'BT'||''''||' ) ,' ||'\n'||
153 ' partition wf_item11 values less than ('||''''||'BW'||''''||' ) ,' ||'\n'||
154 ' partition wf_item12 values less than ('||''''||'CA'||''''||' ) ,' ||'\n'||
155 ' partition wf_item13 values less than ('||''''||'CH'||''''||' ) ,' ||'\n'||
156 ' partition wf_item14 values less than ('||''''||'CI'||''''||' ) ,' ||'\n'||
157 ' partition wf_item15 values less than ('||''''||'CO'||''''||' ) ,' ||'\n'||
158 ' partition wf_item16 values less than ('||''''||'CR'||''''||' ) ,' ||'\n'||
159 ' partition wf_item17 values less than ('||''''||'CS'||''''||' ) ,' ||'\n'||
160 ' partition wf_item18 values less than ('||''''||'CT'||''''||' ) ,' ||'\n'||
161 ' partition wf_item19 values less than ('||''''||'CU'||''''||' ) ,' ||'\n'||
162 ' partition wf_item20 values less than ('||''''||'DE'||''''||' ) ,' ||'\n'||
163 ' partition wf_item21 values less than ('||''''||'EC'||''''||' ) ,' ||'\n'||
164 ' partition wf_item22 values less than ('||''''||'ER'||''''||' ) ,' ||'\n'||
165 ' partition wf_item23 values less than ('||''''||'FA'||''''||' ) ,' ||'\n'||
166 ' partition wf_item24 values less than ('||''''||'FI'||''''||' ) ,' ||'\n'||
167 ' partition wf_item25 values less than ('||''''||'FN'||''''||' ) ,' ||'\n'||
168 ' partition wf_item26 values less than ('||''''||'GE'||''''||' ) ,' ||'\n'||
169 ' partition wf_item27 values less than ('||''''||'GH'||''''||' ) ,' ||'\n'||
170 ' partition wf_item28 values less than ('||''''||'GL'||''''||' ) ,' ||'\n'||
171 ' partition wf_item29 values less than ('||''''||'GM'||''''||' ) ,' ||'\n'||
172 ' partition wf_item30 values less than ('||''''||'GN'||''''||' ) ,' ||'\n'||
173 ' partition wf_item31 values less than ('||''''||'HR'||''''||' ) ,' ||'\n'||
174 ' partition wf_item32 values less than ('||''''||'IC'||''''||' ) ,' ||'\n'||
175 ' partition wf_item33 values less than ('||''''||'IN'||''''||' ) ,' ||'\n'||
176 ' partition wf_item34 values less than ('||''''||'IO'||''''||' ) ,' ||'\n'||
177 ' partition wf_item35 values less than ('||''''||'IW'||''''||' ) ,' ||'\n'||
178 ' partition wf_item36 values less than ('||''''||'JT'||''''||' ) ,' ||'\n'||
179 ' partition wf_item37 values less than ('||''''||'JU'||''''||' ) ,' ||'\n'||
180 ' partition wf_item38 values less than ('||''''||'KH'||''''||' ) ,' ||'\n'||
181 ' partition wf_item39 values less than ('||''''||'KO'||''''||' ) ,' ||'\n'||
182 ' partition wf_item40 values less than ('||''''||'LS'||''''||' ) ,' ||'\n'||
183 ' partition wf_item41 values less than ('||''''||'MD'||''''||' ) ,' ||'\n'||
184 ' partition wf_item42 values less than ('||''''||'MR'||''''||' ) ,' ||'\n'||
185 ' partition wf_item43 values less than ('||''''||'MS'||''''||' ) ,' ||'\n'||
186 ' partition wf_item44 values less than ('||''''||'NE'||''''||' ) ,' ||'\n'||
187 ' partition wf_item45 values less than ('||''''||'NT'||''''||' ) ,' ||'\n'||
188 ' partition wf_item46 values less than ('||''''||'OA'||''''||' ) ,' ||'\n'||
189 ' partition wf_item47 values less than ('||''''||'OB'||''''||' ) ,' ||'\n'||
190 ' partition wf_item48 values less than ('||''''||'OE'||''''||' ) ,' ||'\n'||
191 ' partition wf_item49 values less than ('||''''||'OF'||''''||' ) ,' ||'\n'||
192 ' partition wf_item50 values less than ('||''''||'OK'||''''||' ) ,' ||'\n'||
193 ' partition wf_item51 values less than ('||''''||'OL'||''''||' ) ,' ||'\n'||
194 ' partition wf_item52 values less than ('||''''||'OR'||''''||' ) ,' ||'\n'||
195 ' partition wf_item53 values less than ('||''''||'PA'||''''||' ) ,' ||'\n'||
196 ' partition wf_item54 values less than ('||''''||'PJ'||''''||' ) ,' ||'\n'||
197 ' partition wf_item55 values less than ('||''''||'PM'||''''||' ) ,' ||'\n'||
198 ' partition wf_item56 values less than ('||''''||'PO'||''''||' ) ,' ||'\n'||
199 ' partition wf_item57 values less than ('||''''||'PQ'||''''||' ) ,' ||'\n'||
200 ' partition wf_item58 values less than ('||''''||'PR'||''''||' ) ,' ||'\n'||
201 ' partition wf_item59 values less than ('||''''||'QA'||''''||' ) ,' ||'\n'||
202 ' partition wf_item60 values less than ('||''''||'RB'||''''||' ) ,' ||'\n'||
203 ' partition wf_item61 values less than ('||''''||'RE'||''''||' ) ,' ||'\n'||
204 ' partition wf_item62 values less than ('||''''||'RM'||''''||' ) ,' ||'\n'||
205 ' partition wf_item63 values less than ('||''''||'RO'||''''||' ) ,' ||'\n'||
206 ' partition wf_item64 values less than ('||''''||'SA'||''''||' ) ,' ||'\n'||
207 ' partition wf_item65 values less than ('||''''||'SE'||''''||' ) ,' ||'\n'||
208 ' partition wf_item66 values less than ('||''''||'SH'||''''||' ) ,' ||'\n'||
209 ' partition wf_item67 values less than ('||''''||'SI'||''''||' ) ,' ||'\n'||
210 ' partition wf_item68 values less than ('||''''||'SR'||''''||' ) ,' ||'\n'||
211 ' partition wf_item69 values less than ('||''''||'SU'||''''||' ) ,' ||'\n'||
212 ' partition wf_item70 values less than ('||''''||'SY'||''''||' ) ,' ||'\n'||
213 ' partition wf_item71 values less than ('||''''||'TE'||''''||' ) ,' ||'\n'||
214 ' partition wf_item72 values less than ('||''''||'TS'||''''||' ) ,' ||'\n'||
215 ' partition wf_item73 values less than ('||''''||'WF'||''''||' ) ,' ||'\n'||
216 ' partition wf_item74 values less than ('||''''||'WG'||''''||' ) ,' ||'\n'||
217 ' partition wf_item75 values less than ('||''''||'WI'||''''||' ) ,' ||'\n'||
218 ' partition wf_item76 values less than ('||''''||'WS'||''''||' ) ,' ||'\n'||
219 ' partition wf_item77 values less than (MAXVALUE))' ||'\n'||
220 ' as select * from %s ; \n';
221
222
223 begin
224 utl_file.putf(i_file_handle,v_sql,v_new_table,v_tablespace_name,
225 to_char(v_next),to_char(v_pctinc),tblname);
226 utl_file.fflush(i_file_handle);
227 exception
228 when utl_file.WRITE_ERROR then
229 utl_file.put_line(i_file_handle,'Error writing the dynamic sql into log file');
230 utl_file.fclose(i_file_handle);
231 raise;
232 when utl_file.INTERNAL_ERROR then
233 utl_file.put_line(i_file_handle,'Error writing the dynamic sql into log file');
234 utl_file.fclose(i_file_handle);
235 raise;
236 when OTHERS then
237 utl_file.put_line(i_file_handle,'Error in SQL: '||substr(v_sql,1,220));
238 utl_file.fclose(i_file_handle);
239 raise;
240 end;
241
242 utl_file.put_line(i_file_handle,'--Changing new table '||
243 v_new_table||' to noparalle,logging');
244 v_sql :='alter table %s'||'\n'||
245 'noparallel '||'\n'||
246 'logging;'||'\n';
247 begin
248 utl_file.putf(i_file_handle,v_sql,v_new_table);
249 utl_file.fflush(i_file_handle);
250 exception
251 when OTHERS then
252 utl_file.put_line(i_file_handle,'Error in SQL: '||substr(v_sql,1,220));
253 raise;
254 end;
255 -- rename orig table to old table
256 v_old_table := substr('WFO_'||tblname, 1, 30);
257 v_sql := 'alter table '||tblname||' rename to '||v_old_table||';';
258 begin
259 utl_file.put_line(i_file_handle,'--Execute the following statement :');
260 utl_file.put_line(i_file_handle,v_sql);
261 utl_file.putf(i_file_handle,' '||'\n');
262 exception
263 when OTHERS then
264 utl_file.put_line(i_file_handle,'--Error in: '||substr(v_sql,1,220));
265 utl_file.fclose(i_file_handle);
266 raise;
267 end;
268
269 -- rename new table to orig table
270 v_sql := 'alter table '||v_new_table||' rename to '||tblname||';';
271 begin
272 utl_file.put_line(i_file_handle,'--Execute the following statement :');
273 utl_file.put_line(i_file_handle,v_sql);
274 utl_file.putf(i_file_handle,' '||'\n');
275 exception
276 when OTHERS then
277 utl_file.put_line(i_file_handle,'--Error in: '||substr(v_sql,1,220));
278 utl_file.fclose(i_file_handle);
279 raise_application_error(-20005,'Error in SQL: '||substr(v_sql,1,3000));
280 end;
281 else
282 --If the table is already unpartitioned
283 if (v_partitioned = 'NO') then
284 -- already unpartitioned. Just exit.
285 utl_file.put_line(i_file_handle,'--Table '||tblname||
286 ' not partitioned .');
290 end if;
287 utl_file.fclose(i_file_handle);
288 modified := FALSE;
289 return;
291
292 begin
293 SELECT to_char(least(to_number(VALUE),8)),
294 to_char(least(to_number(VALUE),4))
295 INTO v_freelist_groups, v_initrans
296 FROM V$PARAMETER
297 WHERE NAME = 'cpu_count';
298 exception
299 when NO_DATA_FOUND then
300 utl_file.put_line(i_file_handle,'--Error while querying number of CPUs.');
301 utl_file.put_line(i_file_handle,'-- View V$PARAMETER does not exist.');
302 utl_file.put_line(i_file_handle,'-- Please check if you login to the correct user, database,');
303 utl_file.put_line(i_file_handle,'-- and that application installation has been completed successfully.');
304 raise_application_error(-20001,'Missing view V$PARAMETER');
305 when OTHERS then
306 utl_file.put_line(i_file_handle,'--Error while querying number of CPUs.');
307 raise;
308 end;
309 begin
310 SELECT to_char(min(to_number(VALUE)))
311 INTO v_degree
312 FROM V$PARAMETER
313 WHERE NAME IN ('parallel_max_servers','cpu_count');
314 exception
315 when NO_DATA_FOUND then
316 utl_file.put_line(i_file_handle,'--Error while querying number parallel degree.');
317 utl_file.put_line(i_file_handle,'-- View V$PARAMETER does not exist.');
318 utl_file.put_line(i_file_handle,'-- Please check if you login to the correct user, database,');
319 utl_file.put_line(i_file_handle,'-- and that application installation has been completed successfully.');
320 raise_application_error(-20001,'Missing view V$PARAMETER');
321 when OTHERS then
322 utl_file.put_line(i_file_handle,'--Error while querying number of CPUs.');
323 raise;
324 end;
325 if (tblspcname is not null) then
326 v_tablespace_name := tblspcname;
327 end if;
328
329
330 v_new_table := 'NN'||substr(tblname, 3, 30);
331
332 -- check if new table name exists, if so, may ask to drop it.
333 begin
334 SELECT null
335 INTO v_dummy
336 FROM sys.dual
337 WHERE exists (SELECT 1
338 FROM USER_OBJECTS
339 WHERE OBJECT_NAME = v_new_table
340 );
341 utl_file.put_line(i_file_handle,'Name conflict. Please first drop '||
342 v_new_table);
343 raise_application_error(-20002, v_new_table||' already exists.');
344 exception
345 when NO_DATA_FOUND then
346 null;
347 when OTHERS then
348 utl_file.put_line(i_file_handle,'--Error while checking the new table name.');
349 raise;
350 end;
351
352 --v_initial := 1M; -- Force it to smaller 1MB
353 v_pctinc := 0;
354 v_pctused := '40';
355
356 if (tblname = 'WF_ITEM_ACTIVITY_STATUSES') then
357 v_initial := 104857600; -- 100M
358 v_pctfree := '40';
359 elsif (tblname = 'WF_ITEM_ACTIVITY_STATUSES_H') then
360 v_initial := 104857600; -- 100M
361 v_pctfree := '0';
362 elsif (tblname = 'WF_ITEM_ATTRIBUTE_VALUES') then
363 v_initial := 104857600; -- 100M
364 v_pctfree := '30';
365 elsif (tblname = 'WF_ITEMS') then
366 v_initial := 10485760; -- 10M
367 v_pctfree := '30';
368 end if;
369
370 -- Creating new table
371 begin
372 utl_file.put_line(i_file_handle,'--Creating new table '||v_new_table);
373 v_sql := ' create table %s'||'\n'||
374 ' pctfree %s'||'\n'||
375 ' pctused %s'||'\n'||
376 ' initrans %s'||'\n'||
377 ' tablespace %s';
378
379 utl_file.putf(i_file_handle,v_sql,v_new_table,v_pctfree,
380 v_pctused,v_initrans,v_tablespace_name);
381 utl_file.putf(i_file_handle,'\n');
382 utl_file.fflush(i_file_handle);
383 v_sql := ' storage (initial %s next %s '||'\n'||
384 ' freelists 2 freelist groups %s'||'\n'||
385 ' pctincrease 0 )' ||'\n'||
386 ' parallel %s'||'\n'||
387 ' logging'||
388 ' as select * from %s ;'||'\n';
389
390 utl_file.putf(i_file_handle,v_sql,
391 to_char(v_initial),to_char(v_next),v_freelist_groups,
392 v_degree,tblname);
393 utl_file.fflush(i_file_handle);
394 exception
395 when utl_file.WRITE_ERROR then
396 utl_file.put_line(i_file_handle,'--Error writing the dynamic sql into log file');
397 utl_file.fclose(i_file_handle);
398 raise;
399 when utl_file.INTERNAL_ERROR then
400 utl_file.put_line(i_file_handle,'Error writing the dynamic sql into log file');
401 utl_file.fclose(i_file_handle);
402 raise;
403 when OTHERS then
404 utl_file.put_line(i_file_handle,'Error in SQL: '||substr(v_sql,1,220));
405 utl_file.fclose(i_file_handle);
406 raise;
407 end;
408
409 -- changing new table
410 utl_file.put_line(i_file_handle,'--Changing new table '||
411 v_new_table||' to logging');
412 v_sql :='alter table %s'||'\n'||
413 'noparallel '||'\n'||
414 'logging;'||'\n';
418 exception
415 begin
416 utl_file.putf(i_file_handle,v_sql,v_new_table);
417 utl_file.fflush(i_file_handle);
419 when OTHERS then
420 utl_file.put_line(i_file_handle,'Error in SQL: '||substr(v_sql,1,220));
421 raise;
422 end;
423 -- gathering CBO stats
424 utl_file.put_line(i_file_handle,'--Gathering CBO Stats on new table '||v_new_table);
425 -- begin
426 -- utl_file.put_line(apps.fnd_stats.gather_table_stats(ownname => 'APPLSYS',
427 -- tabname => v_new_table);
428 -- null;
429 -- end;
430
431 -- rename orig table to old table
432 v_old_table := 'OO'||substr(tblname, 3, 28);
433 utl_file.put_line(i_file_handle,'--Rename '||tblname||' to '||v_old_table);
434 v_sql := 'alter table '||tblname||' rename to '||v_old_table||';';
435 begin
436 utl_file.put_line(i_file_handle,v_sql);
437 utl_file.putf(i_file_handle,'\n');
438 utl_file.fflush(i_file_handle);
439 exception
440 when OTHERS then
441 utl_file.put_line(i_file_handle,'Error in: '||substr(v_sql,1,220));
442 raise;
443 end;
444
445 -- rename new table to orig table
446 utl_file.put_line(i_file_handle,'--Rename '||v_new_table||
447 ' to '||tblname);
448 v_sql := 'alter table '||v_new_table||' rename to '||tblname||';';
449 begin
450 utl_file.put_line(i_file_handle,v_sql);
451 utl_file.putf(i_file_handle,'\n');
452 exception
453 when OTHERS then
454 raise_application_error(-20005,'Error in SQL: '||substr(v_sql,1,220));
455 end;
456 end if;
457 utl_file.fclose(i_file_handle);
458 --set the out parameter value to TRUE for indicating
459 --whether or not we sould create index or not.
460 --If the table is already partitioned then we set this parameter
461 --to FALSE to indicate we don't need to recreate indexes on it.
462 modified := TRUE;
463 exception
464 when OTHERS then
465 utl_file.put_line(i_file_handle,'--Error in CreateTable '||tblname);
466 if (utl_file.is_open(i_file_handle)) then
467 utl_file.fclose(i_file_handle);
468 end if;
469 raise;
470 end CreateTable;
471
472 Procedure CreateIndex (
473 partition in boolean ,
474 utl_dir in varchar2,
475 idxname in varchar2,
476 tblspcname in varchar2 default null
477 )
478 is
479 v_tablespace_name varchar2(30);
480 v_initial number;
481 v_next number;
482 v_pctinc number;
483 v_tblpartitioned varchar2(3);
484 v_idxpartitioned varchar2(3);
485 v_prefix varchar2(8);
486 v_new_index varchar2(30);
487 v_old_index varchar2(30);
488 v_sql varchar2(4000);
489 v_dummy varchar2(1);
490 partition_index boolean := false;
491 drop_index boolean := false;
492 v_freelist_groups varchar2(30);
493 v_initrans varchar2(30);
494 v_pctfree varchar2(30);
495 v_degree varchar2(30);
496 i_file_handle utl_file.file_type;
497 begin
498 begin
499 if (partition) then
500 i_file_handle := utl_file.fopen(utl_dir,'wfpart.sql','a',32767);
501 utl_file.putf(i_file_handle,' '||'\n');
502 SELECT I.TABLESPACE_NAME, I.INITIAL_EXTENT, I.NEXT_EXTENT, I.PCT_INCREASE,
503 I.PARTITIONED, T.PARTITIONED
504 INTO v_tablespace_name, v_initial, v_next, v_pctinc,
505 v_idxpartitioned, v_tblpartitioned
506 FROM USER_INDEXES I, USER_TABLES T
507 WHERE I.INDEX_NAME = idxname
508 AND I.TABLE_NAME = T.TABLE_NAME;
509
510 -- initial extent could be much larger than next extent
511 -- if it is greater than 10M, we made next extent at least 10M
512 -- otherwise, we choose next extent to be at least 1M.
513 if (v_initial > 10485760) then
514 v_next := greatest(v_next,10485760);
515 else
516 v_next := greatest(v_next,1048576);
517 end if;
518 else
519 i_file_handle := utl_file.fopen(utl_dir,'wfunpart.sql','a',32767);
520 SELECT NVL(I.TABLESPACE_NAME,UPI.DEF_TABLESPACE_NAME),
521 TO_CHAR(GREATEST(NVL(I.INITIAL_EXTENT,
522 TO_NUMBER(UPI.DEF_INITIAL_EXTENT)),65536)),
523 TO_CHAR(GREATEST(NVL(I.NEXT_EXTENT,
524 TO_NUMBER(UPI.DEF_NEXT_EXTENT)),65536)),
525 NVL(to_char(I.PCT_INCREASE),UPI.DEF_PCT_INCREASE),
526 I.PARTITIONED, T.PARTITIONED
527 INTO v_tablespace_name, v_initial, v_next, v_pctinc,
528 v_idxpartitioned, v_tblpartitioned
529 FROM USER_INDEXES I, USER_TABLES T, USER_PART_INDEXES UPI
530 WHERE I.INDEX_NAME = idxname
531 AND I.TABLE_NAME = T.TABLE_NAME
532 AND UPI.INDEX_NAME(+) = I.INDEX_NAME;
533
534 -- In a partitioned index, next extent is much bigger, so we use that
535 -- to calculate initial extent. We estimated unpartitioned initial
536 -- extent is 10 times of that partitioned next extent, but no less
537 -- than 1M.
538 -- the select above does not seem to get the next extent we defined
539 -- during partitioning of the table so we hard code the value later.
540 -- v_initial := greatest(v_next * 10, 1048576);
541 v_next := 1048576; -- set next extent to 1M
542
543 end if;
544 exception
548 ' does not exist.');
545 when NO_DATA_FOUND then
546 utl_file.put_line(i_file_handle,'--Error while querying storage parameters.');
547 utl_file.put_line(i_file_handle,'-- Index '||idxname||
549 utl_file.put_line(i_file_handle,'-- Please check if you login to the correct user, database,');
550 utl_file.put_line(i_file_handle,'-- and that application installation has been completed successfully.');
551 utl_file.fflush(i_file_handle);
552 utl_file.fclose(i_file_handle);
553 raise_application_error(-20011,'Missing index '||idxname);
554 when OTHERS then
555 utl_file.put_line(i_file_handle,'--Error while querying storage parameters.');
556 utl_file.fclose(i_file_handle);
557 raise;
558 end;
559
560 if (partition) then
561 if (tblspcname is not null) then
562 v_tablespace_name := tblspcname;
563 end if;
564
565 v_new_index := substr('WFN_'||idxname, 1, 30);
566
567 -- check if new index name exists, if so, may ask to drop it.
568 begin
569 SELECT null
570 INTO v_dummy
571 FROM sys.dual
572 WHERE exists (
573 SELECT 1
574 FROM USER_OBJECTS
575 WHERE OBJECT_NAME = v_new_index
576 );
577 utl_file.put_line(i_file_handle,'--Name conflict. Please first drop '||v_new_index);
578 utl_file.fclose(i_file_handle);
579 raise_application_error(-20012, v_new_index||' already exists.');
580 exception
581 when NO_DATA_FOUND then
582 null;
583 when OTHERS then
584 utl_file.put_line(i_file_handle,'--Error while checking the new index name.');
585 utl_file.fclose(i_file_handle);
586 raise;
587 end;
588
589 utl_file.put_line(i_file_handle,'--Recreate index '||idxname);
590 v_prefix := 'WFO_';
591 --v_initial := 40K; -- force it to 40k
592 if (idxname = 'WF_ITEM_ACTIVITY_STATUSES_PK') then
593 partition_index := true;
594 v_prefix := 'WFP_';
595 v_sql := 'create unique index %s'||'\n'||
596 ' on WF_ITEM_ACTIVITY_STATUSES (ITEM_TYPE, ITEM_KEY, PROCESS_ACTIVITY)'||'\n'||
597 ' pctfree 10'||'\n'||
598 ' initrans 10'||'\n'||
599 ' tablespace %s '||'\n'||
600 ' storage (initial 40K next %s '||'\n'||
601 ' freelists 32 freelist groups 4'||'\n'||
602 ' pctincrease %s )' ||'\n'||
603 ' logging'||'\n'||
604 ' local ('||'\n'||
605 ' partition wf_item1,' ||'\n'||
606 ' partition wf_item2,' ||'\n'||
607 ' partition wf_item3,' ||'\n'||
608 ' partition wf_item4,' ||'\n'||
609 ' partition wf_item5,' ||'\n'||
610 ' partition wf_item6,' ||'\n'||
611 ' partition wf_item7,' ||'\n'||
612 ' partition wf_item8,' ||'\n'||
613 ' partition wf_item9,' ||'\n'||
614 ' partition wf_item10,' ||'\n'||
615 ' partition wf_item11,' ||'\n'||
616 ' partition wf_item12,' ||'\n'||
617 ' partition wf_item13,' ||'\n'||
618 ' partition wf_item14,' ||'\n'||
619 ' partition wf_item15,' ||'\n'||
620 ' partition wf_item16,' ||'\n'||
621 ' partition wf_item17,' ||'\n'||
622 ' partition wf_item18,' ||'\n'||
623 ' partition wf_item19,' ||'\n'||
624 ' partition wf_item20,' ||'\n'||
625 ' partition wf_item21,' ||'\n'||
626 ' partition wf_item22,' ||'\n'||
627 ' partition wf_item23,' ||'\n'||
628 ' partition wf_item24,' ||'\n'||
629 ' partition wf_item25,' ||'\n'||
630 ' partition wf_item26,' ||'\n'||
631 ' partition wf_item27,' ||'\n'||
632 ' partition wf_item28,' ||'\n'||
633 ' partition wf_item29,' ||'\n'||
634 ' partition wf_item30,' ||'\n'||
635 ' partition wf_item31,' ||'\n'||
636 ' partition wf_item32,' ||'\n'||
637 ' partition wf_item33,' ||'\n'||
638 ' partition wf_item34,' ||'\n'||
639 ' partition wf_item35,' ||'\n'||
640 ' partition wf_item36,' ||'\n'||
641 ' partition wf_item37,' ||'\n'||
642 ' partition wf_item38,' ||'\n'||
643 ' partition wf_item39,' ||'\n'||
644 ' partition wf_item40,' ||'\n'||
645 ' partition wf_item41,' ||'\n'||
646 ' partition wf_item42,' ||'\n'||
647 ' partition wf_item43,' ||'\n'||
648 ' partition wf_item44,' ||'\n'||
649 ' partition wf_item45,' ||'\n'||
650 ' partition wf_item46,' ||'\n'||
651 ' partition wf_item47,' ||'\n'||
652 ' partition wf_item48,' ||'\n'||
653 ' partition wf_item49,' ||'\n'||
654 ' partition wf_item50,' ||'\n'||
655 ' partition wf_item51,' ||'\n'||
656 ' partition wf_item52,' ||'\n'||
657 ' partition wf_item53,' ||'\n'||
658 ' partition wf_item54,' ||'\n'||
659 ' partition wf_item55,' ||'\n'||
660 ' partition wf_item56,' ||'\n'||
661 ' partition wf_item57,' ||'\n'||
665 ' partition wf_item61,' ||'\n'||
662 ' partition wf_item58,' ||'\n'||
663 ' partition wf_item59,' ||'\n'||
664 ' partition wf_item60,' ||'\n'||
666 ' partition wf_item62,' ||'\n'||
667 ' partition wf_item63,' ||'\n'||
668 ' partition wf_item64,' ||'\n'||
669 ' partition wf_item65,' ||'\n'||
670 ' partition wf_item66,' ||'\n'||
671 ' partition wf_item67,' ||'\n'||
672 ' partition wf_item68,' ||'\n'||
673 ' partition wf_item69,' ||'\n'||
674 ' partition wf_item70,' ||'\n'||
675 ' partition wf_item71,' ||'\n'||
676 ' partition wf_item72,' ||'\n'||
677 ' partition wf_item73,' ||'\n'||
678 ' partition wf_item74,' ||'\n'||
679 ' partition wf_item75,' ||'\n'||
680 ' partition wf_item76,' ||'\n'||
681 ' partition wf_item77);';
682
683 elsif (idxname = 'WF_ITEM_ACTIVITY_STATUSES_N1') then
684 v_prefix := 'WF1_';
685 v_sql := 'create index %s '||'\n'||
686 ' on WF_ITEM_ACTIVITY_STATUSES (ACTIVITY_STATUS, ITEM_TYPE)'||
687 '\n'||
688 ' pctfree 10'||'\n'||
689 ' initrans 10'||'\n'||
690 ' tablespace %s '||'\n'||
691 ' storage (initial 40K next %s '||'\n'||
692 ' freelists 32 freelist groups 4'||'\n'||
693 ' pctincrease %s )' ||'\n'||
694 ' logging;';
695 elsif (idxname = 'WF_ITEM_ACTIVITY_STATUSES_N2') then
696 v_prefix := 'WF2_';
697 v_sql := 'create index %s '||'\n'||
698 ' on WF_ITEM_ACTIVITY_STATUSES (NOTIFICATION_ID)'||'\n'||
699 ' pctfree 10'||'\n'||
700 ' initrans 10'||'\n'||
701 ' tablespace %s '||'\n'||
702 ' storage (initial 40K next %s '||'\n'||
703 ' freelists 32 freelist groups 4'||'\n'||
704 ' pctincrease %s )' ||'\n'||
705 ' logging;';
706 elsif (idxname = 'WF_ITEM_ACTIVITY_STATUSES_N3') then
707 v_prefix := 'WF3_';
708 v_sql := 'create index %s'||'\n'||
709 ' on WF_ITEM_ACTIVITY_STATUSES (ITEM_TYPE,DUE_DATE)'||'\n'||
710 ' pctfree 10'||'\n'||
711 ' initrans 10'||'\n'||
712 ' tablespace %s '||'\n'||
713 ' storage (initial 40K next %s '||'\n'||
714 ' freelists 32 freelist groups 4'||'\n'||
715 ' pctincrease %s )'||'\n'||
716 ' logging;';
717 elsif (idxname = 'WF_ITEM_ACTIVITY_STATUSES_N4') then
718 v_prefix := 'WF4_';
719 v_sql := 'create index %s'||'\n'||
720 ' on WF_ITEM_ACTIVITY_STATUSES (ASSIGNED_USER, ITEM_TYPE)'||'\n'||
721 ' pctfree 10'||'\n'||
722 ' initrans 10'||'\n'||
723 ' tablespace %s '||'\n'||
724 ' storage (initial 40K next %s '||'\n'||
725 ' freelists 32 freelist groups 4'||'\n'||
726 ' pctincrease %s )'||'\n'||
727 ' logging;';
728 elsif (idxname = 'WF_ITEM_ACTIVITY_STATUSES_H_N1') then
729 v_prefix := 'WFH1_';
730 v_sql := 'create index %s '||'\n'||
731 ' on WF_ITEM_ACTIVITY_STATUSES_H (ITEM_TYPE, ITEM_KEY, PROCESS_ACTIVITY)'||'\n'||
732 ' pctfree 10'||'\n'||
733 ' initrans 10'||'\n'||
734 ' tablespace %s '||'\n'||
735 ' storage (initial 40K next %s '||'\n'||
736 ' freelists 32 freelist groups 4'||'\n'||
737 ' pctincrease %s )' ||'\n'||
738 ' logging;';
739 elsif (idxname = 'WF_ITEM_ACTIVITY_STATUSES_H_N2') then
740 v_prefix := 'WFH2_';
741 v_sql := 'create index %s '||'\n'||
742 ' on WF_ITEM_ACTIVITY_STATUSES_H (NOTIFICATION_ID)'||'\n'||
743 ' pctfree 10'||'\n'||
744 ' initrans 10'||'\n'||
745 ' tablespace %s '||'\n'||
746 ' storage (initial 40K next %s '||'\n'||
747 ' freelists 32 freelist groups 4'||'\n'||
748 ' pctincrease %s )' ||'\n'||
749 ' logging;';
750 elsif (idxname = 'WF_ITEM_ACTIVITY_STATUSES_H_N3') then
751 v_prefix := 'WFH3_';
752 v_sql := 'create index %s '||'\n'||
753 ' on WF_ITEM_ACTIVITY_STATUSES_H (ASSIGNED_USER)'||'\n'||
754 ' pctfree 10'||'\n'||
755 ' initrans 10'||'\n'||
756 ' tablespace %s '||'\n'||
757 ' storage (initial 40K next %s '||'\n'||
758 ' freelists 32 freelist groups 4'||'\n'||
759 ' pctincrease %s )' ||'\n'||
760 ' logging;';
761 elsif (idxname = 'WF_ITEM_ATTRIBUTE_VALUES_PK') then
762 partition_index := true;
763 v_sql := 'create unique index %s '||'\n'||
764 ' on WF_ITEM_ATTRIBUTE_VALUES (ITEM_TYPE, ITEM_KEY, NAME)'||'\n'||
765 ' pctfree 10'||'\n'||
766 ' initrans 10'||'\n'||
767 ' tablespace %s '||'\n'||
768 ' storage (initial 40K next %s '||'\n'||
769 ' freelists 32 freelist groups 4'||'\n'||
770 ' pctincrease %s )' ||'\n'||
771 ' logging'||'\n'||
772 ' local ('||'\n'||
773 ' partition wf_item1,' ||'\n'||
774 ' partition wf_item2,' ||'\n'||
778 ' partition wf_item6,' ||'\n'||
775 ' partition wf_item3,' ||'\n'||
776 ' partition wf_item4,' ||'\n'||
777 ' partition wf_item5,' ||'\n'||
779 ' partition wf_item7,' ||'\n'||
780 ' partition wf_item8,' ||'\n'||
781 ' partition wf_item9,' ||'\n'||
782 ' partition wf_item10,' ||'\n'||
783 ' partition wf_item11,' ||'\n'||
784 ' partition wf_item12,' ||'\n'||
785 ' partition wf_item13,' ||'\n'||
786 ' partition wf_item14,' ||'\n'||
787 ' partition wf_item15,' ||'\n'||
788 ' partition wf_item16,' ||'\n'||
789 ' partition wf_item17,' ||'\n'||
790 ' partition wf_item18,' ||'\n'||
791 ' partition wf_item19,' ||'\n'||
792 ' partition wf_item20,' ||'\n'||
793 ' partition wf_item21,' ||'\n'||
794 ' partition wf_item22,' ||'\n'||
795 ' partition wf_item23,' ||'\n'||
796 ' partition wf_item24,' ||'\n'||
797 ' partition wf_item25,' ||'\n'||
798 ' partition wf_item26,' ||'\n'||
799 ' partition wf_item27,' ||'\n'||
800 ' partition wf_item28,' ||'\n'||
801 ' partition wf_item29,' ||'\n'||
802 ' partition wf_item30,' ||'\n'||
803 ' partition wf_item31,' ||'\n'||
804 ' partition wf_item32,' ||'\n'||
805 ' partition wf_item33,' ||'\n'||
806 ' partition wf_item34,' ||'\n'||
807 ' partition wf_item35,' ||'\n'||
808 ' partition wf_item36,' ||'\n'||
809 ' partition wf_item37,' ||'\n'||
810 ' partition wf_item38,' ||'\n'||
811 ' partition wf_item39,' ||'\n'||
812 ' partition wf_item40,' ||'\n'||
813 ' partition wf_item41,' ||'\n'||
814 ' partition wf_item42,' ||'\n'||
815 ' partition wf_item43,' ||'\n'||
816 ' partition wf_item44,' ||'\n'||
817 ' partition wf_item45,' ||'\n'||
818 ' partition wf_item46,' ||'\n'||
819 ' partition wf_item47,' ||'\n'||
820 ' partition wf_item48,' ||'\n'||
821 ' partition wf_item49,' ||'\n'||
822 ' partition wf_item50,' ||'\n'||
823 ' partition wf_item51,' ||'\n'||
824 ' partition wf_item52,' ||'\n'||
825 ' partition wf_item53,' ||'\n'||
826 ' partition wf_item54,' ||'\n'||
827 ' partition wf_item55,' ||'\n'||
828 ' partition wf_item56,' ||'\n'||
829 ' partition wf_item57,' ||'\n'||
830 ' partition wf_item58,' ||'\n'||
831 ' partition wf_item59,' ||'\n'||
832 ' partition wf_item60,' ||'\n'||
833 ' partition wf_item61,' ||'\n'||
834 ' partition wf_item62,' ||'\n'||
835 ' partition wf_item63,' ||'\n'||
836 ' partition wf_item64,' ||'\n'||
837 ' partition wf_item65,' ||'\n'||
838 ' partition wf_item66,' ||'\n'||
839 ' partition wf_item67,' ||'\n'||
840 ' partition wf_item68,' ||'\n'||
841 ' partition wf_item69,' ||'\n'||
842 ' partition wf_item70,' ||'\n'||
843 ' partition wf_item71,' ||'\n'||
844 ' partition wf_item72,' ||'\n'||
845 ' partition wf_item73,' ||'\n'||
846 ' partition wf_item74,' ||'\n'||
847 ' partition wf_item75,' ||'\n'||
848 ' partition wf_item76,' ||'\n'||
849 ' partition wf_item77);';
850 elsif (idxname = 'WF_ITEMS_PK') then
851 partition_index := true;
852 v_sql:= 'create unique index %s '||'\n'||
853 ' on WF_ITEMS (ITEM_TYPE, ITEM_KEY)'||'\n'||
854 ' pctfree 10'||'\n'||
855 ' initrans 10'||'\n'||
856 ' tablespace %s '||'\n'||
857 ' storage (initial 40K next %s '||'\n'||
858 ' freelists 32 freelist groups 4'||'\n'||
859 ' pctincrease %s )' ||'\n'||
860 ' logging'||'\n'||
861 ' local ('||'\n'||
862 ' partition wf_item1,' ||'\n'||
863 ' partition wf_item2,' ||'\n'||
864 ' partition wf_item3,' ||'\n'||
865 ' partition wf_item4,' ||'\n'||
866 ' partition wf_item5,' ||'\n'||
867 ' partition wf_item6,' ||'\n'||
868 ' partition wf_item7,' ||'\n'||
869 ' partition wf_item8,' ||'\n'||
870 ' partition wf_item9,' ||'\n'||
871 ' partition wf_item10,' ||'\n'||
872 ' partition wf_item11,' ||'\n'||
873 ' partition wf_item12,' ||'\n'||
874 ' partition wf_item13,' ||'\n'||
875 ' partition wf_item14,' ||'\n'||
876 ' partition wf_item15,' ||'\n'||
877 ' partition wf_item16,' ||'\n'||
878 ' partition wf_item17,' ||'\n'||
879 ' partition wf_item18,' ||'\n'||
880 ' partition wf_item19,' ||'\n'||
881 ' partition wf_item20,' ||'\n'||
882 ' partition wf_item21,' ||'\n'||
883 ' partition wf_item22,' ||'\n'||
884 ' partition wf_item23,' ||'\n'||
888 ' partition wf_item27,' ||'\n'||
885 ' partition wf_item24,' ||'\n'||
886 ' partition wf_item25,' ||'\n'||
887 ' partition wf_item26,' ||'\n'||
889 ' partition wf_item28,' ||'\n'||
890 ' partition wf_item29,' ||'\n'||
891 ' partition wf_item30,' ||'\n'||
892 ' partition wf_item31,' ||'\n'||
893 ' partition wf_item32,' ||'\n'||
894 ' partition wf_item33,' ||'\n'||
895 ' partition wf_item34,' ||'\n'||
896 ' partition wf_item35,' ||'\n'||
897 ' partition wf_item36,' ||'\n'||
898 ' partition wf_item37,' ||'\n'||
899 ' partition wf_item38,' ||'\n'||
900 ' partition wf_item39,' ||'\n'||
901 ' partition wf_item40,' ||'\n'||
902 ' partition wf_item41,' ||'\n'||
903 ' partition wf_item42,' ||'\n'||
904 ' partition wf_item43,' ||'\n'||
905 ' partition wf_item44,' ||'\n'||
906 ' partition wf_item45,' ||'\n'||
907 ' partition wf_item46,' ||'\n'||
908 ' partition wf_item47,' ||'\n'||
909 ' partition wf_item48,' ||'\n'||
910 ' partition wf_item49,' ||'\n'||
911 ' partition wf_item50,' ||'\n'||
912 ' partition wf_item51,' ||'\n'||
913 ' partition wf_item52,' ||'\n'||
914 ' partition wf_item53,' ||'\n'||
915 ' partition wf_item54,' ||'\n'||
916 ' partition wf_item55,' ||'\n'||
917 ' partition wf_item56,' ||'\n'||
918 ' partition wf_item57,' ||'\n'||
919 ' partition wf_item58,' ||'\n'||
920 ' partition wf_item59,' ||'\n'||
921 ' partition wf_item60,' ||'\n'||
922 ' partition wf_item61,' ||'\n'||
923 ' partition wf_item62,' ||'\n'||
924 ' partition wf_item63,' ||'\n'||
925 ' partition wf_item64,' ||'\n'||
926 ' partition wf_item65,' ||'\n'||
927 ' partition wf_item66,' ||'\n'||
928 ' partition wf_item67,' ||'\n'||
929 ' partition wf_item68,' ||'\n'||
930 ' partition wf_item69,' ||'\n'||
931 ' partition wf_item70,' ||'\n'||
932 ' partition wf_item71,' ||'\n'||
933 ' partition wf_item72,' ||'\n'||
934 ' partition wf_item73,' ||'\n'||
935 ' partition wf_item74,' ||'\n'||
936 ' partition wf_item75,' ||'\n'||
937 ' partition wf_item76,' ||'\n'||
938 ' partition wf_item77);';
939 elsif (idxname = 'WF_ITEMS_N1') then
940 v_sql := 'create index %s '||'\n'||
941 ' on WF_ITEMS (PARENT_ITEM_TYPE, PARENT_ITEM_KEY)'||'\n'||
942 ' pctfree 10'||'\n'||
943 ' initrans 10'||'\n'||
944 ' tablespace %s '||'\n'||
945 ' storage (initial 40K next %s '||'\n'||
946 ' freelists 32 freelist groups 4'||'\n'||
947 ' pctincrease %s )' ||'\n'||
948 ' logging;';
949 elsif (idxname = 'WF_ITEMS_N2') then
950 v_sql := 'create index %s '||'\n'||
951 ' on WF_ITEMS (BEGIN_DATE)'||'\n'||
952 ' pctfree 10'||'\n'||
953 ' initrans 10'||'\n'||
954 ' tablespace %s '||'\n'||
955 ' storage (initial 40K next %s '||'\n'||
956 ' freelists 32 freelist groups 4'||'\n'||
957 ' pctincrease %s )' ||'\n'||
958 ' logging;';
959 elsif (idxname = 'WF_ITEMS_N3') then
960 v_sql := 'create index %s '||'\n'||
961 ' on WF_ITEMS (END_DATE)'||'\n'||
962 ' pctfree 10'||'\n'||
963 ' initrans 10'||'\n'||
964 ' tablespace %s '||'\n'||
965 ' storage (initial 40K next %s '||'\n'||
966 ' freelists 32 freelist groups 4'||'\n'||
967 ' pctincrease %s )' ||'\n'||
968 ' logging;';
969 elsif (idxname = 'WF_ITEMS_N4') then
970 v_sql := 'create index %s '||'\n'||
971 ' on WF_ITEMS (ITEM_TYPE,ROOT_ACTIVITY,OWNER_ROLE)'||'\n'||
972 ' pctfree 10'||'\n'||
973 ' initrans 10'||'\n'||
974 ' tablespace %s '||'\n'||
975 ' storage (initial 40K next %s '||'\n'||
976 ' freelists 32 freelist groups 4'||'\n'||
977 ' pctincrease %s )' ||'\n'||
978 ' logging;';
979 elsif (idxname = 'WF_ITEMS_N5') then
980 v_sql := 'create index %s '||'\n'||
981 ' on WF_ITEMS (USER_KEY)'||'\n'||
982 ' pctfree 10'||'\n'||
983 ' initrans 10'||'\n'||
984 ' tablespace %s '||'\n'||
985 ' storage (initial 40K next %s '||'\n'||
986 ' freelists 32 freelist groups 4'||'\n'||
987 ' pctincrease %s )' ||'\n'||
988 ' logging;';
989 elsif (idxname = 'WF_ITEMS_N6') then
990 v_sql := 'create index %s '||'\n'||
991 ' on WF_ITEMS (OWNER_ROLE )'||'\n'||
992 ' pctfree 10'||'\n'||
993 ' initrans 10'||'\n'||
994 ' tablespace %s '||'\n'||
995 ' storage (initial 40K next %s '||'\n'||
996 ' freelists 32 freelist groups 4'||'\n'||
997 ' pctincrease %s )' ||'\n'||
998 ' logging;';
999
1003 if (partition_index) then
1000 end if;
1001
1002 -- now check if we need to proceed
1004 if (v_idxpartitioned = 'YES') then
1005 -- already partitioned. Just exit.
1006 utl_file.put_line(i_file_handle,'--Index '||idxname||
1007 ' already partitioned.');
1008 utl_file.fflush(i_file_handle);
1009 utl_file.fclose(i_file_handle);
1010 return;
1011 elsif (v_tblpartitioned = 'YES') then
1012 -- first drop the index
1013 drop_index := true;
1014 begin
1015 utl_file.put_line(i_file_handle,'--Execute the statement fist to drop the existing index');
1016 utl_file.put_line(i_file_handle,'Drop index '||idxname);
1017 utl_file.putf(i_file_handle,' '||'\n');
1018 exception
1019 when OTHERS then
1020 utl_file.put_line(i_file_handle,'--Error in SQL: '||
1021 substr('drop index '||idxname,1,200));
1022 utl_file.fclose(i_file_handle);
1023 raise;
1024 end;
1025
1026 end if;
1027 else
1028 if (v_tblpartitioned = 'YES') then
1029 -- Detected table partitioned meant this script had already been run.
1030 -- Just exit.
1031 utl_file.put_line(i_file_handle,'--Table for index '||idxname||' already partitioned.');
1032 utl_file.fclose(i_file_handle);
1033 return;
1034 end if;
1035 end if;
1036
1037 begin
1038 utl_file.put_line(i_file_handle,'-- Execute the statement to create index '|| v_new_index);
1039 utl_file.putf(i_file_handle,v_sql,v_new_index,v_tablespace_name,
1040 to_char(v_next),to_char(v_pctinc));
1041 utl_file.putf(i_file_handle,'\n');
1042 utl_file.fflush(i_file_handle);
1043 exception
1044 when UTL_FILE.WRITE_ERROR then
1045 utl_file.put_line(i_file_handle,'--Error writing sql '||
1046 substr(v_sql,1,220)||'to file');
1047 utl_file.fclose(i_file_handle);
1048 raise;
1049 when UTL_FILE.INVALID_FILEHANDLE then
1050 utl_file.put_line(i_file_handle,'--Error : Invalid file handle ');
1051 utl_file.fclose(i_file_handle);
1052 raise;
1053 when UTL_FILE.INVALID_OPERATION then
1054 utl_file.put_line(i_file_handle,'--Error : Invalid operation');
1055 utl_file.fclose(i_file_handle);
1056 raise;
1057 when OTHERS then
1058 utl_file.put_line(i_file_handle,'Error in SQL: '||substr(v_sql,1,220));
1059 utl_file.fclose(i_file_handle);
1060 raise;
1061 end;
1062
1063 -- Rename only if we did not drop index before
1064 if (not drop_index) then
1065 -- rename orig index to old index
1066 v_old_index := substr(v_prefix||idxname, 1, 30);
1067 utl_file.put_line(i_file_handle,'--Rename '||idxname||' to '||
1068 v_old_index);
1069 v_sql := 'alter index '||idxname||' rename to '||v_old_index||';';
1070 begin
1071 utl_file.put_line(i_file_handle,'--Execute the statement :');
1072 utl_file.put_line(i_file_handle,v_sql);
1073 utl_file.putf(i_file_handle,'\n');
1074 exception
1075 when UTL_FILE.WRITE_ERROR then
1076 utl_file.put_line(i_file_handle,'--Error writing sql '||
1077 substr(v_sql,1,220)||'to file');
1078 utl_file.fclose(i_file_handle);
1079 raise;
1080 when UTL_FILE.INVALID_FILEHANDLE then
1081 utl_file.put_line(i_file_handle,'--Error : Invalid file handle ');
1082 utl_file.fclose(i_file_handle);
1083 raise;
1084 when UTL_FILE.INVALID_OPERATION then
1085 utl_file.put_line(i_file_handle,'--Error : Invalid operation');
1086 utl_file.fclose(i_file_handle);
1087 raise;
1088 when others then
1089 utl_file.fclose(i_file_handle);
1090 raise;
1091 end;
1092 end if;
1093
1094 -- rename new index to orig index
1095 utl_file.put_line(i_file_handle,'--Rename '||v_new_index||' to '||idxname);
1096 v_sql := 'alter index '||v_new_index||' rename to '||idxname||';';
1097 begin
1098 utl_file.put_line(i_file_handle,'--Execute the following sql :');
1099 utl_file.put_line(i_file_handle,v_sql);
1100 utl_file.putf(i_file_handle,' '||'\n');
1101 exception
1102 when UTL_FILE.WRITE_ERROR then
1103 utl_file.put_line(i_file_handle,'--Error writing sql '||
1104 substr(v_sql,1,220)||'to file');
1105 utl_file.fclose(i_file_handle);
1106 raise;
1107 when UTL_FILE.INVALID_FILEHANDLE then
1108 utl_file.put_line(i_file_handle,'--Error : Invalid file handle ');
1109 utl_file.fclose(i_file_handle);
1110 raise;
1111 when UTL_FILE.INVALID_OPERATION then
1112 utl_file.put_line(i_file_handle,'--Error : Invalid operation');
1113 utl_file.fclose(i_file_handle);
1114 raise;
1115 when others then
1116 utl_file.fclose(i_file_handle);
1117 raise;
1118 end;
1119
1120 else
1121 begin
1122 SELECT to_char(least(to_number(VALUE),8)),
1123 to_char(least(to_number(VALUE)*2,8))
1124 INTO v_freelist_groups, v_initrans
1125 FROM V$PARAMETER
1126 WHERE NAME = 'cpu_count';
1127 exception
1128 when NO_DATA_FOUND then
1132 utl_file.put_line(i_file_handle,'-- and that application installation has been completed successfully.');
1129 utl_file.put_line(i_file_handle,'--Error while querying number of CPUs.');
1130 utl_file.put_line(i_file_handle,'-- View V$PARAMETER does not exist.');
1131 utl_file.put_line(i_file_handle,'-- Please check if you login to the correct user, database,');
1133 raise_application_error(-20001,'Missing view V$PARAMETER');
1134 when OTHERS then
1135 utl_file.put_line(i_file_handle,'--Error while querying number of CPUs.');
1136 raise;
1137 end;
1138
1139 begin
1140 SELECT to_char(min(to_number(VALUE)))
1141 INTO v_degree
1142 FROM V$PARAMETER
1143 WHERE NAME IN ('parallel_max_servers','cpu_count');
1144 exception
1145 when NO_DATA_FOUND then
1146 utl_file.put_line(i_file_handle,'--Error while querying number parallel degree.');
1147 utl_file.put_line(i_file_handle,'-- View V$PARAMETER does not exist.');
1148 utl_file.put_line(i_file_handle,'-- Please check if you login to the correct user, database,');
1149
1150 utl_file.put_line(i_file_handle,'-- and that application installation has been completed successfully.');
1151 raise_application_error(-20001,'Missing view V$PARAMETER');
1152
1153 when OTHERS then
1154 utl_file.put_line(i_file_handle,'--Error while querying number of CPUs.');
1155 raise;
1156 end;
1157
1158 if (tblspcname is not null) then
1159 v_tablespace_name := tblspcname;
1160 end if;
1161
1162 v_new_index := 'NN'||substr(idxname, 3, 30);
1163
1164 -- check if new index name exists, if so, may ask to drop it.
1165 begin
1166 select null
1167 into v_dummy
1168 from sys.dual
1169 where exists (select 1
1170 from USER_OBJECTS
1171 where OBJECT_NAME = v_new_index
1172 );
1173 utl_file.put_line(i_file_handle,'Name conflict. Please first drop '||v_new_index);
1174 raise_application_error(-20012, v_new_index||' already exists.');
1175 exception
1176 when NO_DATA_FOUND then
1177 null;
1178 when OTHERS then
1179 utl_file.put_line(i_file_handle,'--Error while checking the new index name.');
1180 raise;
1181 end;
1182
1183
1184 utl_file.put_line(i_file_handle,'--Recreating index '||idxname);
1185
1186 -- v_initial := v_next; -- force it to uniform size
1187 -- v_initial := 1M;
1188 v_pctinc := '0';
1189 v_pctfree := '0';
1190
1191 begin
1192 utl_file.put_line(i_file_handle,'--Create index '||v_new_index);
1193
1194 if (idxname = 'WF_ITEM_ACTIVITY_STATUSES_PK') then
1195 v_sql := 'create unique index %s'||'\n'||
1196 ' on WF_ITEM_ACTIVITY_STATUSES (ITEM_TYPE, ITEM_KEY, PROCESS_ACTIVITY)'||'\n';
1197 v_initial := 10485760; -- 10M
1198 elsif (idxname = 'WF_ITEM_ACTIVITY_STATUSES_N1') then
1199 v_sql := 'create index %s'||'\n'||
1200 ' on WF_ITEM_ACTIVITY_STATUSES (ACTIVITY_STATUS, ITEM_TYPE)'||'\n';
1201 v_initial := 10485760; -- 10M
1202 elsif (idxname = 'WF_ITEM_ACTIVITY_STATUSES_N2') then
1203 v_sql := 'create index %s'||'\n'||
1204 ' on WF_ITEM_ACTIVITY_STATUSES (NOTIFICATION_ID)'||'\n';
1205 v_initial := 10485760; -- 10M
1206 elsif (idxname = 'WF_ITEM_ACTIVITY_STATUSES_N3') then
1207 v_sql := 'create index %s'||'\n'||
1208 ' on WF_ITEM_ACTIVITY_STATUSES (DUE_DATE, ITEM_TYPE)'||'\n';
1209 v_initial := 10485760; -- 10M
1210 elsif (idxname = 'WF_ITEM_ACTIVITY_STATUSES_N4') then
1211 v_sql := 'create index %s'||'\n'||
1212 ' on WF_ITEM_ACTIVITY_STATUSES (ASSIGNED_USER, ITEM_TYPE)'||'\n';
1213 v_initial := 10485760; -- 10M
1214 elsif (idxname = 'WF_ITEM_ACTIVITY_STATUSES_H_N1') then
1215 v_sql := 'create index %s'||'\n'||
1216 ' on WF_ITEM_ACTIVITY_STATUSES_H (ITEM_TYPE, ITEM_KEY, PROCESS_ACTIVITY)'||'\n';
1217 v_initial := 10485760; -- 10M
1218 elsif (idxname = 'WF_ITEM_ACTIVITY_STATUSES_H_N2') then
1219 v_sql := 'create index %s'||'\n'||
1220 ' on WF_ITEM_ACTIVITY_STATUSES_H (NOTIFICATION_ID)'||'\n';
1221 v_initial := 10485760; -- 10M
1222 elsif (idxname = 'WF_ITEM_ACTIVITY_STATUSES_H_N3') then
1223 v_sql := 'create index %s'||'\n'||
1224 ' on WF_ITEM_ACTIVITY_STATUSES_H (ASSIGNED_USER)'||'\n';
1225 v_initial := 10485760; -- 10M
1226 elsif (idxname = 'WF_ITEM_ATTRIBUTE_VALUES_PK') then
1227 v_sql := 'create unique index %s'||'\n'||
1228 ' on WF_ITEM_ATTRIBUTE_VALUES (ITEM_TYPE, ITEM_KEY, NAME)'||'\n';
1229 v_initial := 10485760; -- 10M
1230 elsif (idxname = 'WF_ITEMS_PK') then
1231 v_sql := 'create unique index %s'||'\n'||
1232 ' on WF_ITEMS (ITEM_TYPE, ITEM_KEY)'||'\n';
1233 v_initial := 1048576; -- 1M
1234 elsif (idxname = 'WF_ITEMS_N1') then
1235 v_sql := 'create index %s'||'\n'||
1236 ' on WF_ITEMS (PARENT_ITEM_TYPE, PARENT_ITEM_KEY)'||'\n';
1237 v_initial := 1048576; -- 1M
1238 elsif (idxname = 'WF_ITEMS_N2') then
1239 v_sql := 'create index %s'||'\n'||
1240 ' on WF_ITEMS (BEGIN_DATE)'||'\n';
1241 v_initial := 1048576; -- 1M
1242 elsif (idxname = 'WF_ITEMS_N3') then
1243 v_sql := 'create index %s'||'\n'||
1244 ' on WF_ITEMS (END_DATE)'||'\n';
1245 v_initial := 1048576; -- 1M
1249 v_initial := 1048576; -- 1M
1246 elsif (idxname = 'WF_ITEMS_N4') then
1247 v_sql := 'create index %s'||'\n'||
1248 ' on WF_ITEMS (ITEM_TYPE,ROOT_ACTIVITY,OWNER_ROLE)'||'\n';
1250 elsif (idxname = 'WF_ITEMS_N5') then
1251 v_sql := 'create index %s'||'\n'||
1252 ' on WF_ITEMS (USER_KEY)'||'\n';
1253 v_initial := 1048576; -- 1M
1254 elsif (idxname = 'WF_ITEMS_N6') then
1255 v_sql := 'create index %s'||'\n'||
1256 ' on WF_ITEMS (OWNER_ROLE)'||'\n';
1257 v_initial := 1048576; -- 1M
1258 elsif (idxname = 'WF_ITEMS_U1') then
1259 v_sql := 'create unique index %s'||'\n'||
1260 ' on WF_ITEMS (HA_MIGRATION_FLAG, ITEM_TYPE, ITEM_KEY)'||'\n';
1261 v_initial := 1048576; -- 1M
1262 end if;
1263
1264 utl_file.putf(i_file_handle,v_sql,v_new_index);
1265 utl_file.fflush(i_file_handle);
1266
1267 v_sql := ' pctfree %s'||'\n'||
1268 ' initrans %s'||'\n'||
1269 ' tablespace %s'||'\n'||
1270 ' storage (initial %s next %s'||'\n';
1271 utl_file.putf(i_file_handle,v_sql,v_pctfree,v_initrans,
1272 v_tablespace_name,to_char(v_initial),to_char(v_next));
1273 utl_file.fflush(i_file_handle);
1274 v_sql := ' freelists 2 freelist groups %s'||'\n'||
1275 ' pctincrease 0)'||'\n'||
1276 ' parallel '||'\n'||
1277 ' logging;'||'\n';
1278
1279 utl_file.putf(i_file_handle,v_sql,v_freelist_groups,v_degree);
1280 utl_file.fflush(i_file_handle);
1281
1282 exception
1283 when OTHERS then
1284 utl_file.put_line(i_file_handle,'Error in SQL: '||substr(v_sql,1,220));
1285 raise;
1286 end;
1287
1288 begin
1289 utl_file.put_line(i_file_handle,'--Execute the statement to alter index ');
1290 v_sql := 'alter index %s'||'\n'||
1291 ' noparallel'||'\n'||
1292 ' logging;'||'\n';
1293 utl_file.putf(i_file_handle,v_sql,v_new_index);
1294 utl_file.fflush(i_file_handle);
1295 exception
1296 when OTHERS then
1297 utl_file.put_line(i_file_handle,'Error in SQL: '||substr(v_sql,1,220));
1298 raise;
1299 end;
1300
1301 -- gathering CBO stats
1302 UTL_FILE.PUT_LINE(I_FILE_Handle,'--Gathering CBO Stats on new index '||
1303 v_new_index);
1304 -- begin
1305 -- apps.fnd_stats.gather_index_stats(ownname=>'APPLSYS',
1306 -- indname=>v_new_index);
1307 -- null;
1308 -- end;
1309
1310 -- rename orig index to old index
1311 v_old_index := 'OO'||substr(idxname,3,28);
1312 utl_file.put_line(i_file_handle,'--Rename '||idxname||' to '||v_old_index);
1313 v_sql := 'alter index '||idxname||' rename to '||v_old_index||';';
1314
1315 begin
1316 utl_file.put_line(i_file_handle,v_sql);
1317 exception
1318 when OTHERS then
1319 utl_file.put_line(i_file_handle,'Error in SQL: '||substr(v_sql,1,220));
1320 raise;
1321 end;
1322
1323 -- rename new index to orig index
1324 utl_file.put_line(i_file_handle,'--Rename '||v_new_index||' to '||idxname);
1325 v_sql := 'alter index '||v_new_index||' rename to '||idxname||';';
1326 begin
1327 utl_file.put_line(i_file_handle,v_sql);
1328 exception
1329 when OTHERS then
1330 utl_file.put_line(i_file_handle,'Error in SQL: '||substr(v_sql,1,220));
1331 raise;
1332 end;
1333
1334 end if;
1335
1336 utl_file.fclose(i_file_handle);
1337 exception
1338 when OTHERS then
1339 utl_file.put_line(i_file_handle,'--Error in CreateIndex'||idxname);
1340 utl_file.fclose(i_file_handle);
1341 raise;
1342 end CreateIndex;
1343
1344 PROCEDURE Start_partition(p_tablespace in varchar2,
1345 partition out nocopy boolean)
1346 is
1347 current_space number;
1348 used_space number;
1349 table_list varchar2 (200);
1350 statement varchar2 (1000);
1351 begin
1352 table_list := '''WF_ITEM_ACTIVITY_STATUSES'''||','
1353 ||'''WF_ITEM_ACTIVITY_STATUSES_H'''||','
1354 ||'''WF_ITEM_ATTRIBUTE_VALUES'''||','||'''WF_ITEMS''';
1355 select sum(bytes )/1024/1024
1356 into current_space
1357 from user_free_space
1358 where tablespace_name = p_tablespace;
1359
1360 statement := 'SELECT SUM(BYTES)/1024/1024 FROM USER_SEGMENTS
1361 WHERE TABLESPACE_NAME = '''||p_tablespace||''' AND SEGMENT_NAME in (
1362 SELECT INDEX_NAME FROM USER_INDEXES WHERE PARTITIONED='||'''NO'''
1363 ||' AND TABLE_NAME IN ('||table_list||')
1364 UNION
1365 SELECT TABLE_NAME from USER_TABLES WHERE PARTITIONED='||'''NO'''
1366 ||' AND TABLE_NAME IN ('||table_list||'))';
1367
1368 EXECUTE IMMEDIATE statement INTO used_space;
1369
1370 if (used_space > current_space) then
1371 partition := FALSE;
1372 else
1373 partition := TRUE;
1374 end if;
1375 end;
1376
1377 end Wf_Install;