[Home] [Help]
PACKAGE BODY: APPS.EDW_SIZE_WRAPPER
Source
1 PACKAGE BODY EDW_SIZE_WRAPPER AS
2 /* $Header: EDWAUTSB.pls 115.14 2004/02/13 05:10:48 smulye noship $*/
3
4 PROCEDURE function_call(errbuf OUT NOCOPY VARCHAR2,
5 retcode OUT NOCOPY VARCHAR2,
6 p_log_name IN VARCHAR2,
7 p_from_date IN VARCHAR2,
8 p_to_date IN VARCHAR2,
9 p_input_num_rows IN NUMBER DEFAULT 0,
10 p_custom IN NUMBER DEFAULT 0,
11 p_commit_size IN NUMBER DEFAULT 10000) IS
12
13
14 api_CLAUSE VARCHAR2(2400);
15 l_cursor_id NUMBER;
16 l_dummy NUMBER;
17 l_avg_row_len NUMBER;
18 l_num_rows NUMBER;
19 l_row_cnt_proc_name VARCHAR2(120) := null;
20 l_row_len_proc_name VARCHAR2(120) := null;
21 l_pack_name VARCHAR2(120) := null;
22 l_pack_name_tmp VARCHAR2(10) := null;
23 l_package_found NUMBER := 0;
24 l_count NUMBER := 0;
25 l_count1 NUMBER := 1;
26 l_loop_cnt NUMBER := 0;
27
28 TABLE_NOT_FOUND EXCEPTION;
29 PROCEDURE_NOT_FOUND EXCEPTION;
30 PACKAGE_NOT_FOUND EXCEPTION;
31 PARAMETER_NULL EXCEPTION;
32
33 CURSOR c_get_schema IS
34 select table_name, table_owner, table_type
35 from EDW_SIZE_INPUT where
36 table_logical_name like p_log_name || '%';
37
38 CURSOR c_get_proc_name IS
39 select num_rows_proc_name, row_len_proc_name
40 from EDW_SIZE_INPUT where table_owner = g_schema
41 and table_name = g_table_name;
42
43 CURSOR c_get_all_objects IS
44 select num_rows_proc_name, row_len_proc_name, table_name, table_owner,
45 table_type, table_logical_name
46 from EDW_SIZE_INPUT order by table_owner;
47
48 /*
49 changed from all_objects to user_objects by amitgupt
50 for impact analysis changes
51 */
52 CURSOR c_check_pack IS
53 select count(*)
54 from user_objects where object_type = 'PACKAGE BODY'
55 and object_name = l_pack_name;
56
57
58 BEGIN
59
60 /* api_CLAUSE := 'exec ' || p_table_name||'_SIZE.init_all(''' || p_table_name ||
61 ''', '''|| p_schema || ''', ''' || p_from_date ||''', ''' || p_to_date || ''', '''
62 || p_output_file || ''')' ; */
63
64 errbuf := null;
65 retcode := 0;
66
67 g_from_date := to_date(p_from_date, 'YYYY/MM/DD HH24:MI:SS');
68 g_to_date := to_date(p_to_date, 'YYYY/MM/DD HH24:MI:SS');
69 g_custom := p_custom;
70 g_commit_size := p_commit_size;
71
72 IF (p_log_name ^= 'ALL') THEN
73 g_log_name := p_log_name;
74 l_package_found := 1;
75
76 OPEN c_get_schema;
77 FETCH c_get_schema INTO g_table_name, g_schema, g_table_type;
78 IF c_get_schema%NOTFOUND THEN
79 RAISE TABLE_NOT_FOUND;
80 END IF;
81 CLOSE c_get_schema;
82
83 OPEN c_get_proc_name;
84 FETCH c_get_proc_name INTO l_row_cnt_proc_name, l_row_len_proc_name;
85 CLOSE c_get_proc_name;
86
87 l_loop_cnt := length(l_row_cnt_proc_name);
88 FOR l_count1 in 1.. l_loop_cnt LOOP
89 l_pack_name_tmp := substr(l_row_cnt_proc_name, l_count1, 1);
90 IF l_pack_name_tmp = '.' THEN
91 l_pack_name := substr(l_row_cnt_proc_name, 1, l_count1 - 1);
92 -- dbms_output.put_line('l_pack_name = ' || l_pack_name );
93 EXIT;
94 END IF;
95 END LOOP;
96
97 OPEN c_check_pack;
98 FETCH c_check_pack INTO l_package_found;
99 CLOSE c_check_pack;
100 IF l_package_found = 0 THEN
101 g_message := 'Package ' || l_pack_name || ' (for object ' || p_log_name
102 || ') not existing in the database. ';
103 fnd_file.put_line(FND_FILE.LOG, g_message) ;
104 fnd_file.put_line(FND_FILE.LOG, ' ') ;
105 ELSE
106 api_CLAUSE := 'begin ' || l_row_len_proc_name ||' (:s1, :s2, :s3) ; end; ';
107
108 l_cursor_id := dbms_sql.open_cursor;
109 dbms_sql.parse(l_cursor_id ,api_CLAUSE, DBMS_SQL.V7);
110 dbms_sql.bind_variable(l_cursor_id, ':s1', g_from_date);
111 dbms_sql.bind_variable(l_cursor_id, ':s2', g_to_date);
112 dbms_sql.bind_variable(l_cursor_id, ':s3', l_avg_row_len);
113 l_dummy := dbms_sql.execute(l_cursor_id);
114 dbms_sql.variable_value(l_cursor_id, ':s3', l_avg_row_len);
115 dbms_sql.close_cursor(l_cursor_id);
116
117 IF p_input_num_rows = 0 THEN
118 api_CLAUSE := 'begin ' || l_row_cnt_proc_name ||' (:s1, :s2, :s3) ; end; ';
119
120 l_cursor_id := dbms_sql.open_cursor;
121 dbms_sql.parse(l_cursor_id, api_CLAUSE, DBMS_SQL.V7);
122 dbms_sql.bind_variable(l_cursor_id, ':s1', g_from_date);
123 dbms_sql.bind_variable(l_cursor_id, ':s2', g_to_date);
124 dbms_sql.bind_variable(l_cursor_id, ':s3', l_num_rows);
125 l_dummy := dbms_sql.execute(l_cursor_id);
126 dbms_sql.variable_value(l_cursor_id, ':s3', l_num_rows);
127 dbms_sql.close_cursor(l_cursor_id);
128 ELSE
129 l_num_rows := p_input_num_rows;
130 END IF;
131
132 -- Start calculation and write the results into a file.
133 calculate_detail(l_avg_row_len, l_num_rows);
134 END IF;
135
136 ELSE
137 OPEN c_get_all_objects;
138 LOOP
139 FETCH c_get_all_objects INTO l_row_cnt_proc_name, l_row_len_proc_name,
140 g_table_name, g_schema, g_table_type, g_log_name;
141 EXIT WHEN c_get_all_objects%NOTFOUND;
142
143 IF g_log_name = 'ALL' THEN
144 null;
145 ELSE
146 IF l_row_len_proc_name is null or l_row_cnt_proc_name is null THEN
147 RAISE PROCEDURE_NOT_FOUND;
148 END IF;
149
150 l_package_found := 1;
151 l_loop_cnt := length(l_row_cnt_proc_name);
152 FOR l_count1 in 1.. l_loop_cnt LOOP
153 l_pack_name_tmp := substr(l_row_cnt_proc_name, l_count1, 1);
154 IF l_pack_name_tmp = '.' THEN
155 l_pack_name := substr(l_row_cnt_proc_name, 1, l_count1 - 1);
156 EXIT;
157 END IF;
158 END LOOP;
159
160 OPEN c_check_pack;
161 FETCH c_check_pack INTO l_package_found;
162 CLOSE c_check_pack;
163 IF l_package_found = 0 THEN
164 g_message := 'Package ' || l_pack_name || ' (for object ' || g_log_name
165 || ') not existing in the database. ';
166 fnd_file.put_line(FND_FILE.LOG, g_message) ;
167 fnd_file.put_line(FND_FILE.LOG, ' ') ;
168 -- RAISE PACKAGE_NOT_FOUND;
169 ELSE
170 api_CLAUSE := 'begin ' || l_row_len_proc_name ||' (:s1, :s2, :s3) ; end; ';
171
172 l_cursor_id := dbms_sql.open_cursor;
173 dbms_sql.parse(l_cursor_id ,api_CLAUSE, DBMS_SQL.V7);
174 dbms_sql.bind_variable(l_cursor_id, ':s1', g_from_date);
175 dbms_sql.bind_variable(l_cursor_id, ':s2', g_to_date);
176 dbms_sql.bind_variable(l_cursor_id, ':s3', l_avg_row_len);
177 l_dummy := dbms_sql.execute(l_cursor_id);
178 dbms_sql.variable_value(l_cursor_id, ':s3', l_avg_row_len);
179 dbms_sql.close_cursor(l_cursor_id);
180
181 IF p_input_num_rows = 0 THEN
182 api_CLAUSE := 'begin ' || l_row_cnt_proc_name ||' (:s1, :s2, :s3) ; end; ';
183
184 l_cursor_id := dbms_sql.open_cursor;
185 dbms_sql.parse(l_cursor_id ,api_CLAUSE, DBMS_SQL.V7);
186 dbms_sql.bind_variable(l_cursor_id, ':s1', g_from_date);
187 dbms_sql.bind_variable(l_cursor_id, ':s2', g_to_date);
188 dbms_sql.bind_variable(l_cursor_id, ':s3', l_num_rows);
189 l_dummy := dbms_sql.execute(l_cursor_id);
190
191 dbms_sql.variable_value(l_cursor_id, ':s3', l_num_rows);
192 dbms_sql.close_cursor(l_cursor_id);
193 ELSE
194 l_num_rows := p_input_num_rows;
195 END IF;
196
197 -- Start calculation and write the results into a file.
198 calculate_detail(l_avg_row_len, l_num_rows);
199 END IF;
200 END IF;
201
202 END LOOP;
203 CLOSE c_get_all_objects;
204
205 fnd_file.new_line(FND_FILE.OUTPUT, 2) ;
206 fnd_file.put_line(FND_FILE.OUTPUT, 'In Summary ') ;
207 fnd_file.put_line(FND_FILE.OUTPUT, '------------------------------------------------') ;
208 fnd_file.put_line(FND_FILE.OUTPUT, 'The total space of indeces for all objects is (MB): '
209 || ceil(g_all_index_space/1024/1024)) ;
210 fnd_file.put_line(FND_FILE.OUTPUT, 'The total space of tables for all objects is (MB): '
211 || ceil(g_all_table_space/1024/1024)) ;
212 fnd_file.new_line(FND_FILE.OUTPUT, 2) ;
213
214 END IF;
215
216 EXCEPTION
217 WHEN TABLE_NOT_FOUND THEN
218 g_message := 'No table is found from EDW_SIZE_INPUT. ';
219 fnd_file.put_line(FND_FILE.LOG, g_message) ;
220 fnd_file.put_line(FND_FILE.LOG, ' ') ;
221 WHEN PROCEDURE_NOT_FOUND THEN
222 g_message := 'Procedure name ' || l_row_cnt_proc_name
223 || ' not found from table EDW_SIZE_INPUT. ';
224 fnd_file.put_line(FND_FILE.LOG, g_message) ;
225 fnd_file.put_line(FND_FILE.LOG, ' ') ;
226 WHEN PACKAGE_NOT_FOUND THEN
227 g_message := 'Package ' || l_pack_name
228 || ' not existing in the database. ';
229 fnd_file.put_line(FND_FILE.LOG, g_message) ;
230 fnd_file.put_line(FND_FILE.LOG, ' ') ;
231 WHEN PARAMETER_NULL THEN
232 g_message := 'The parameter can not be null. ';
233 fnd_file.put_line(FND_FILE.LOG, g_message) ;
234 fnd_file.put_line(FND_FILE.LOG, ' ') ;
235 WHEN OTHERS THEN
236 IF sqlerrm = 'ORA-06502' or sqlerrm = 'ORA-06550' THEN
237 g_message := 'Package ' || l_pack_name || ' has error ' || sqlerrm;
238 fnd_file.put_line(FND_FILE.LOG, g_message) ;
239 fnd_file.put_line(FND_FILE.LOG, ' ') ;
240 ELSE
241 errbuf := sqlerrm;
242 retcode := sqlcode;
243 fnd_file.put_line(FND_FILE.LOG, errbuf) ;
244 fnd_file.put_line(FND_FILE.LOG, ' ') ;
245 END IF;
246
247 END function_call; -- procedure function_call.
248
249 PROCEDURE calculate_detail (p_avg_row_len NUMBER, p_num_rows NUMBER) IS
250
251 l_TBL_s NUMBER := 0;
252 l_TBL_l NUMBER := 0;
253 l_TBL_t NUMBER := 0;
254 l_IND_s NUMBER := 0;
255 l_IND_l NUMBER := 0;
256 l_IND_t NUMBER := 0;
257 l_pct_s NUMBER := 0;
258 l_pct_l NUMBER := 0;
259 l_pct_t NUMBER := 0;
260 l_tbl_size_s NUMBER := 0;
261 l_ind_size_s NUMBER := 0;
262 l_tbl_size_l NUMBER := 0;
263 l_ind_size_l NUMBER := 0;
264 l_tbl_size_t NUMBER := 0;
265 l_ind_size_t NUMBER := 0;
266 l_temp_tbl_size NUMBER := 0;
267 l_temp_size NUMBER := 0;
268 l_temp_size_source NUMBER := 0;
269 l_total_pem_space NUMBER := 0;
270 l_total_tmp_space NUMBER := 0;
271 l_rb_size NUMBER := 0;
272 l_constant1 NUMBER := 0;
273 l_constant2 NUMBER := 0;
274 l_constant3 NUMBER := 0;
275 l_constant4 NUMBER := 0;
276 l_constant5 NUMBER := 0;
277 l_constant6 NUMBER := 0;
278 l_row_len_ss NUMBER := 0;
279 l_row_len_m NUMBER := 0;
280 l_row_len_l NUMBER := 0;
281 l_TMP_TBL NUMBER := 0;
282 l_TMP NUMBER := 0;
283 l_RB NUMBER := 0;
284 l_count number := 0;
285 l_num_of_levels number := 0;
286 l_from_date varchar2(11);
287 l_to_date varchar2(11);
288
289 CURSOR c_check IS
290 select count(*)
291 from EDW_SIZE_OUTPUT where
292 table_name = g_table_name
293 and owner = g_schema;
294
295 CURSOR c_get_cons_f IS
296 select avg_row_len1, avg_row_len2, avg_row_len3, max_row_len_ss, max_row_len_m
297 from EDW_SIZE_INPUT where table_name = g_table_name
298 and table_owner = g_schema;
299
300 CURSOR c_get_cons_m IS
301 select num_of_levels, avg_row_len1, avg_row_len2, avg_row_len3,
302 avg_row_len4, avg_row_len5, avg_row_len6, max_row_len_l
303 from EDW_SIZE_INPUT where table_name = g_table_name
304 and table_owner = g_schema;
305
306
307 BEGIN
308
309 l_from_date := to_char(g_from_date, 'DD-MON-YYYY');
310 l_to_date := to_char(g_to_date, 'DD-MON-YYYY');
311
312 OPEN c_check;
313 FETCH c_check INTO l_count;
314 CLOSE c_check;
315
316 IF g_table_type = 'FACT' THEN
317 OPEN c_get_cons_f;
318 FETCH c_get_cons_f INTO l_constant1, l_constant2, l_constant3,
319 l_row_len_ss, l_row_len_m;
320 CLOSE c_get_cons_f;
321
322 l_TBL_s := p_avg_row_len + l_constant1 + g_custom;
323 l_IND_s := l_constant2;
324 l_TBL_t := p_avg_row_len + g_custom;
325 l_IND_t := l_constant3;
326 l_pct_s := 1 + 0.07;
327 l_pct_l := 1 + 0.32;
328 l_pct_t := 1 + 0.32;
329 l_TMP_TBL := 2*l_TBL_s + l_TBL_t;
330 l_TMP := 2*l_row_len_m;
331 l_RB := l_TBL_s + l_TBL_t;
332
333 l_tbl_size_s := ceil(p_num_rows*l_pct_s*l_TBL_s);
334 l_ind_size_s := ceil(p_num_rows*l_pct_l*l_IND_s);
335 l_tbl_size_t := ceil(p_num_rows*l_pct_t*l_TBL_t);
336 l_ind_size_t := ceil(p_num_rows*l_pct_l*l_IND_t);
337
338 l_temp_tbl_size := g_commit_size*l_TMP_TBL;
339 l_temp_size := g_commit_size*l_TMP;
340 l_rb_size := g_commit_size*l_RB;
341 l_temp_size_source := ceil(p_num_rows*1.15*l_TBL_s + g_commit_size*2*l_row_len_ss);
342
343 l_total_pem_space := l_tbl_size_s + l_ind_size_s + l_tbl_size_t + l_ind_size_t;
344 l_total_tmp_space := l_temp_tbl_size + l_temp_size + l_rb_size;
345
346 IF (g_log_name ^= 'ALL') THEN
347 g_all_index_space := g_all_index_space + l_ind_size_s + l_ind_size_t;
348 g_all_table_space := g_all_table_space + l_tbl_size_s + l_tbl_size_t;
349 END IF;
350
351 IF l_count = 0 THEN
355 TABLE_TYPE,
352 insert into edw_size_output(
353 TABLE_NAME,
354 OWNER,
356 TABLE_LOGICAL_NAME,
357 AVG_ROW_LEN_STAGE,
358 AVG_ROW_LEN,
359 AVG_ROW_LEN_IND_S,
360 AVG_ROW_LEN_IND,
361 NUM_ROWS,
362 TABLE_SIZE_STAGE,
363 INDEX_SIZE_STAGE,
364 TABLE_SIZE,
365 INDEX_SIZE,
366 TEMP_TABLE_SIZE,
367 TEMP_SIZE,
368 TEMP_SIZE_SOURCE,
369 RB_SIZE,
370 TOTAL_PEM_SPACE,
371 TOTAL_TMP_SPACE,
372 PCT_FREE_S,
373 PCT_FREE_L,
374 PCT_FREE,
375 FROM_DATE,
376 TO_DATE,
377 LAST_UPDATE_DATE ,
378 LAST_UPDATED_BY,
379 LAST_UPDATE_LOGIN,
380 CREATED_BY,
381 CREATION_DATE) values
382 ( g_table_name,
383 g_schema,
384 g_table_type,
385 g_log_name,
386 l_TBL_s,
387 l_TBL_t,
388 l_IND_s,
389 l_IND_t,
390 p_num_rows,
391 l_tbl_size_s,
392 l_ind_size_s,
393 l_tbl_size_t,
394 l_ind_size_t,
395 l_temp_tbl_size,
396 l_temp_size,
397 l_temp_size_source,
398 l_rb_size,
399 l_total_pem_space,
400 l_total_tmp_space,
401 l_pct_s,
402 l_pct_l,
403 l_pct_t,
404 g_from_date,
405 g_to_date,
406 sysdate, 0, 0, 0, sysdate);
407 g_message := 'Object logical name: ' || g_log_name ||
408 ', one record is inserted';
409 fnd_file.put_line(FND_FILE.LOG, g_message) ;
410 ELSE
411 update edw_size_output set TABLE_LOGICAL_NAME = g_log_name,
412 OWNER = g_schema,
413 AVG_ROW_LEN_STAGE = l_TBL_s,
414 AVG_ROW_LEN = l_TBL_t,
415 AVG_ROW_LEN_IND_S = l_IND_s,
416 AVG_ROW_LEN_IND = l_IND_t,
417 NUM_ROWS = p_num_rows,
418 TABLE_SIZE_STAGE = l_tbl_size_s,
419 INDEX_SIZE_STAGE = l_ind_size_s,
420 TABLE_SIZE = l_tbl_size_t,
421 INDEX_SIZE = l_ind_size_t,
422 TEMP_TABLE_SIZE = l_temp_tbl_size,
423 TEMP_SIZE = l_temp_size,
424 TEMP_SIZE_SOURCE = l_temp_size_source,
425 RB_SIZE = l_rb_size,
426 TOTAL_PEM_SPACE = l_total_pem_space,
427 TOTAL_TMP_SPACE = l_total_tmp_space,
428 PCT_FREE_S = l_pct_s,
429 PCT_FREE_L = l_pct_l,
430 PCT_FREE = l_pct_t,
431 FROM_DATE = g_from_date,
432 TO_DATE = g_to_date,
433 LAST_UPDATE_DATE = sysdate,
434 LAST_UPDATED_BY = 0,
435 LAST_UPDATE_LOGIN = 0,
436 CREATED_BY = 0,
437 CREATION_DATE = sysdate
438 where table_name = g_table_name
439 and owner = g_schema;
440 g_message := 'Object logical name: ' || g_log_name ||
441 ', record is updated';
442 fnd_file.put_line(FND_FILE.LOG, g_message) ;
443 END IF;
444
445 print_f(g_schema,
446 l_from_date,
447 l_to_date,
448 l_temp_size_source,
449 l_TBL_s,
450 l_TBL_t,
451 l_IND_s,
452 l_IND_t,
453 p_num_rows,
454 l_tbl_size_s,
455 l_ind_size_s,
456 l_tbl_size_t,
457 l_ind_size_t,
458 l_temp_size,
459 l_rb_size,
460 l_temp_tbl_size,
461 l_total_pem_space,
462 l_total_tmp_space);
463
464
465 -- calculate size for dimension.
466 ELSIF g_table_type = 'DIMENSION' THEN
467 OPEN c_get_cons_m;
468 FETCH c_get_cons_m INTO l_num_of_levels, l_constant1, l_constant2,
469 l_constant3, l_constant4, l_constant5, l_constant6, l_row_len_l;
470 CLOSE c_get_cons_m;
471
472 l_TBL_s := p_avg_row_len + l_constant1 + g_custom;
473 l_IND_s := l_constant2;
474 l_TBL_l := p_avg_row_len + l_constant3 + g_custom;
475 l_IND_l := l_constant4;
476 l_TBL_t := p_avg_row_len + l_constant5 + g_custom;
477 l_IND_t := l_constant6;
478 l_pct_s := 1 + 0.07;
479 l_pct_l := 1 + 0.32;
480 l_pct_t := 1 + 0.32;
481 l_TMP_TBL := ceil(1.5*l_TBL_t);
482 l_TMP := 2*l_row_len_l;
483 l_RB := l_TBL_l + l_TBL_t;
484
485 l_tbl_size_s := ceil(p_num_rows*l_pct_s*l_TBL_s/l_num_of_levels);
486 l_ind_size_s := ceil(p_num_rows*l_pct_l*l_IND_s/l_num_of_levels);
487 l_tbl_size_l := ceil(p_num_rows*l_pct_t*l_TBL_l/l_num_of_levels);
488 l_ind_size_l := ceil(p_num_rows*l_pct_l*l_IND_l/l_num_of_levels);
489 l_tbl_size_t := ceil(p_num_rows*l_pct_t*l_TBL_t);
490 l_ind_size_t := ceil(p_num_rows*l_pct_l*l_IND_t);
491
492 l_temp_tbl_size := g_commit_size*l_TMP_TBL;
493 l_temp_size := g_commit_size*l_TMP;
494 l_rb_size := g_commit_size*l_RB;
495
496 l_total_pem_space := l_tbl_size_s + l_ind_size_s + l_tbl_size_l +
497 l_ind_size_l + l_tbl_size_t + l_ind_size_t;
501 g_all_index_space := g_all_index_space + l_ind_size_s + l_ind_size_l + l_ind_size_t;
498 l_total_tmp_space := l_temp_tbl_size + l_temp_size + l_rb_size;
499
500 IF (g_log_name ^= 'ALL') THEN
502 g_all_table_space := g_all_table_space + l_tbl_size_s + l_tbl_size_l + l_tbl_size_t;
503 END IF;
504
505 IF l_count = 0 THEN
506 insert into edw_size_output(
507 TABLE_NAME,
508 OWNER,
509 TABLE_TYPE,
510 TABLE_LOGICAL_NAME,
511 AVG_ROW_LEN_STAGE,
512 AVG_ROW_LEN,
513 AVG_ROW_LEN_LEVEL,
514 AVG_ROW_LEN_IND_S,
515 AVG_ROW_LEN_IND,
516 AVG_ROW_LEN_IND_L,
517 NUM_ROWS,
518 TABLE_SIZE_STAGE,
519 INDEX_SIZE_STAGE,
520 TABLE_SIZE_LEVEL,
521 INDEX_SIZE_LEVEL,
522 TABLE_SIZE,
523 INDEX_SIZE,
524 TEMP_TABLE_SIZE,
525 TEMP_SIZE,
526 RB_SIZE,
527 TOTAL_PEM_SPACE,
528 TOTAL_TMP_SPACE,
529 PCT_FREE_S,
530 PCT_FREE_L,
531 PCT_FREE,
532 FROM_DATE,
533 TO_DATE,
534 LAST_UPDATE_DATE ,
535 LAST_UPDATED_BY,
536 LAST_UPDATE_LOGIN,
537 CREATED_BY,
538 CREATION_DATE) values
539 ( g_table_name,
540 g_schema,
541 g_table_type,
542 g_log_name,
543 l_TBL_s,
544 l_TBL_t,
545 l_TBL_l,
546 l_IND_s,
547 l_IND_t,
548 l_IND_l,
549 p_num_rows,
550 l_tbl_size_s,
551 l_ind_size_s,
552 l_tbl_size_l,
553 l_ind_size_l,
554 l_tbl_size_t,
555 l_ind_size_t,
556 l_temp_tbl_size,
557 l_temp_size,
558 l_rb_size,
559 l_total_pem_space,
560 l_total_tmp_space,
561 l_pct_s,
562 l_pct_l,
563 l_pct_t,
564 g_from_date,
565 g_to_date,
566 sysdate, 0, 0, 0, sysdate);
567 g_message := 'Object logical name: ' || g_log_name ||
568 ', one record is inserted';
569 fnd_file.put_line(FND_FILE.LOG, g_message) ;
570 ELSE
571 update edw_size_output set TABLE_LOGICAL_NAME = g_log_name,
572 OWNER = g_schema,
573 AVG_ROW_LEN_STAGE = l_TBL_s,
574 AVG_ROW_LEN = l_TBL_t,
575 AVG_ROW_LEN_LEVEL = l_TBL_l,
576 AVG_ROW_LEN_IND_S = l_IND_s,
577 AVG_ROW_LEN_IND = l_IND_t,
578 AVG_ROW_LEN_IND_L = l_IND_l,
579 NUM_ROWS = p_num_rows,
580 TABLE_SIZE_STAGE = l_tbl_size_s,
581 INDEX_SIZE_STAGE = l_ind_size_s,
582 TABLE_SIZE_LEVEL = l_tbl_size_l,
583 INDEX_SIZE_LEVEL = l_ind_size_l,
584 TABLE_SIZE = l_tbl_size_t,
585 INDEX_SIZE = l_ind_size_t,
586 TEMP_TABLE_SIZE = l_temp_tbl_size,
587 TEMP_SIZE = l_temp_size,
588 RB_SIZE = l_rb_size,
589 TOTAL_PEM_SPACE = l_total_pem_space,
590 TOTAL_TMP_SPACE = l_total_tmp_space,
591 PCT_FREE_S = l_pct_s,
592 PCT_FREE_L = l_pct_l,
593 PCT_FREE = l_pct_t,
594 FROM_DATE = g_from_date,
595 TO_DATE = g_to_date,
596 LAST_UPDATE_DATE = sysdate,
597 LAST_UPDATED_BY = 0,
598 LAST_UPDATE_LOGIN = 0,
599 CREATED_BY = 0,
600 CREATION_DATE = sysdate
601 where table_name = g_table_name
602 and owner = g_schema;
603 g_message := 'Object logical name: ' || g_log_name ||
604 ', record is updated' ;
605 fnd_file.put_line(FND_FILE.LOG, g_message) ;
606 END IF; -- l_count.
607
608 print_m(g_schema,
609 l_from_date,
610 l_to_date,
611 l_TBL_s,
612 l_TBL_l,
613 l_TBL_t,
614 l_IND_s,
615 l_IND_l,
616 l_IND_t,
617 p_num_rows,
618 l_tbl_size_s,
619 l_ind_size_s,
620 l_tbl_size_l,
621 l_ind_size_l,
622 l_tbl_size_t,
623 l_ind_size_t,
624 l_temp_size,
625 l_rb_size,
626 l_temp_tbl_size,
627 l_total_pem_space,
628 l_total_tmp_space);
629
630 END IF; -- g_table_type.
631
632 END calculate_detail;
633
634 PROCEDURE show_results (errbuf OUT NOCOPY VARCHAR2,
635 retcode OUT NOCOPY VARCHAR2,
636 p_log_name IN VARCHAR2) IS
637
638
639 v_TABLE_LOGICAL_NAME VARCHAR2(240);
640 v_TABLE_NAME VARCHAR2(70);
641 v_TABLE_OWNER VARCHAR2(30);
642 v_TABLE_TYPE VARCHAR2(30);
643 v_AVG_ROW_LEN_STAGE NUMBER;
644 v_AVG_ROW_LEN_LEVEL NUMBER;
645 v_AVG_ROW_LEN NUMBER;
646 v_AVG_ROW_LEN_IND_S NUMBER;
647 v_AVG_ROW_LEN_IND_L NUMBER;
651 v_INDEX_SIZE_STAGE NUMBER;
648 v_AVG_ROW_LEN_IND NUMBER;
649 v_NUM_ROWS NUMBER;
650 v_TABLE_SIZE_STAGE NUMBER;
652 v_TABLE_SIZE_LEVEL NUMBER;
653 v_INDEX_SIZE_LEVEL NUMBER;
654 v_TABLE_SIZE NUMBER;
655 v_INDEX_SIZE NUMBER;
656 v_TEMP_SIZE NUMBER;
657 v_RB_SIZE NUMBER;
658 v_TEMP_TABLE_SIZE NUMBER;
659 v_TEMP_SIZE_SOURCE NUMBER;
660 v_TOTAL_PEM_SPACE NUMBER;
661 v_TOTAL_TMP_SPACE NUMBER;
662 v_FROM_DATE DATE;
663 v_TO_DATE DATE;
664 v_total_idx_space NUMBER := 0;
665 v_total_tbl_space NUMBER := 0;
666 l_FROM_DATE VARCHAR2(11);
667 l_TO_DATE VARCHAR2(11);
668
669
670 NO_INFORMATION EXCEPTION;
671
672 CURSOR c_get_type IS
673 select table_name, table_type
674 from EDW_SIZE_OUTPUT where table_logical_name = p_log_name;
675
676 CURSOR c_get_object_f IS
677 select TABLE_LOGICAL_NAME,
678 OWNER,
679 FROM_DATE,
680 TO_DATE,
681 TEMP_SIZE_SOURCE ,
682 AVG_ROW_LEN_STAGE,
683 AVG_ROW_LEN ,
684 AVG_ROW_LEN_IND_S,
685 AVG_ROW_LEN_IND ,
686 NUM_ROWS ,
687 TABLE_SIZE_STAGE ,
688 INDEX_SIZE_STAGE ,
689 TABLE_SIZE ,
690 INDEX_SIZE ,
691 TEMP_SIZE ,
692 RB_SIZE ,
693 TEMP_TABLE_SIZE ,
694 TOTAL_PEM_SPACE ,
695 TOTAL_TMP_SPACE
696 from EDW_SIZE_OUTPUT where table_logical_name = p_log_name;
697
698 CURSOR c_get_object_m IS
699 select TABLE_LOGICAL_NAME,
700 OWNER,
701 FROM_DATE,
702 TO_DATE,
703 AVG_ROW_LEN_STAGE,
704 AVG_ROW_LEN_LEVEL,
705 AVG_ROW_LEN ,
706 AVG_ROW_LEN_IND_S,
707 AVG_ROW_LEN_IND_L,
708 AVG_ROW_LEN_IND ,
709 NUM_ROWS ,
710 TABLE_SIZE_STAGE ,
711 INDEX_SIZE_STAGE ,
712 TABLE_SIZE_LEVEL ,
713 INDEX_SIZE_LEVEL ,
714 TABLE_SIZE ,
715 INDEX_SIZE ,
716 TEMP_SIZE ,
717 RB_SIZE ,
718 TEMP_TABLE_SIZE ,
719 TEMP_SIZE_SOURCE ,
720 TOTAL_PEM_SPACE ,
721 TOTAL_TMP_SPACE
722 from EDW_SIZE_OUTPUT where table_logical_name = p_log_name;
723
724 CURSOR c_get_objects IS
725 select TABLE_LOGICAL_NAME,
726 TABLE_TYPE,
727 OWNER,
728 FROM_DATE,
729 TO_DATE,
730 AVG_ROW_LEN_STAGE,
731 AVG_ROW_LEN_LEVEL,
732 AVG_ROW_LEN ,
733 AVG_ROW_LEN_IND_S,
734 AVG_ROW_LEN_IND_L,
735 AVG_ROW_LEN_IND ,
736 NUM_ROWS ,
737 TABLE_SIZE_STAGE ,
738 INDEX_SIZE_STAGE ,
739 TABLE_SIZE_LEVEL ,
740 INDEX_SIZE_LEVEL ,
741 TABLE_SIZE ,
742 INDEX_SIZE ,
743 TEMP_SIZE ,
744 RB_SIZE ,
745 TEMP_TABLE_SIZE ,
746 TEMP_SIZE_SOURCE ,
747 TOTAL_PEM_SPACE ,
748 TOTAL_TMP_SPACE
749 from EDW_SIZE_OUTPUT order by owner;
750
751 BEGIN
752
753 g_log_name := p_log_name;
754 IF g_log_name ^= 'ALL' THEN
755 OPEN c_get_type;
756 FETCH c_get_type INTO g_table_name, g_table_type;
757 CLOSE c_get_type;
758
759 IF g_table_name is null or g_table_name = 'null' THEN
760 RAISE NO_INFORMATION;
761 END IF;
762
763 IF g_table_type = 'FACT' then
764 OPEN c_get_object_f;
765 FETCH c_get_object_f INTO g_log_name,
766 g_schema,
767 v_FROM_DATE,
768 v_TO_DATE,
769 v_TEMP_SIZE_SOURCE,
770 v_AVG_ROW_LEN_STAGE,
771 v_AVG_ROW_LEN,
772 v_AVG_ROW_LEN_IND_S,
773 v_AVG_ROW_LEN_IND,
774 v_NUM_ROWS ,
775 v_TABLE_SIZE_STAGE,
776 v_INDEX_SIZE_STAGE ,
777 v_TABLE_SIZE ,
778 v_INDEX_SIZE ,
779 v_TEMP_SIZE ,
780 v_RB_SIZE ,
781 v_TEMP_TABLE_SIZE,
782 v_TOTAL_PEM_SPACE,
783 v_TOTAL_TMP_SPACE;
784 CLOSE c_get_object_f;
785 l_FROM_DATE := to_char(v_FROM_DATE, 'DD-MON-YYYY');
786 l_TO_DATE := to_char(v_TO_DATE, 'DD-MON-YYYY');
790 l_TO_DATE,
787
788 print_f(g_schema,
789 l_FROM_DATE,
791 v_TEMP_SIZE_SOURCE ,
792 v_AVG_ROW_LEN_STAGE ,
793 v_AVG_ROW_LEN ,
794 v_AVG_ROW_LEN_IND_S ,
795 v_AVG_ROW_LEN_IND ,
796 v_NUM_ROWS ,
797 v_TABLE_SIZE_STAGE ,
798 v_INDEX_SIZE_STAGE ,
799 v_TABLE_SIZE ,
800 v_INDEX_SIZE ,
801 v_TEMP_SIZE ,
802 v_RB_SIZE ,
803 v_TEMP_TABLE_SIZE ,
804 v_TOTAL_PEM_SPACE ,
805 v_TOTAL_TMP_SPACE );
806 g_message := 'Information for object ' || g_log_name || 'is printed. ';
807 fnd_file.put_line(FND_FILE.LOG, g_message ) ;
808
809 ELSIF g_table_type = 'DIMENSION' then
810 OPEN c_get_object_m;
811 FETCH c_get_object_m INTO g_log_name,
812 g_schema,
813 v_FROM_DATE,
814 v_TO_DATE,
815 v_AVG_ROW_LEN_STAGE,
816 v_AVG_ROW_LEN_LEVEL,
817 v_AVG_ROW_LEN,
818 v_AVG_ROW_LEN_IND_S,
819 v_AVG_ROW_LEN_IND_L,
820 v_AVG_ROW_LEN_IND,
821 v_NUM_ROWS ,
822 v_TABLE_SIZE_STAGE,
823 v_INDEX_SIZE_STAGE ,
824 v_TABLE_SIZE_LEVEL,
825 v_INDEX_SIZE_LEVEL,
826 v_TABLE_SIZE ,
827 v_INDEX_SIZE ,
828 v_TEMP_SIZE ,
829 v_RB_SIZE ,
830 v_TEMP_TABLE_SIZE,
831 v_TEMP_SIZE_SOURCE,
832 v_TOTAL_PEM_SPACE,
833 v_TOTAL_TMP_SPACE;
834 CLOSE c_get_object_m;
835 l_FROM_DATE := to_char(v_FROM_DATE, 'DD-MON-YYYY');
836 l_TO_DATE := to_char(v_TO_DATE, 'DD-MON-YYYY');
837
838 print_m(g_schema,
839 l_FROM_DATE,
840 l_TO_DATE,
841 v_AVG_ROW_LEN_STAGE ,
842 v_AVG_ROW_LEN_LEVEL ,
843 v_AVG_ROW_LEN ,
844 v_AVG_ROW_LEN_IND_S ,
845 v_AVG_ROW_LEN_IND_L ,
846 v_AVG_ROW_LEN_IND ,
847 v_NUM_ROWS ,
848 v_TABLE_SIZE_STAGE ,
849 v_INDEX_SIZE_STAGE ,
850 v_TABLE_SIZE_LEVEL ,
851 v_INDEX_SIZE_LEVEL ,
852 v_TABLE_SIZE ,
853 v_INDEX_SIZE ,
854 v_TEMP_SIZE ,
855 v_RB_SIZE ,
856 v_TEMP_TABLE_SIZE ,
857 v_TOTAL_PEM_SPACE ,
858 v_TOTAL_TMP_SPACE );
859 g_message := 'Information for object ' || g_log_name || 'is printed. ';
860 fnd_file.put_line(FND_FILE.LOG, g_message ) ;
861
862 END IF;
863 ELSE
864 OPEN c_get_objects;
865 LOOP
866 FETCH c_get_objects INTO V_TABLE_LOGICAL_NAME,
867 g_table_type,
868 g_schema,
869 v_FROM_DATE,
870 v_TO_DATE,
871 v_AVG_ROW_LEN_STAGE,
872 v_AVG_ROW_LEN_LEVEL,
873 v_AVG_ROW_LEN,
874 v_AVG_ROW_LEN_IND_S,
875 v_AVG_ROW_LEN_IND_L,
876 v_AVG_ROW_LEN_IND,
877 v_NUM_ROWS ,
878 v_TABLE_SIZE_STAGE,
879 v_INDEX_SIZE_STAGE ,
880 v_TABLE_SIZE_LEVEL,
881 v_INDEX_SIZE_LEVEL,
882 v_TABLE_SIZE ,
883 v_INDEX_SIZE ,
884 v_TEMP_SIZE ,
885 v_RB_SIZE ,
886 v_TEMP_TABLE_SIZE,
887 v_TEMP_SIZE_SOURCE,
888 v_TOTAL_PEM_SPACE,
889 v_TOTAL_TMP_SPACE;
890 EXIT WHEN c_get_objects%NOTFOUND;
891
892 IF V_TABLE_LOGICAL_NAME is null or V_TABLE_LOGICAL_NAME = 'null' THEN
893 RAISE NO_INFORMATION;
894 END IF;
895
896 l_FROM_DATE := to_char(v_FROM_DATE, 'DD-MON-YYYY');
897 l_TO_DATE := to_char(v_TO_DATE, 'DD-MON-YYYY');
898
899 IF g_table_type = 'FACT' THEN
900 fnd_file.new_line(FND_FILE.OUTPUT, 2) ;
904 print_f(g_schema,
901 fnd_file.put_line(FND_FILE.OUTPUT, 'Size Estimation Result for Table: '
902 || V_TABLE_LOGICAL_NAME || '(' || g_table_type || ')') ;
903 fnd_file.put_line(FND_FILE.OUTPUT, '------------------------------------------------') ;
905 l_FROM_DATE,
906 l_TO_DATE,
907 v_TEMP_SIZE_SOURCE ,
908 v_AVG_ROW_LEN_STAGE ,
909 v_AVG_ROW_LEN ,
910 v_AVG_ROW_LEN_IND_S ,
911 v_AVG_ROW_LEN_IND ,
912 v_NUM_ROWS ,
913 v_TABLE_SIZE_STAGE ,
914 v_INDEX_SIZE_STAGE ,
915 v_TABLE_SIZE ,
916 v_INDEX_SIZE ,
917 v_TEMP_SIZE ,
918 v_RB_SIZE ,
919 v_TEMP_TABLE_SIZE ,
920 v_TOTAL_PEM_SPACE ,
921 v_TOTAL_TMP_SPACE );
922 null;
923 g_message := 'Information for object ' || g_log_name || 'is printed. ';
924 fnd_file.put_line(FND_FILE.LOG, g_message ) ;
925 v_total_idx_space := v_total_idx_space + v_INDEX_SIZE_STAGE + v_INDEX_SIZE;
926 v_total_tbl_space := v_total_tbl_space + v_TABLE_SIZE_STAGE + v_TABLE_SIZE;
927 ELSE
928 fnd_file.new_line(FND_FILE.OUTPUT, 2) ;
929 fnd_file.put_line(FND_FILE.OUTPUT, 'Size Estimation Result for Table: '
930 || V_TABLE_LOGICAL_NAME || '(' || g_table_type || ')') ;
931 fnd_file.put_line(FND_FILE.OUTPUT, '------------------------------------------------') ;
932 print_m(g_schema,
933 l_FROM_DATE,
934 l_TO_DATE,
935 v_AVG_ROW_LEN_STAGE ,
936 v_AVG_ROW_LEN_LEVEL ,
937 v_AVG_ROW_LEN ,
938 v_AVG_ROW_LEN_IND_S ,
939 v_AVG_ROW_LEN_IND_L ,
940 v_AVG_ROW_LEN_IND ,
941 v_NUM_ROWS ,
942 v_TABLE_SIZE_STAGE ,
943 v_INDEX_SIZE_STAGE ,
944 v_TABLE_SIZE_LEVEL ,
945 v_INDEX_SIZE_LEVEL ,
946 v_TABLE_SIZE ,
947 v_INDEX_SIZE ,
948 v_TEMP_SIZE ,
949 v_RB_SIZE ,
950 v_TEMP_TABLE_SIZE ,
951 v_TOTAL_PEM_SPACE ,
952 v_TOTAL_TMP_SPACE );
953 g_message := 'Information for object ' || g_log_name || 'is printed. ';
954 fnd_file.put_line(FND_FILE.LOG, g_message ) ;
955 v_total_idx_space := v_total_idx_space + v_INDEX_SIZE_STAGE +
956 v_INDEX_SIZE_LEVEL + v_INDEX_SIZE;
957 v_total_tbl_space := v_total_tbl_space + v_TABLE_SIZE_STAGE +
958 v_TABLE_SIZE_LEVEL + v_TABLE_SIZE;
959 END IF;
960 END LOOP;
961 CLOSE c_get_objects;
962 fnd_file.new_line(FND_FILE.OUTPUT, 2) ;
963 fnd_file.put_line(FND_FILE.OUTPUT, 'In Summary') ;
964 fnd_file.put_line(FND_FILE.OUTPUT, '------------------------------------------------') ;
965 fnd_file.put_line(FND_FILE.OUTPUT, 'The total index space of all objects is (MB): '
966 || ceil(v_total_idx_space/1024/1024)) ;
967 fnd_file.put_line(FND_FILE.OUTPUT, 'The total table space of all objects is (MB): '
968 || ceil(v_total_tbl_space/1024/1024)) ;
969 fnd_file.new_line(FND_FILE.OUTPUT, 2) ;
970 END IF;
971
972 EXCEPTION
973 WHEN NO_INFORMATION THEN
974 g_message := 'No information found for object: ';
975 fnd_file.put_line(FND_FILE.LOG, g_message || g_log_name) ;
976 fnd_file.put_line(FND_FILE.LOG, ' ') ;
977 WHEN OTHERS THEN
978 errbuf := sqlerrm;
979 retcode := sqlcode;
980 fnd_file.put_line(FND_FILE.LOG, errbuf) ;
981 fnd_file.put_line(FND_FILE.LOG, ' ') ;
982
983 END show_results; -- procedure show_results.
984
985 PROCEDURE print_f (v_TABLE_OWNER VARCHAR2,
986 v_FROM_DATE VARCHAR2,
987 v_TO_DATE VARCHAR2,
988 v_TEMP_SIZE_SOURCE NUMBER,
989 v_AVG_ROW_LEN_STAGE NUMBER,
990 v_AVG_ROW_LEN NUMBER,
991 v_AVG_ROW_LEN_IND_S NUMBER,
992 v_AVG_ROW_LEN_IND NUMBER,
993 v_NUM_ROWS NUMBER,
994 v_TABLE_SIZE_STAGE NUMBER,
995 v_INDEX_SIZE_STAGE NUMBER,
996 v_TABLE_SIZE NUMBER,
997 v_INDEX_SIZE NUMBER,
998 v_TEMP_SIZE NUMBER,
999 v_RB_SIZE NUMBER,
1000 v_TEMP_TABLE_SIZE NUMBER,
1001 v_TOTAL_PEM_SPACE NUMBER,
1005
1002 v_TOTAL_TMP_SPACE NUMBER) AS
1003
1004 BEGIN
1006 fnd_file.new_line(FND_FILE.OUTPUT, 2) ;
1007 IF g_log_name = 'ALL' THEN
1008 null;
1009 ELSE
1010 fnd_file.put_line(FND_FILE.OUTPUT, 'Size Estimation Result for Table: '
1011 || g_log_name || '(' || g_table_type || ')') ;
1012 fnd_file.put_line(FND_FILE.OUTPUT, '------------------------------------------------') ;
1013 END IF;
1014
1015 fnd_file.new_line(FND_FILE.OUTPUT, 2) ;
1016 fnd_file.put(FND_FILE.OUTPUT, 'Schema: ' || v_TABLE_OWNER) ;
1017 fnd_file.new_line(FND_FILE.OUTPUT, 2) ;
1018 fnd_file.put(FND_FILE.OUTPUT, 'From Date: ' || v_FROM_DATE) ;
1019 fnd_file.new_line(FND_FILE.OUTPUT, 2) ;
1020 fnd_file.put(FND_FILE.OUTPUT, 'To Date: ' || v_TO_DATE) ;
1021 fnd_file.new_line(FND_FILE.OUTPUT, 2) ;
1022 fnd_file.put(FND_FILE.OUTPUT, 'Size of Temporary Space on Source Side (bytes): '
1023 || v_TEMP_SIZE_SOURCE) ;
1024 fnd_file.new_line(FND_FILE.OUTPUT, 2) ;
1025 fnd_file.put(FND_FILE.OUTPUT, 'Number of Rows: ' || v_NUM_ROWS) ;
1026 fnd_file.new_line(FND_FILE.OUTPUT, 2) ;
1027 fnd_file.put(FND_FILE.OUTPUT, 'Avg Row Length of Interface Table (bytes): '
1028 || v_AVG_ROW_LEN_STAGE) ;
1029 fnd_file.new_line(FND_FILE.OUTPUT, 2) ;
1030 fnd_file.put(FND_FILE.OUTPUT, 'Avg Row Length of the Fact (bytes): '
1031 || v_AVG_ROW_LEN) ;
1032 fnd_file.new_line(FND_FILE.OUTPUT, 2) ;
1033 fnd_file.put(FND_FILE.OUTPUT, 'Avg Row Length of Index for Interface Table (bytes): '
1034 || v_AVG_ROW_LEN_IND_S) ;
1035 fnd_file.new_line(FND_FILE.OUTPUT, 2) ;
1036 fnd_file.put(FND_FILE.OUTPUT, 'Avg Row Length of Index for the Fact (bytes): '
1037 || v_AVG_ROW_LEN_IND) ;
1038 fnd_file.new_line(FND_FILE.OUTPUT, 2) ;
1039 fnd_file.put(FND_FILE.OUTPUT, 'Size of Interface Table (bytes): ' || v_TABLE_SIZE_STAGE) ;
1040 fnd_file.new_line(FND_FILE.OUTPUT, 2) ;
1041 fnd_file.put(FND_FILE.OUTPUT, 'Size of Index for Interface Table (bytes): '
1042 || v_INDEX_SIZE_STAGE) ;
1043 fnd_file.new_line(FND_FILE.OUTPUT, 2) ;
1044 fnd_file.put(FND_FILE.OUTPUT, 'Size of the Fact (bytes): ' || v_TABLE_SIZE) ;
1045 fnd_file.new_line(FND_FILE.OUTPUT, 2) ;
1046 fnd_file.put(FND_FILE.OUTPUT, 'Size of Index of the Fact (bytes): ' || v_INDEX_SIZE) ;
1047 fnd_file.new_line(FND_FILE.OUTPUT, 2) ;
1048 fnd_file.put(FND_FILE.OUTPUT, 'Size of Temporary Space (bytes): ' || v_TEMP_SIZE) ;
1049 fnd_file.new_line(FND_FILE.OUTPUT, 2) ;
1050 fnd_file.put(FND_FILE.OUTPUT, 'Size of Rollback Segments (bytes): ' || v_RB_SIZE) ;
1051 fnd_file.new_line(FND_FILE.OUTPUT, 2) ;
1052 fnd_file.put(FND_FILE.OUTPUT, 'Size of Temporary Table Space (bytes): '
1053 || v_TEMP_TABLE_SIZE) ;
1054 fnd_file.new_line(FND_FILE.OUTPUT, 2) ;
1055 fnd_file.put(FND_FILE.OUTPUT, 'Total Size of Permanent Space (bytes): '
1056 || v_TOTAL_PEM_SPACE) ;
1057 fnd_file.new_line(FND_FILE.OUTPUT, 2) ;
1058 fnd_file.put(FND_FILE.OUTPUT, 'Total Size of Temporary Space (bytes): '
1059 || v_TOTAL_TMP_SPACE) ;
1060 fnd_file.new_line(FND_FILE.OUTPUT, 2) ;
1061 END print_f;
1062
1063 PROCEDURE print_m (v_TABLE_OWNER VARCHAR2,
1064 v_FROM_DATE VARCHAR2,
1065 v_TO_DATE VARCHAR2,
1066 v_AVG_ROW_LEN_STAGE NUMBER,
1067 v_AVG_ROW_LEN_LEVEL NUMBER,
1068 v_AVG_ROW_LEN NUMBER,
1069 v_AVG_ROW_LEN_IND_S NUMBER,
1070 v_AVG_ROW_LEN_IND_L NUMBER,
1071 v_AVG_ROW_LEN_IND NUMBER,
1072 v_NUM_ROWS NUMBER,
1073 v_TABLE_SIZE_STAGE NUMBER,
1074 v_INDEX_SIZE_STAGE NUMBER,
1075 v_TABLE_SIZE_LEVEL NUMBER,
1076 v_INDEX_SIZE_LEVEL NUMBER,
1077 v_TABLE_SIZE NUMBER,
1078 v_INDEX_SIZE NUMBER,
1079 v_TEMP_SIZE NUMBER,
1080 v_RB_SIZE NUMBER,
1081 v_TEMP_TABLE_SIZE NUMBER,
1082 v_TOTAL_PEM_SPACE NUMBER,
1083 v_TOTAL_TMP_SPACE NUMBER) IS
1084
1085 BEGIN
1086
1087 fnd_file.new_line(FND_FILE.OUTPUT, 2) ;
1088 IF g_log_name = 'ALL' THEN
1089 null;
1090 ELSE
1091 fnd_file.put_line(FND_FILE.OUTPUT, 'Size Estimation Result for Table: '
1092 || g_log_name || '(' || g_table_type || ')') ;
1093 fnd_file.put_line(FND_FILE.OUTPUT, '------------------------------------------------') ;
1094 END IF;
1095
1096 fnd_file.new_line(FND_FILE.OUTPUT, 2) ;
1097 fnd_file.put(FND_FILE.OUTPUT, 'Schema: ' || v_TABLE_OWNER) ;
1098 fnd_file.new_line(FND_FILE.OUTPUT, 2) ;
1102 fnd_file.new_line(FND_FILE.OUTPUT, 2) ;
1099 fnd_file.put(FND_FILE.OUTPUT, 'From Date: ' || v_FROM_DATE) ;
1100 fnd_file.new_line(FND_FILE.OUTPUT, 2) ;
1101 fnd_file.put(FND_FILE.OUTPUT, 'To Date: ' || v_TO_DATE) ;
1103 fnd_file.put(FND_FILE.OUTPUT, 'Number of Rows: ' || v_NUM_ROWS) ;
1104 fnd_file.new_line(FND_FILE.OUTPUT, 2) ;
1105 fnd_file.put(FND_FILE.OUTPUT, 'Avg Row Length of All Interface Tables (bytes): '
1106 || v_AVG_ROW_LEN_STAGE) ;
1107 fnd_file.new_line(FND_FILE.OUTPUT, 2) ;
1108 fnd_file.put(FND_FILE.OUTPUT, 'Avg Row Length of All Level Tables (bytes): '
1109 || v_AVG_ROW_LEN_LEVEL) ;
1110 fnd_file.new_line(FND_FILE.OUTPUT, 2) ;
1111 fnd_file.put(FND_FILE.OUTPUT, 'Avg Row Length of the Dimension (bytes): '
1112 || v_AVG_ROW_LEN) ;
1113 fnd_file.new_line(FND_FILE.OUTPUT, 2) ;
1114 fnd_file.put(FND_FILE.OUTPUT, 'Avg Row Length of Index of All Interface Tables (bytes): '
1115 || v_AVG_ROW_LEN_IND_S) ;
1116 fnd_file.new_line(FND_FILE.OUTPUT, 2) ;
1117 fnd_file.put(FND_FILE.OUTPUT, 'Avg Row Length of Index of All Level Tables (bytes): '
1118 || v_AVG_ROW_LEN_IND_L) ;
1119 fnd_file.new_line(FND_FILE.OUTPUT, 2) ;
1120 fnd_file.put(FND_FILE.OUTPUT, 'Avg Row Length of Index of the Dimension (bytes): '
1121 || v_AVG_ROW_LEN_IND) ;
1122 fnd_file.new_line(FND_FILE.OUTPUT, 2) ;
1123 fnd_file.put(FND_FILE.OUTPUT, 'Size of Interface Table (bytes): '
1124 || v_TABLE_SIZE_STAGE) ;
1125 fnd_file.new_line(FND_FILE.OUTPUT, 2) ;
1126 fnd_file.put(FND_FILE.OUTPUT, 'Size of Index of Interface Table (bytes): '
1127 || v_INDEX_SIZE_STAGE) ;
1128 fnd_file.new_line(FND_FILE.OUTPUT, 2) ;
1129 fnd_file.put(FND_FILE.OUTPUT, 'Size of Level Table (bytes): '
1130 || v_TABLE_SIZE_LEVEL) ;
1131 fnd_file.new_line(FND_FILE.OUTPUT, 2) ;
1132 fnd_file.put(FND_FILE.OUTPUT, 'Size of Index of Level Table (bytes): '
1133 || v_INDEX_SIZE_LEVEL) ;
1134 fnd_file.new_line(FND_FILE.OUTPUT, 2) ;
1135 fnd_file.put(FND_FILE.OUTPUT, 'Size of the Dimension (bytes): ' || v_TABLE_SIZE) ;
1136 fnd_file.new_line(FND_FILE.OUTPUT, 2) ;
1137 fnd_file.put(FND_FILE.OUTPUT, 'Size of Index of the Dimension (bytes): ' || v_INDEX_SIZE) ;
1138 fnd_file.new_line(FND_FILE.OUTPUT, 2) ;
1139 fnd_file.put(FND_FILE.OUTPUT, 'Size of Temporary Space (bytes): ' || v_TEMP_SIZE) ;
1140 fnd_file.new_line(FND_FILE.OUTPUT, 2) ;
1141 fnd_file.put(FND_FILE.OUTPUT, 'Size of Rollback Segments (bytes): ' || v_RB_SIZE) ;
1142 fnd_file.new_line(FND_FILE.OUTPUT, 2) ;
1143 fnd_file.put(FND_FILE.OUTPUT, 'Size of Temporary Table Space (bytes): '
1144 || v_TEMP_TABLE_SIZE) ;
1145 fnd_file.new_line(FND_FILE.OUTPUT, 2) ;
1146 fnd_file.put(FND_FILE.OUTPUT, 'Total Size of Permanent Space (bytes): '
1147 || v_TOTAL_PEM_SPACE) ;
1148 fnd_file.new_line(FND_FILE.OUTPUT, 2) ;
1149 fnd_file.put(FND_FILE.OUTPUT, 'Total Size of Temporary Space (bytes): '
1150 || v_TOTAL_TMP_SPACE) ;
1151 fnd_file.new_line(FND_FILE.OUTPUT, 2) ;
1152
1153 END print_m; -- procedure print_m.
1154
1155
1156 END EDW_SIZE_WRAPPER; -- package body SIZE_WRAPPER.