1 PACKAGE BODY fnd_ts_size AS
2 /* $Header: fndpsizb.pls 120.4 2005/11/22 15:16:16 mnovakov noship $ */
3
4 TYPE rec_type IS RECORD (
5 owner VARCHAR2(30),
6 table_name VARCHAR2(30),
7 tablespace_name VARCHAR2(30),
8 object_class VARCHAR2(30),
9 parent VARCHAR2(30),
10 object_type VARCHAR2(30),
11 subobject_type VARCHAR2(30),
12 classified VARCHAR2(3),
13 partitioned VARCHAR2(3));
14
15 FUNCTION get_tsp_name (p_tablespace_type IN VARCHAR2)
16 RETURN VARCHAR2
17 IS
18 CURSOR tsp_csr IS
19 SELECT tablespace
20 FROM fnd_tablespaces
21 WHERE tablespace_type = p_tablespace_type;
22 l_tablespace_name VARCHAR2(30);
23 BEGIN
24 OPEN tsp_csr;
25 FETCH tsp_csr INTO l_tablespace_name;
26 CLOSE tsp_csr;
27 RETURN l_tablespace_name;
28 END get_tsp_name;
29
30 PROCEDURE ins_fnd_ts_sizing (cur_rec IN rec_type,
31 p_uni_extent IN NUMBER,
32 p_allocation_type IN VARCHAR2,
33 p_creation_date IN DATE,
34 p_partition_name IN VARCHAR2 DEFAULT NULL)
35 IS
36 tot_blks NUMBER(15);
37 tot_byts NUMBER(15);
38 unused_blks NUMBER;
39 unused_byts NUMBER;
40 used_byts NUMBER(15);
41 lst_ext_file NUMBER;
42 lst_ext_blk NUMBER;
43 lst_usd_blk NUMBER;
44 l_err_status VARCHAR2(30);
45 l_err_code VARCHAR2(4000);
46 l_tablespace_name VARCHAR2(30);
47 l_object_type VARCHAR2(30);
48 l_uniform_extent_size NUMBER(15);
49 l_number_of_extents NUMBER(15);
50 l_total_bytes_required NUMBER(15);
51
52 l_version NUMBER;
53
54 CURSOR lob_part_siz_csr IS
55 SELECT bytes
56 FROM dba_segments
57 WHERE owner = cur_rec.owner
58 AND segment_name = cur_rec.table_name
59 AND partition_name = p_partition_name;
60 BEGIN
61 l_tablespace_name := get_tsp_name(cur_rec.object_class);
62
63 /*
64 if SUBSTR(cur_rec.table_name, 1, 7) = 'SYS_LOB' then
65 if cur_rec.partitioned = 'YES' then
66 l_object_type := 'LOB PARTITION';
67 else
68 l_object_type := 'LOBSEGMENT';
69 end if;
70 elsif SUBSTR(cur_rec.table_name, 1, 6) = 'SYS_IL' then
71 if cur_rec.partitioned = 'YES' then
72 l_object_type := 'LOB INDEX PARTITION';
73 else
74 l_object_type := 'LOBINDEX';
75 end if;
76 else
77 l_object_type := cur_rec.object_type;
78 end if;
79 */
80 l_object_type := cur_rec.object_type;
81
82 if cur_rec.object_type = 'LOB PARTITION' then
83 l_version := fnd_ts_mig_util.get_db_version;
84 end if;
85
86 if cur_rec.object_type = 'LOB PARTITION' AND l_version < 10 then
87 OPEN lob_part_siz_csr;
88 FETCH lob_part_siz_csr INTO tot_byts;
89 CLOSE lob_part_siz_csr;
90 unused_byts := 0;
91 else
92 begin
93 DBMS_SPACE.UNUSED_SPACE (
94 cur_rec.owner,
95 cur_rec.table_name,
96 cur_rec.object_type,
97 tot_blks,
98 tot_byts,
99 unused_blks,
100 unused_byts,
101 lst_ext_file,
102 lst_ext_blk,
103 lst_usd_blk,
104 p_partition_name);
105 exception when others then
106 l_err_status := 'ERROR';
107 tot_byts := 0;
108 tot_blks := 0;
109 unused_byts := 0;
110 unused_blks := 0;
111 l_err_code := sqlerrm;
112 end;
113 end if;
114 used_byts := tot_byts - unused_byts;
115
116 if P_ALLOCATION_TYPE = 'A' then
117 l_uniform_extent_size:= null;
118 l_number_of_extents:= null ;
119 if tot_byts <=1048576 then
120 l_total_bytes_required:= CEIL(used_byts/65536) * (65536);
121 l_uniform_extent_size:= 65536;
122 l_number_of_extents:= CEIL(used_byts/65536);
123 end if;
124
125 if (tot_byts <=67108864 and tot_byts >1048576) then
126 l_total_bytes_required:= CEIL(used_byts/1048576) * (1048576);
127 l_uniform_extent_size:= 1048576;
128 l_number_of_extents:= CEIL(used_byts/1048576);
129 end if;
130
131 if (tot_byts <=1073741824 and tot_byts > 67108864 ) then
132 l_uniform_extent_size:= 8388608;
133 l_number_of_extents:= CEIL(used_byts/8388608);
134 l_total_bytes_required:= CEIL(used_byts/8388608) * (8388608);
135 end if;
136
137 if tot_byts > 1073741824 then
138 l_total_bytes_required:= CEIL(used_byts/67108864) * (67108864);
139 l_uniform_extent_size:= 67108864;
140 l_number_of_extents:= CEIL(used_byts/67108864);
141 end if;
142
143 else
144 if P_ALLOCATION_TYPE = 'U' then
145 l_uniform_extent_size:= p_uni_extent;
146 l_number_of_extents:= CEIL(used_byts/p_uni_extent);
147 l_total_bytes_required:= (ceil(used_byts/p_uni_extent))*(p_uni_extent);
148 end if;
149 end if;
150
151 INSERT INTO fnd_ts_sizing (
152 owner,
153 old_tablespace,
154 new_tablespace,
155 object_type,
156 object_name,
157 parent_object_name,
158 current_extents,
159 current_bytes,
160 free_bytes,
161 used_bytes,
162 uniform_extent_size,
163 number_of_extents,
164 allocation_type,
165 total_bytes_required,
166 sizing_error_status,
167 error_code,
168 classified,
169 partitioned,
170 creation_date)
171 values (
172 cur_rec.owner,
173 cur_rec.tablespace_name,
174 l_tablespace_name,
175 l_object_type,
176 cur_rec.table_name,
177 cur_rec.parent,
178 NULL,
179 tot_byts,
180 unused_byts,
181 used_byts,
182 l_uniform_extent_size,
183 --p_uni_extent,
184 --CEIL(used_byts/p_uni_extent),
185 l_number_of_extents,
186 --CEIL(used_byts/p_uni_extent) * p_uni_extent,
187 p_allocation_type,
188 l_total_bytes_required,
189 l_err_status,
190 l_err_code,
191 cur_rec.classified,
192 cur_rec.partitioned,
193 p_creation_date);
194
195 END ins_fnd_ts_sizing;
196
197 PROCEDURE gen_tab_sizing (p_app IN VARCHAR2,
198 p_uni_extent IN NUMBER,
199 p_allocation_type IN VARCHAR2,
200 p_creation_date IN DATE)
201 IS
202 cur_rec rec_type;
203
204 cursor tab_csr is
205 -- CLASSIFIED
206 -- TABLES (Normal) minus IOT Tables)
207 -- MINUS AQ TABLES
208 -- MINUS IOT OVERFLOW TABLES
209 SELECT /*+ rule */ d.owner,
210 d.table_name,
211 d.tablespace_name,
212 nvl(o.custom_tablespace_type, o.tablespace_type) object_class,
213 'table' parent,
214 'TABLE' object_type,
215 'TABLE' subobject_type,
216 'YES' classified,
217 nvl(d.partitioned,'NO') partitioned
218 FROM fnd_object_tablespaces o,
219 DBA_TABLES d
220 WHERE d.owner = o.oracle_username
221 AND o.oracle_username = p_app
222 AND d.owner = p_app
223 AND o.object_name = d.table_name
224 AND o.object_type = 'TABLE'
225 AND nvl(d.iot_type,'ZZZ') NOT IN ('IOT', 'IOT_OVERFLOW')
226 AND NVL(d.temporary, 'N') = 'N'
227 AND d.table_name NOT LIKE 'BIN$%'
228 AND NOT EXISTS ( select det.table_name
229 from dba_external_tables det
230 where det.owner = d.owner
231 and det.table_name = d.table_name)
232 UNION ALL
233 -- UNCLASSIFIED
234 -- TABLES (Normal) minus IOT Tables, MV, MV logs
235 -- MINUS AQ TABLES and Domain Indexes tables
236 -- MINUS IOT and IOT OVERFLOW TABLES
237 SELECT /*+ rule */ d.owner,
238 d.table_name,
239 d.tablespace_name,
240 fnd_ts_mig_util.l_unclass_tsp object_class,
241 'table' parent,
242 'TABLE' object_type,
243 'TABLE' subobject_type,
244 'NO' classified,
245 nvl(d.partitioned,'NO') partitioned
246 FROM dba_tables d
247 WHERE d.owner = p_app
248 AND NOT EXISTS
249 (SELECT object_name
250 FROM fnd_object_tablespaces o
251 WHERE o.oracle_username = p_app
252 and o.object_name = d.table_name)
253 AND NOT EXISTS
254 (SELECT table_name
255 FROM DBA_SNAPSHOTS s
256 where s.owner = p_app
257 and s.table_name = d.table_name)
258 AND NOT EXISTS
259 (SELECT log_table
260 FROM DBA_SNAPSHOT_LOGS L
261 where l.log_owner = p_app
262 and l.log_table = d.table_name)
263 AND nvl(d.iot_type,'ZZZ') NOT IN ('IOT', 'IOT_OVERFLOW')
264 AND NOT EXISTS
265 (SELECT queue_table
266 FROM dba_queue_tables dqt
267 WHERE dqt.owner = p_app
268 and dqt.queue_table = d.table_name)
269 AND NOT EXISTS ( select det.table_name
270 from dba_external_tables det
271 where det.owner = p_app
272 and det.table_name = d.table_name)
273 AND d.table_name NOT LIKE 'AQ$%'
274 AND d.table_name NOT LIKE 'DR$%'
275 AND NVL(d.temporary, 'N') = 'N'
276 AND d.table_name NOT LIKE 'BIN$%'
277 UNION ALL
278 -- MVIEW LOGS
279 SELECT /*+ rule */ distinct dsl.log_owner,
280 dsl.log_table table_name,
281 dt.tablespace_name,
282 fnd_ts_mig_util.l_def_mv_tsp object_class,
283 dt.table_name parent,
284 'TABLE' object_type,
285 'MV_LOG' subobject_type,
286 'NO' classified,
287 nvl(dt.partitioned,'NO') partitioned
288 FROM dba_tables dt,
289 dba_snapshot_logs dsl
290 WHERE dsl.log_owner = p_app
291 AND dsl.log_owner = dt.owner
292 AND dt.owner = p_app
293 AND dsl.log_table = dt.table_name
294 AND NVL(dt.temporary, 'N') = 'N'
295 AND dt.table_name NOT LIKE 'BIN$%'
296 UNION ALL
297 -- MVIEWS
298 SELECT /*+ rule */ d.owner,
299 d.name table_name,
300 dt.tablespace_name,
301 fnd_ts_mig_util.l_def_mv_tsp object_class,
302 dt.table_name parent,
303 'TABLE' object_type,
304 'MVIEW' subobject_type,
305 'NO' classified,
306 nvl(dt.partitioned,'NO') partitioned
307 FROM dba_snapshots d,
308 dba_tables dt
309 WHERE d.owner = p_app
310 AND d.owner = dt.owner
311 AND dt.owner = p_app
312 AND dt.table_name = d.table_name
313 AND NVL(dt.temporary, 'N') = 'N'
314 AND dt.table_name NOT LIKE 'BIN$%'
315 UNION ALL
316 -- IOT OVERFLOW
317 -- MINUS AQ TABLES
318 -- MINUS IOT START WITH 'DR$%' (Domain Index Tables IOT OVERFLOW)
319 SELECT /*+ rule */ dt.owner,
320 dt.table_name table_name,
321 dt.tablespace_name,
322 fnd_ts_mig_util.l_def_tab_tsp object_class,
323 dt.iot_name parent,
324 'TABLE' object_type,
325 'IOT' subobject_type,
326 'NO' classified,
327 nvl(dt.partitioned,'NO') partitioned
328 FROM dba_tables dt
329 WHERE dt.owner = p_app
330 AND NVL(dt.iot_type, 'ZZZ') = 'IOT_OVERFLOW'
331 AND NVL(dt.iot_name, 'ZZZ') NOT LIKE 'AQ$%'
332 AND NVL(dt.iot_name, 'ZZZ') NOT LIKE 'DR$%'
333 AND NVL(dt.temporary, 'N') = 'N'
334 AND dt.table_name NOT LIKE 'BIN$%'
335 UNION ALL
336 -- IOT OVERFLOW FOR AQ TABLES
337 SELECT /*+ rule */ dt.owner,
338 dt.table_name table_name,
339 dt.tablespace_name,
340 fnd_ts_mig_util.l_aq_tab_tsp object_class,
341 --fnd_ts_mig_util.l_def_tab_tsp object_class,
342 dt.iot_name parent,
343 'TABLE' object_type,
344 'AQ' subobject_type,
345 'NO' classified,
346 nvl(dt.partitioned,'NO') partitioned
347 FROM dba_tables dt
348 WHERE dt.owner = p_app
349 AND NVL(dt.iot_type, 'ZZZ') = 'IOT_OVERFLOW'
350 AND NVL(dt.iot_name, 'ZZZ') LIKE 'AQ$%'
351 AND NVL(dt.temporary, 'N') = 'N'
352 AND dt.table_name NOT LIKE 'BIN$%'
353 UNION ALL
354 -- IOT OVERFLOW for Domain Indexes IOTs
355 SELECT /*+ rule */ dt.owner,
356 dt.table_name table_name,
357 dt.tablespace_name,
358 fnd_ts_mig_util.l_def_ind_tsp object_class,
359 dt.iot_name parent,
360 'TABLE' object_type,
361 'DOMAIN' subobject_type,
362 'NO' classified,
363 nvl(dt.partitioned,'NO') partitioned
364 FROM dba_tables dt
365 WHERE dt.owner = p_app
366 AND NVL(dt.iot_type, 'ZZZ') = 'IOT_OVERFLOW'
367 AND NVL(dt.iot_name, 'ZZZ') LIKE 'DR$%'
368 AND NVL(dt.temporary, 'N') = 'N'
369 AND dt.table_name NOT LIKE 'BIN$%'
370 UNION ALL
371 -- LOBS in Classified Objects
372 select /*+ rule */ d.owner,
373 d.segment_name table_name,
374 dt.tablespace_name,
375 nvl(o.custom_tablespace_type, o.tablespace_type) object_class,
376 d.table_name parent,
377 'LOB' object_type,
378 'TABLE' subobject_type,
379 'YES' classified,
380 nvl(dt.partitioned,'NO') partitioned
381 from fnd_object_tablespaces o,
382 dba_lobs d,
383 dba_tables dt
384 where dt.owner = p_app
385 and dt.owner = o.oracle_username
386 and o.oracle_username = p_app
387 and o.object_name = d.table_name
388 and o.object_type = 'TABLE'
389 and d.owner = p_app
390 and d.owner = dt.owner
391 and d.table_name = dt.table_name
392 and NVL(dt.temporary, 'N') = 'N'
393 AND dt.table_name NOT LIKE 'BIN$%'
394 AND NOT EXISTS ( select det.table_name
395 from dba_external_tables det
396 where det.owner = dt.owner
397 and det.table_name = dt.table_name)
398 UNION ALL
399 -- LOBS in Unclassified Tables
400 -- (no IOT, MVs, AQs, Domain Indexes)
401 select /*+ rule */ d.owner,
402 d.segment_name table_name,
403 dt.tablespace_name,
404 fnd_ts_mig_util.l_unclass_tsp object_class,
405 d.table_name parent,
406 'LOB' object_type,
407 'TABLE' subobject_type,
408 'NO' classified,
409 nvl(dt.partitioned,'NO') partitioned
410 from dba_lobs d,
411 dba_tables dt
412 where d.owner = p_app
413 and d.owner = dt.owner
414 and dt.owner = p_app
415 and d.table_name = dt.table_name
416 AND NOT EXISTS
417 (SELECT object_name
418 FROM fnd_object_tablespaces o
419 WHERE o.oracle_username = p_app
420 and o.object_name = dt.table_name)
421 AND NOT EXISTS
422 (SELECT table_name
423 FROM dba_snapshots s
424 WHERE s.owner = p_app
425 and s.table_name = dt.table_name)
426 AND NOT EXISTS
427 (SELECT log_table
428 FROM dba_snapshot_logs l
429 where l.log_owner = p_app
430 and l.log_table = d.table_name)
431 and dt.iot_type IS NULL
432 AND NOT EXISTS
433 (SELECT queue_table
434 FROM dba_queue_tables dqt
435 WHERE dqt.owner = p_app
436 and dqt.queue_table = dt.table_name)
437 AND NOT EXISTS ( select det.table_name
438 from dba_external_tables det
439 where det.owner = p_app
440 and det.table_name = d.table_name)
441 AND dt.table_name NOT LIKE 'AQ$%'
442 AND dt.table_name NOT LIKE 'DR$%'
443 and NVL(dt.temporary, 'N') = 'N'
444 AND dt.table_name NOT LIKE 'BIN$%'
445 UNION ALL
446 -- LOBS in IOTs
447 -- Minus AQ and Domain Index
448 select /*+ rule */ d.owner,
449 d.segment_name table_name,
450 dt.tablespace_name,
451 fnd_ts_mig_util.l_def_tab_tsp object_class,
452 d.table_name parent,
453 'LOB' object_type,
454 'IOT' subobject_type,
455 'NO' classified,
456 nvl(dt.partitioned,'NO') partitioned
457 from dba_lobs d,
458 dba_tables dt
459 where d.owner = p_app
460 and d.owner = dt.owner
461 and dt.owner = p_app
462 and d.table_name = dt.table_name
463 and NVL(dt.iot_type, 'ZZZ') IN ('IOT', 'IOT_OVERFLOW')
464 AND NOT EXISTS
465 (SELECT queue_table
466 FROM dba_queue_tables dqt
467 WHERE dqt.owner = p_app
468 and dqt.queue_table = dt.table_name)
469 AND NOT EXISTS ( select det.table_name
470 from dba_external_tables det
471 where det.owner = p_app
472 and det.table_name = d.table_name)
473 AND dt.table_name NOT LIKE 'AQ$%'
474 AND dt.table_name NOT LIKE 'DR$%'
475 and NVL(dt.temporary, 'N') = 'N'
476 AND dt.table_name NOT LIKE 'BIN$%'
477 UNION ALL
478 -- LOBs in MVs
479 select /*+ rule */ d.owner,
480 d.segment_name table_name,
481 dt.tablespace_name,
482 fnd_ts_mig_util.l_def_mv_tsp object_class,
483 d.table_name parent,
484 'LOB' object_type,
485 'MVIEW' subobject_type,
486 'NO' classified,
487 nvl(dt.partitioned,'NO') partitioned
488 from dba_lobs d,
489 dba_tables dt
490 where d.owner = p_app
491 and d.owner = dt.owner
492 and dt.owner = p_app
493 and d.table_name = dt.table_name
494 AND EXISTS
495 (SELECT table_name
496 FROM dba_snapshots s
497 WHERE s.owner = p_app
498 and s.table_name = d.table_name)
499 and NVL(dt.temporary, 'N') = 'N'
500 AND dt.table_name NOT LIKE 'BIN$%'
501 UNION ALL
502 -- LOBS in AQs
503 select /*+ rule */ d.owner,
504 d.segment_name table_name,
505 dt.tablespace_name,
506 fnd_ts_mig_util.l_aq_tab_tsp object_class,
507 --fnd_ts_mig_util.l_def_tab_tsp object_class,
508 d.table_name parent,
509 'LOB' object_type,
510 'AQ' subobject_type,
511 'NO' classified,
512 nvl(dt.partitioned,'NO') partitioned
513 from dba_lobs d,
514 dba_tables dt
515 where d.owner = p_app
516 and d.owner = dt.owner
517 and dt.owner = p_app
518 and d.table_name = dt.table_name
519 AND (EXISTS
520 (SELECT queue_table
521 FROM dba_queue_tables dqt
522 WHERE dqt.owner = p_app
523 and dqt.queue_table = dt.table_name)
524 OR dt.table_name LIKE 'AQ$%')
525 and NVL(dt.temporary, 'N') = 'N'
526 AND dt.table_name NOT LIKE 'BIN$%'
527 UNION ALL
528 -- LOBS in Domain Index Objects
529 select /*+ rule */ d.owner,
530 d.segment_name table_name,
531 dt.tablespace_name,
532 fnd_ts_mig_util.l_def_ind_tsp object_class,
533 d.table_name parent,
534 'LOB' object_type,
535 'DOMAIN' subobject_type,
536 'NO' classified,
537 nvl(dt.partitioned,'NO') partitioned
538 from dba_lobs d,
539 dba_tables dt
540 where d.owner = p_app
541 and d.owner = dt.owner
542 and dt.owner = p_app
543 and d.table_name = dt.table_name
544 AND dt.table_name LIKE 'DR$%'
545 AND dt.table_name NOT LIKE 'BIN$%'
546
547 UNION ALL
548 -- Parent AQ tables
549 SELECT /*+ rule */ dt.owner,
550 dqt.queue_table table_name,
551 dt.tablespace_name,
552 fnd_ts_mig_util.l_aq_tab_tsp object_class,
553 --fnd_ts_mig_util.l_def_tab_tsp object_class,
554 'table' parent,
555 'TABLE' object_type,
556 'AQ' subobject_type,
557 'NO' classified,
558 nvl(dt.partitioned,'NO') partitioned
559 FROM dba_queue_tables dqt,
560 dba_tables dt
561 WHERE dt.owner = p_app
562 AND dt.owner = dqt.owner
563 AND dqt.owner = p_app
564 AND dt.table_name = dqt.queue_table
565 AND NVL(dt.temporary, 'N') = 'N'
566 AND dt.table_name NOT LIKE 'BIN$%'
567 UNION ALL
568 -- Child AQ tables not IOTs
569 SELECT /*+ rule */ dt.owner,
570 dt.table_name table_name,
571 dt.tablespace_name,
572 fnd_ts_mig_util.l_aq_tab_tsp object_class,
573 --fnd_ts_mig_util.l_def_tab_tsp object_class,
574 SUBSTR(dt.table_name, 5, LENGTH(dt.table_name)-6) parent,
575 'TABLE' object_type,
576 'AQ' subobject_type,
577 'NO' classified,
578 nvl(dt.partitioned,'NO') partitioned
579 FROM dba_tables dt
580 WHERE dt.owner = p_app
581 AND dt.table_name LIKE 'AQ$%'
582 AND dt.iot_type IS NULL
583 AND NVL(dt.temporary, 'N') = 'N'
584 AND dt.table_name NOT LIKE 'BIN$%'
585 UNION ALL
586 -- Domain Indexes tables not IOTs
587 SELECT /*+ rule */ dt.owner,
588 dt.table_name table_name,
589 dt.tablespace_name,
590 fnd_ts_mig_util.l_def_ind_tsp object_class,
591 SUBSTR(dt.table_name, 4, LENGTH(dt.table_name)-5) parent,
592 'TABLE' object_type,
593 'DOMAIN' subobject_type,
594 'NO' classified,
595 nvl(dt.partitioned,'NO') partitioned
596 FROM dba_tables dt
597 WHERE dt.owner = p_app
598 AND dt.table_name LIKE 'DR$%'
599 AND dt.iot_type IS NULL
600 AND dt.table_name NOT LIKE 'BIN$%'
601 AND NVL(dt.temporary, 'N') = 'N';
602
603 cursor get_tab_partition(p_table_name varchar2) is
604 select /*+ ALL_ROWS */ partition_name, tablespace_name
605 from dba_tab_partitions
606 where table_owner = p_app
607 and table_name = p_table_name;
608
609 cursor get_lob_partition(p_table_name varchar2, p_lob_name varchar2) is
610 select /*+ ALL_ROWS */ lob_partition_name, lob_indpart_name, tablespace_name
611 from dba_lob_partitions
612 where table_owner = p_app
613 and table_name = p_table_name
614 and lob_name = p_lob_name;
615
616 BEGIN
617
618 DELETE FROM fnd_ts_sizing
619 WHERE owner = p_app;
620 COMMIT;
621
622 OPEN tab_csr;
623 LOOP
624 FETCH tab_csr INTO cur_rec;
625 EXIT WHEN tab_csr%NOTFOUND;
626
627 cur_rec.object_class := trim(cur_rec.object_class);
628 cur_rec.parent := trim(cur_rec.parent);
629 cur_rec.object_type := trim(cur_rec.object_type);
630 cur_rec.subobject_type := trim(cur_rec.subobject_type);
631 cur_rec.classified := trim(cur_rec.classified);
632 cur_rec.partitioned := trim(cur_rec.partitioned);
633
634 if (cur_rec.partitioned = 'NO') then
635 -- non-partitioned tables and LOBS will be sized here.
636 ins_fnd_ts_sizing (
637 cur_rec,
638 p_uni_extent,
639 p_allocation_type,
640 p_creation_date);
641 elsif (cur_rec.object_type = 'TABLE') then
642 cur_rec.object_type := 'TABLE PARTITION';
643 FOR tab_part_rec IN get_tab_partition(cur_rec.table_name)
644 LOOP
645 cur_rec.tablespace_name := tab_part_rec.tablespace_name;
646 ins_fnd_ts_sizing (
647 cur_rec,
648 P_uni_extent,
649 p_allocation_type,
650 p_creation_date,
651 tab_part_rec.partition_name);
652 END LOOP;
653 elsif (cur_rec.object_type = 'LOB') then
654 cur_rec.object_type := 'LOB PARTITION';
655 FOR lob_part_rec IN get_lob_partition(cur_rec.parent, cur_rec.table_name)
656 LOOP
657 cur_rec.tablespace_name := lob_part_rec.tablespace_name;
658 ins_fnd_ts_sizing (
659 cur_rec,
660 p_uni_extent,
661 p_allocation_type,
662 p_creation_date,
663 lob_part_rec.lob_partition_name);
664 /*
665 cur_rec.object_type := 'INDEX PARTITION';
666 cur_rec.table_name := REPLACE(cur_rec.table_name, 'LOB', 'IL');
667 ins_fnd_ts_sizing (
668 cur_rec,
669 p_uni_extent,
670 p_allocation_type,
671 p_creation_date,
672 lob_part_rec.lob_indpart_name);
673 */
674 END LOOP;
675 end if;
676 COMMIT;
677 END LOOP;
678 CLOSE tab_csr;
679 END gen_tab_sizing;
680
681
682 PROCEDURE gen_ind_sizing (p_app IN VARCHAR2,
683 p_uni_extent IN NUMBER,
684 p_allocation_type IN VARCHAR2,
685 p_creation_date IN DATE)
686 IS
687 cur_rec rec_type;
688
689 cursor ind_csr is
690 -- Indexes on Classified Tables
691 select /*+ rule */ i.owner,
692 i.index_name table_name,
693 i.tablespace_name,
694 decode(nvl(o.custom_tablespace_type, o.tablespace_type), fnd_ts_mig_util.l_def_tab_tsp, fnd_ts_mig_util.l_def_ind_tsp, o.tablespace_type) object_class,
695 i.table_name parent,
696 'INDEX' object_type,
697 'INDEX' subobject_type,
698 'YES' classified,
699 nvl(i.partitioned,'NO') partitioned
700 from fnd_object_tablespaces o,
701 dba_indexes i
702 where i.table_owner = p_app
703 and o.oracle_username = i.table_owner
704 and o.object_name = i.table_name
705 and i.index_type not in ('DOMAIN','IOT - TOP','CLUSTER','LOB')
706 and NVL(i.temporary, 'N') = 'N'
707 and i.index_name NOT LIKE 'BIN$%'
708 UNION ALL
709 -- LOB Indexes on Classified Tables
710 select /*+ rule */ i.owner,
711 i.index_name table_name,
712 i.tablespace_name,
713 decode(nvl(o.custom_tablespace_type, o.tablespace_type), fnd_ts_mig_util.l_def_tab_tsp, fnd_ts_mig_util.l_def_tab_tsp, o.tablespace_type) object_class,
714 i.table_name parent,
715 'INDEX' object_type,
716 'INDEX' subobject_type,
717 'YES' classified,
718 nvl(i.partitioned,'NO') partitioned
719 from fnd_object_tablespaces o,
720 dba_indexes i
721 where i.table_owner = p_app
722 and o.oracle_username = i.table_owner
723 and o.object_name = i.table_name
724 and i.index_type ='LOB'
725 and NVL(i.temporary, 'N') = 'N'
726 and i.index_name NOT LIKE 'BIN$%'
727 UNION ALL
728 -- Indexes for Unclassified Tables w/o lob
729 -- (no IOTs, MVs, MV Logs, AQs, Domain Indexes)
730 SELECT /*+ rule */ i.owner,
731 i.index_name table_name,
732 i.tablespace_name,
733 fnd_ts_mig_util.l_unclass_ind_tsp object_class,
734 i.table_name parent,
735 'INDEX' object_type,
736 'INDEX' subobject_type,
737 'NO' classified,
738 nvl(i.partitioned,'NO') partitioned
739 FROM dba_indexes i,
740 dba_tables dt
741 WHERE i.table_owner = p_app
742 AND i.table_owner = dt.owner
743 AND i.table_name = dt.table_name
744 AND NVL(dt.iot_type, 'ZZZ') NOT IN ('IOT', 'IOT_OVERFLOW')
745 AND NVL(dt.temporary, 'N') = 'N'
746 AND NOT EXISTS
747 (SELECT object_name
748 FROM fnd_object_tablespaces o
749 WHERE o.oracle_username = p_app
750 and o.object_name = i.table_name)
751 AND NOT EXISTS
752 (SELECT table_name
753 FROM dba_snapshots s
754 WHERE s.owner = p_app
755 and s.table_name = i.table_name)
756 AND NOT EXISTS
757 (SELECT log_table
758 FROM dba_snapshot_logs s
759 WHERE s.log_owner = p_app
760 and s.log_table = i.table_name)
761 AND NOT EXISTS
762 (SELECT queue_table
763 FROM dba_queue_tables dqt
764 WHERE dqt.owner = p_app
765 and dqt.queue_table = i.table_name)
766 AND i.index_type not in ('DOMAIN', 'IOT - TOP', 'CLUSTER','LOB')
767 AND i.table_name NOT LIKE 'AQ$%'
768 AND i.table_name NOT LIKE 'DR$%'
769 AND NVL(i.temporary, 'N') = 'N'
770 and i.index_name NOT LIKE 'BIN$%'
771 UNION ALL
772 -- Indexes for Unclassified Tables with lob
773 -- (no IOTs, MVs, MV Logs, AQs, Domain Indexes)
774 SELECT /*+ rule */ i.owner,
775 i.index_name table_name,
776 i.tablespace_name,
777 fnd_ts_mig_util.l_unclass_tsp object_class,
778 i.table_name parent,
779 'INDEX' object_type,
780 'INDEX' subobject_type,
781 'NO' classified,
782 nvl(i.partitioned,'NO') partitioned
783 FROM dba_indexes i,
784 dba_tables dt
785 WHERE i.table_owner = p_app
786 AND i.table_owner = dt.owner
787 AND i.table_name = dt.table_name
788 AND NVL(dt.iot_type, 'ZZZ') NOT IN ('IOT', 'IOT_OVERFLOW')
789 AND NVL(dt.temporary, 'N') = 'N'
790 AND NOT EXISTS
791 (SELECT object_name
792 FROM fnd_object_tablespaces o
793 WHERE o.oracle_username = p_app
794 and o.object_name = i.table_name)
795 AND NOT EXISTS
796 (SELECT table_name
797 FROM dba_snapshots s
798 WHERE s.owner = p_app
799 and s.table_name = i.table_name)
800 AND NOT EXISTS
801 (SELECT log_table
802 FROM dba_snapshot_logs s
803 WHERE s.log_owner = p_app
804 and s.log_table = i.table_name)
805 AND NOT EXISTS
806 (SELECT queue_table
807 FROM dba_queue_tables dqt
808 WHERE dqt.owner = p_app
809 and dqt.queue_table = i.table_name)
810 AND i.index_type = 'LOB'
811 AND i.table_name NOT LIKE 'AQ$%'
812 AND i.table_name NOT LIKE 'DR$%'
813 AND NVL(i.temporary, 'N') = 'N'
814 AND i.index_name NOT LIKE 'BIN$%'
815
816 UNION ALL
817 -- IOT Tables (since IOT go to TRANSACTION data)
818 -- Minus Child AQ IOTs
819 -- Minus Domain Indexes IOTs
820 SELECT /*+ rule */ d.owner,
821 d.index_name table_name,
822 d.tablespace_name,
823 fnd_ts_mig_util.l_aq_tab_tsp object_class,
824 --fnd_ts_mig_util.l_def_tab_tsp object_class,
825 d.table_name parent,
826 'INDEX' object_type,
827 'IOT' subobject_type,
828 'NO' classified,
829 nvl(d.partitioned,'NO') partitioned
830 FROM dba_indexes d
831 WHERE d.table_owner = p_app
832 AND d.index_type = 'IOT - TOP'
833 AND d.table_name NOT LIKE 'AQ$%'
834 AND d.table_name NOT LIKE 'DR$%'
835 AND NVL(d.temporary, 'N') = 'N'
836 AND d.index_name NOT LIKE 'BIN$%'
837 UNION ALL
838 -- Indexes on IOTs
839 -- Minus Indexes on AQ IOTs and Domain Index IOTs
840 SELECT /*+ rule */ i.owner,
841 i.index_name table_name,
842 i.tablespace_name,
843 fnd_ts_mig_util.l_def_ind_tsp object_class,
844 i.table_name parent,
845 'INDEX' object_type,
846 'IOT' subobject_type,
847 'NO' classified,
848 nvl(i.partitioned,'NO') partitioned
849 FROM dba_indexes i,
850 dba_tables dt
851 WHERE i.table_owner = p_app
852 AND dt.owner = i.table_owner
853 AND dt.table_name = i.table_name
854 AND NVL(dt.iot_type, 'ZZZ') IN ('IOT', 'IOT_OVERFLOW')
855 AND NVL(dt.temporary, 'N') = 'N'
856 AND NOT EXISTS
857 (SELECT table_name
858 FROM dba_snapshots s
859 WHERE s.owner = p_app
860 AND s.table_name = i.table_name)
861 AND i.table_name NOT LIKE 'AQ$%'
862 AND i.table_name NOT LIKE 'DR$%'
863 AND i.index_type NOT IN ('DOMAIN', 'IOT - TOP', 'CLUSTER','LOB')
864 AND NVL(i.temporary, 'N') = 'N'
865 AND i.index_name NOT LIKE 'BIN$%'
866 UNION ALL
867 -- Indexes on IOTs witj lob
868 -- Minus Indexes on AQ IOTs and Domain Index IOTs
869 SELECT /*+ rule */ i.owner,
870 i.index_name table_name,
871 i.tablespace_name,
872 fnd_ts_mig_util.l_def_tab_tsp object_class,
873 i.table_name parent,
874 'INDEX' object_type,
875 'IOT' subobject_type,
876 'NO' classified,
877 nvl(i.partitioned,'NO') partitioned
878 FROM dba_indexes i,
879 dba_tables dt
880 WHERE i.table_owner = p_app
881 AND dt.owner = i.table_owner
882 AND dt.table_name = i.table_name
883 AND NVL(dt.iot_type, 'ZZZ') IN ('IOT', 'IOT_OVERFLOW')
884 AND NVL(dt.temporary, 'N') = 'N'
885 AND NOT EXISTS
886 (SELECT table_name
887 FROM dba_snapshots s
888 WHERE s.owner = p_app
889 AND s.table_name = i.table_name)
890 AND i.table_name NOT LIKE 'AQ$%'
891 AND i.table_name NOT LIKE 'DR$%'
892 AND i.index_type ='LOB'
893 AND NVL(i.temporary, 'N') = 'N'
894 AND i.index_name NOT LIKE 'BIN$%'
895 UNION ALL
896 -- Indexes on MVs
897 select /*+ rule */ i.owner,
898 i.index_name table_name,
899 i.tablespace_name,
900 fnd_ts_mig_util.l_def_mv_tsp object_class,
901 i.table_name parent,
902 'INDEX' object_type,
903 'MVIEW' subobject_type,
904 'NO' classified,
905 nvl(i.partitioned,'NO') partitioned
906 from dba_indexes i
907 where i.table_owner = p_app
908 AND EXISTS
909 (SELECT table_name
910 FROM dba_snapshots s
911 WHERE s.owner = p_app
912 and s.table_name = i.table_name)
913 AND i.index_type not in ('DOMAIN','IOT - TOP','CLUSTER','LOB')
914 AND NVL(i.temporary, 'N') = 'N'
915 AND i.index_name NOT LIKE 'BIN$%'
916 UNION ALL
917 -- Indexes on MVs
918 select /*+ rule */ i.owner,
919 i.index_name table_name,
920 i.tablespace_name,
921 fnd_ts_mig_util.l_def_mv_tsp object_class,
922 i.table_name parent,
923 'INDEX' object_type,
924 'MVIEW' subobject_type,
925 'NO' classified,
926 nvl(i.partitioned,'NO') partitioned
927 from dba_indexes i
928 where i.table_owner = p_app
929 AND EXISTS
930 (SELECT table_name
931 FROM dba_snapshots s
932 WHERE s.owner = p_app
933 and s.table_name = i.table_name)
934 AND i.index_type ='LOB'
935 AND NVL(i.temporary, 'N') = 'N'
936 AND i.index_name NOT LIKE 'BIN$%'
937
938 UNION ALL
939 -- Indexes on MV Logs
940 select /*+ rule */ i.owner,
941 i.index_name table_name,
942 i.tablespace_name,
943 fnd_ts_mig_util.l_def_mv_tsp object_class,
944 i.table_name parent,
945 'INDEX' object_type,
946 'MV_LOG' subobject_type,
947 'NO' classified,
948 nvl(i.partitioned,'NO') partitioned
949 from dba_indexes i
950 where i.table_owner = p_app
951 AND EXISTS
952 (SELECT table_name
953 FROM dba_snapshot_logs s
954 WHERE s.log_owner = p_app
955 and s.log_table = i.table_name)
956 AND I.index_type not in ('DOMAIN','IOT - TOP','CLUSTER','LOB')
957 AND NVL(i.temporary, 'N') = 'N'
958 AND i.index_name NOT LIKE 'BIN$%'
959 UNION ALL
960 -- Indexes on MV Logs with lobs
961 select /*+ rule */ i.owner,
962 i.index_name table_name,
963 i.tablespace_name,
964 fnd_ts_mig_util.l_def_mv_tsp object_class,
965 i.table_name parent,
966 'INDEX' object_type,
967 'MV_LOG' subobject_type,
968 'NO' classified,
969 nvl(i.partitioned,'NO') partitioned
970 from dba_indexes i
971 where i.table_owner = p_app
972 AND EXISTS
973 (SELECT table_name
974 FROM dba_snapshot_logs s
975 WHERE s.log_owner = p_app
976 and s.log_table = i.table_name)
977 AND I.index_type ='LOB'
978 AND NVL(i.temporary, 'N') = 'N'
979 AND i.index_name NOT LIKE 'BIN$%'
980 UNION ALL
981 -- Child AQ IOTs
982 SELECT /*+ rule */ d.owner,
983 d.index_name table_name,
984 d.tablespace_name,
985 fnd_ts_mig_util.l_aq_tab_tsp object_class,
986 -- fnd_ts_mig_util.l_def_tab_tsp object_class,
987 SUBSTR(d.table_name, 5, LENGTH(d.table_name)-6) parent,
988 'INDEX' object_type,
989 'AQ' subobject_type,
990 'NO' classified,
991 nvl(d.partitioned,'NO') partitioned
992 FROM dba_indexes d
993 WHERE d.table_owner = p_app
994 AND d.index_type = 'IOT - TOP'
995 AND d.table_name LIKE 'AQ$%'
996 AND NVL(d.temporary, 'N') = 'N'
997 AND d.index_name NOT LIKE 'BIN$%'
998 UNION ALL
999 -- Indexes on Parent and Child AQ tables (including indexes on child IOTs)
1000 SELECT /*+ rule */ di.owner,
1001 di.index_name table_name,
1002 di.tablespace_name,
1003 fnd_ts_mig_util.l_aq_tab_tsp object_class,
1004 --fnd_ts_mig_util.l_def_ind_tsp object_class,
1005 di.table_name parent,
1006 'INDEX' object_type,
1007 'AQ' subobject_type,
1008 'NO' classified,
1009 nvl(di.partitioned,'NO') partitioned
1010 FROM dba_indexes di
1011 WHERE di.table_owner = p_app
1012 AND (EXISTS
1013 (SELECT queue_table
1014 FROM dba_queue_tables dqt
1015 WHERE dqt.owner = p_app
1016 AND dqt.queue_table = di.table_name)
1017 OR di.table_name LIKE 'AQ$%')
1018 AND di.index_type not in ('DOMAIN','IOT - TOP','CLUSTER','LOB')
1019 AND NVL(di.temporary, 'N') = 'N'
1020 AND di.index_name NOT LIKE 'BIN$%'
1021 UNION ALL
1022 -- Indexes on Parent and Child AQ tables (including indexes on child IOTs with lob)
1023 SELECT /*+ rule */ di.owner,
1024 di.index_name table_name,
1025 di.tablespace_name,
1026 fnd_ts_mig_util.l_aq_tab_tsp object_class,
1027 --fnd_ts_mig_util.l_def_ind_tsp object_class,
1028 di.table_name parent,
1029 'INDEX' object_type,
1030 'AQ' subobject_type,
1031 'NO' classified,
1032 nvl(di.partitioned,'NO') partitioned
1033 FROM dba_indexes di
1034 WHERE di.table_owner = p_app
1035 AND (EXISTS
1036 (SELECT queue_table
1037 FROM dba_queue_tables dqt
1038 WHERE dqt.owner = p_app
1039 AND dqt.queue_table = di.table_name)
1040 OR di.table_name LIKE 'AQ$%')
1041 AND di.index_type ='LOB'
1042 AND NVL(di.temporary, 'N') = 'N'
1043 AND di.index_name NOT LIKE 'BIN$%'
1044
1045 UNION ALL
1046 -- Child Domain Indexes IOTs
1047 SELECT /*+ rule */ d.owner,
1048 d.index_name table_name,
1049 d.tablespace_name,
1050 fnd_ts_mig_util.l_def_ind_tsp object_class,
1051 SUBSTR(d.table_name, 4, LENGTH(d.table_name)-5) parent,
1052 'INDEX' object_type,
1053 'DOMAIN' subobject_type,
1054 'NO' classified,
1055 nvl(d.partitioned,'NO') partitioned
1056 FROM dba_indexes d
1057 WHERE d.table_owner = p_app
1058 AND d.index_type = 'IOT - TOP'
1059 AND d.table_name LIKE 'DR$%'
1060 AND NVL(d.temporary, 'N') = 'N'
1061 AND d.index_name NOT LIKE 'BIN$%'
1062 UNION ALL
1063 -- Indexes on Child Domain Indexes tables including IOTs
1064 SELECT /*+ rule */ di.owner,
1065 di.index_name table_name,
1066 di.tablespace_name,
1067 fnd_ts_mig_util.l_def_ind_tsp object_class,
1068 di.table_name parent,
1069 'INDEX' object_type,
1070 'DOMAIN' subobject_type,
1071 'NO' classified,
1072 nvl(di.partitioned,'NO') partitioned
1073 FROM dba_indexes di
1074 WHERE di.table_owner = p_app
1075 AND di.table_name LIKE 'DR$%'
1076 AND di.index_type not in ('DOMAIN','IOT - TOP','CLUSTER','LOB')
1077 AND NVL(di.temporary, 'N') = 'N'
1078 AND di.index_name NOT LIKE 'BIN$%'
1079
1080 UNION ALL
1081 -- Indexes on Child Domain Indexes tables including IOTs with lob
1082 SELECT /*+ rule */ di.owner,
1083 di.index_name table_name,
1084 di.tablespace_name,
1085 fnd_ts_mig_util.l_def_tab_tsp object_class,
1086 di.table_name parent,
1087 'INDEX' object_type,
1088 'DOMAIN' subobject_type,
1089 'NO' classified,
1090 nvl(di.partitioned,'NO') partitioned
1091 FROM dba_indexes di
1092 WHERE di.table_owner = p_app
1093 AND di.table_name LIKE 'DR$%'
1094 AND di.index_type ='LOB'
1095 AND di.index_name NOT LIKE 'BIN$%'
1096 AND NVL(di.temporary, 'N') = 'N';
1097
1098 cursor get_ind_partition(p_index_name varchar2) is
1099 select /*+ ALL_ROWS */ partition_name, tablespace_name
1100 from dba_ind_partitions
1101 where index_name = p_index_name
1102 and index_owner=p_app;
1103 BEGIN
1104 /*
1105 DELETE FROM fnd_ts_sizing
1106 WHERE owner = p_app
1107 AND object_type IN ('INDEX', 'INDEX PARTITION', 'LOBINDEX');
1108 COMMIT;
1109 */
1110
1111 OPEN ind_csr;
1112 LOOP
1113 FETCH ind_csr INTO cur_rec;
1114 EXIT WHEN ind_csr%NOTFOUND;
1115
1116 cur_rec.object_class := trim(cur_rec.object_class);
1117 cur_rec.parent := trim(cur_rec.parent);
1118 cur_rec.object_type := trim(cur_rec.object_type);
1119 cur_rec.subobject_type := trim(cur_rec.subobject_type);
1120 cur_rec.classified := trim(cur_rec.classified);
1121 cur_rec.partitioned := trim(cur_rec.partitioned);
1122
1123 if (cur_rec.partitioned = 'NO') then
1124 -- non-partitioned indexes will be sized here.
1125 ins_fnd_ts_sizing (
1126 cur_rec,
1127 p_uni_extent,
1128 p_allocation_type,
1129 p_creation_date);
1130 elsif (cur_rec.object_type = 'INDEX') then
1131 cur_rec.object_type := 'INDEX PARTITION';
1132 FOR ind_part_rec IN get_ind_partition(cur_rec.table_name)
1133 LOOP
1134 cur_rec.tablespace_name := ind_part_rec.tablespace_name;
1135 ins_fnd_ts_sizing (
1136 cur_rec,
1137 p_uni_extent,
1138 p_allocation_type,
1139 p_creation_date,
1140 ind_part_rec.partition_name);
1141 END LOOP;
1142 end if;
1143 COMMIT;
1144 END LOOP;
1145 CLOSE ind_csr;
1146 END gen_ind_sizing;
1147
1148 PROCEDURE gen_all_tab_sizing ( p_uni_extent IN NUMBER,
1149 p_allocation_type IN VARCHAR2,
1150 p_creation_date IN DATE)
1151 IS
1152 cur_rec rec_type;
1153
1154 CURSOR tab_csr IS
1155 -- CLASSIFIED TABLES (Normal) minus IOT Tables
1156 -- MINUS AQ TABLES
1157 -- MINUS IOT OVERFLOW TABLES
1158 SELECT /*+ rule */ dt.owner,
1159 dt.table_name,
1160 dt.tablespace_name,
1161 nvl(fot.custom_tablespace_type, fot.tablespace_type) object_class,
1162 'table' parent,
1163 'TABLE' object_type,
1164 'TABLE' subobject_type,
1165 'YES' classified,
1166 NVL(dt.partitioned,'NO') partitioned
1167 FROM fnd_object_tablespaces fot,
1168 dba_tables dt
1169 WHERE dt.owner IN (select oracle_username
1170 from fnd_oracle_userid
1171 where read_only_flag IN ('E','A','U','K','M'))
1172 AND dt.owner = fot.oracle_username
1173 AND fot.object_name = dt.table_name
1174 AND fot.object_type = 'TABLE'
1175 AND nvl(dt.iot_type,'ZZZ') NOT IN ('IOT', 'IOT_OVERFLOW')
1176 AND NVL(dt.temporary, 'N') = 'N'
1177 AND dt.table_name NOT LIKE 'BIN$%'
1178 AND NOT EXISTS ( select det.table_name
1179 from dba_external_tables det
1180 where det.owner = dt.owner
1181 and det.table_name = dt.table_name)
1182 UNION ALL
1183 -- UNCLASSIFIED
1184 -- TABLES (Normal) minus IOT Tables, MV, MV logs
1185 -- MINUS AQ TABLES and Domain Indexes tables
1186 -- MINUS IOT and IOT OVERFLOW TABLES
1187 SELECT /*+ rule */ dt.owner,
1188 dt.table_name,
1189 dt.tablespace_name,
1190 fnd_ts_mig_util.l_unclass_tsp object_class,
1191 'table' parent,
1192 'TABLE' object_type,
1193 'TABLE' subobject_type,
1194 'NO' classified,
1195 NVL(dt.partitioned,'NO') partitioned
1196 FROM dba_tables dt
1197 WHERE dt.owner IN (select oracle_username
1198 from fnd_oracle_userid
1199 where read_only_flag IN ('E','A','U','K','M'))
1200 AND NOT EXISTS
1201 (SELECT object_name
1202 FROM fnd_object_tablespaces o
1203 WHERE o.oracle_username = dt.owner
1204 AND o.object_name = dt.table_name)
1205 AND NOT EXISTS
1206 (SELECT table_name
1207 FROM dba_snapshots s
1208 WHERE s.owner = dt.owner
1209 AND s.table_name = dt.table_name)
1210 AND NOT EXISTS
1211 (SELECT log_table
1212 FROM dba_snapshot_logs l
1213 WHERE l.log_owner = dt.owner
1214 AND l.log_table = dt.table_name)
1215 AND NVL(dt.iot_type,'ZZZ') NOT IN ('IOT', 'IOT_OVERFLOW')
1216 AND NOT EXISTS
1217 (SELECT queue_table
1218 FROM dba_queue_tables dqt
1219 WHERE dqt.owner = dt.owner
1220 AND dqt.queue_table = dt.table_name)
1221 AND NOT EXISTS ( select det.table_name
1222 from dba_external_tables det
1223 where det.owner = dt.owner
1224 and det.table_name = dt.table_name)
1225 AND dt.table_name NOT LIKE 'AQ$%'
1226 AND dt.table_name NOT LIKE 'DR$%'
1227 AND dt.table_name NOT LIKE 'RUPD$%'
1228 AND dt.table_name NOT LIKE 'MDRT%$'
1229 AND NVL(dt.temporary, 'N') = 'N'
1230 AND dt.table_name NOT LIKE 'BIN$%'
1231 UNION ALL
1232 -- MVIEW LOGS
1233 SELECT /*+ rule */ distinct dsl.log_owner,
1234 dsl.log_table table_name,
1235 dt.tablespace_name,
1236 fnd_ts_mig_util.l_def_mv_tsp object_class,
1237 dt.table_name parent,
1238 'TABLE' object_type,
1239 'MV_LOG' subobject_type,
1240 'NO' classified,
1241 NVL(dt.partitioned,'NO') partitioned
1242 FROM dba_tables dt,
1243 dba_snapshot_logs dsl
1244 WHERE dsl.log_owner IN (select oracle_username
1245 from fnd_oracle_userid
1246 where read_only_flag IN ('E','A','U','K','M'))
1247 AND dsl.log_owner = dt.owner
1248 AND dsl.log_table = dt.table_name
1249 AND NVL(dt.temporary, 'N') = 'N'
1250 AND dt.table_name NOT LIKE 'BIN$%'
1251 UNION ALL
1252 -- MVIEWS
1253 SELECT /*+ rule */ ds.owner,
1254 ds.name table_name,
1255 dt.tablespace_name,
1256 fnd_ts_mig_util.l_def_mv_tsp object_class,
1257 dt.table_name parent,
1258 'TABLE' object_type,
1259 'MVIEW' subobject_type,
1260 'NO' classified,
1261 NVL(dt.partitioned,'NO') partitioned
1262 FROM dba_snapshots ds,
1263 dba_tables dt
1264 WHERE ds.owner IN (select oracle_username
1265 from fnd_oracle_userid
1266 where read_only_flag IN ('E','A','U','K','M'))
1267 AND ds.owner = dt.owner
1268 AND dt.table_name = ds.table_name
1269 AND NVL(dt.temporary, 'N') = 'N'
1270 AND dt.table_name NOT LIKE 'BIN$%'
1271 UNION ALL
1272 -- IOT OVERFLOW
1273 -- MINUS AQ TABLES
1274 -- MINUS IOT START WITH 'DR$%' (Domain Index Tables IOT OVERFLOW)
1275 SELECT /*+ rule */ dt.owner,
1276 dt.table_name table_name,
1277 dt.tablespace_name,
1278 fnd_ts_mig_util.l_def_tab_tsp object_class,
1279 dt.iot_name parent,
1280 'TABLE' object_type,
1281 'IOT' subobject_type,
1282 'NO' classified,
1283 NVL(dt.partitioned,'NO') partitioned
1284 FROM dba_tables dt
1285 WHERE dt.owner IN (select oracle_username
1286 from fnd_oracle_userid
1287 where read_only_flag IN ('E','A','U','K','M'))
1288 AND NVL(dt.iot_type, 'ZZZ') = 'IOT_OVERFLOW'
1289 AND NVL(dt.iot_name, 'ZZZ') NOT LIKE 'AQ$%'
1290 AND NVL(dt.iot_name, 'ZZZ') NOT LIKE 'DR$%'
1291 AND NVL(dt.iot_name, 'ZZZ') NOT LIKE 'RUPD$%'
1292 AND NVL(dt.iot_name, 'ZZZ') NOT LIKE 'MDRT%$'
1293 AND NVL(dt.temporary, 'N') = 'N'
1294 AND dt.table_name NOT LIKE 'BIN$%'
1295 UNION ALL
1296 -- IOT OVERFLOW FOR AQ TABLES
1297 SELECT /*+ rule */ dt.owner,
1298 dt.table_name table_name,
1299 dt.tablespace_name,
1300 fnd_ts_mig_util.l_aq_tab_tsp object_class,
1301 --fnd_ts_mig_util.l_def_tab_tsp object_class,
1302 dt.iot_name parent,
1303 'TABLE' object_type,
1304 'AQ' subobject_type,
1305 'NO' classified,
1306 nvl(dt.partitioned,'NO') partitioned
1307 FROM dba_tables dt
1308 WHERE dt.owner IN (select oracle_username
1309 from fnd_oracle_userid
1310 where read_only_flag IN ('E','A','U','K','M'))
1311 AND NVL(dt.iot_type, 'ZZZ') = 'IOT_OVERFLOW'
1312 AND NVL(dt.iot_name, 'ZZZ') LIKE 'AQ$%'
1313 AND NVL(dt.temporary, 'N') = 'N'
1314 AND dt.table_name NOT LIKE 'BIN$%'
1315 UNION ALL
1316 -- IOT OVERFLOW for Domain Indexes IOTs
1317 SELECT /*+ rule */ dt.owner,
1318 dt.table_name table_name,
1319 dt.tablespace_name,
1320 fnd_ts_mig_util.l_def_ind_tsp object_class,
1321 dt.iot_name parent,
1322 'TABLE' object_type,
1323 'DOMAIN' subobject_type,
1324 'NO' classified,
1325 nvl(dt.partitioned,'NO') partitioned
1326 FROM dba_tables dt
1327 WHERE dt.owner IN (select oracle_username
1328 from fnd_oracle_userid
1329 where read_only_flag IN ('E','A','U','K','M'))
1330 AND NVL(dt.iot_type, 'ZZZ') = 'IOT_OVERFLOW'
1331 AND (NVL(dt.iot_name, 'ZZZ') LIKE 'DR$%'
1332 OR NVL(dt.iot_name, 'ZZZ') LIKE 'MDRT%$')
1333 AND NVL(dt.temporary, 'N') = 'N'
1334 AND dt.table_name NOT LIKE 'BIN$%'
1335 UNION ALL
1336 -- LOBS in Classified Objects
1337 SELECT /*+ rule */ dl.owner,
1338 dl.segment_name table_name,
1339 dt.tablespace_name,
1340 nvl(o.custom_tablespace_type, o.tablespace_type) object_class,
1341 dl.table_name parent,
1342 'LOB' object_type,
1343 'TABLE' subobject_type,
1344 'YES' classified,
1345 nvl(dt.partitioned,'NO') partitioned
1346 FROM fnd_object_tablespaces o,
1347 dba_lobs dl,
1348 dba_tables dt
1349 WHERE dt.owner IN (select oracle_username
1350 from fnd_oracle_userid
1351 where read_only_flag IN ('E','A','U','K','M'))
1352 AND dt.owner = o.oracle_username
1353 AND o.object_name = dl.table_name
1354 AND o.object_type = 'TABLE'
1355 AND dl.owner = dt.owner
1356 AND dl.table_name = dt.table_name
1357 AND NVL(dt.temporary, 'N') = 'N'
1358 AND dt.table_name NOT LIKE 'BIN$%'
1359 AND NOT EXISTS ( select det.table_name
1360 from dba_external_tables det
1361 where det.owner = dt.owner
1362 and det.table_name = dt.table_name)
1363 UNION ALL
1364 -- LOBS in Unclassified Tables
1365 -- (no IOT, MVs, AQs, Domain Indexes)
1366 SELECT /*+ rule */ dl.owner,
1367 dl.segment_name table_name,
1368 dt.tablespace_name,
1369 fnd_ts_mig_util.l_unclass_tsp object_class,
1370 dl.table_name parent,
1371 'LOB' object_type,
1372 'TABLE' subobject_type,
1373 'NO' classified,
1374 nvl(dt.partitioned,'NO') partitioned
1375 FROM dba_lobs dl,
1376 dba_tables dt
1377 WHERE dt.owner IN (select oracle_username
1378 from fnd_oracle_userid
1379 where read_only_flag IN ('E','A','U','K','M'))
1380 AND dl.owner = dt.owner
1381 AND dl.table_name = dt.table_name
1382 AND NOT EXISTS
1383 (SELECT object_name
1384 FROM fnd_object_tablespaces o
1385 WHERE o.oracle_username = dt.owner
1386 and o.object_name = dt.table_name)
1387 AND NOT EXISTS
1388 (SELECT table_name
1389 FROM dba_snapshots s
1390 WHERE s.owner = dt.owner
1391 and s.table_name = dt.table_name)
1392 AND NOT EXISTS
1393 (SELECT log_table
1394 FROM dba_snapshot_logs l
1395 where l.log_owner = dt.owner
1396 and l.log_table = dt.table_name)
1397 and dt.iot_type IS NULL
1398 AND NOT EXISTS
1399 (SELECT queue_table
1400 FROM dba_queue_tables dqt
1401 WHERE dqt.owner = dt.owner
1402 and dqt.queue_table = dt.table_name)
1403 AND NOT EXISTS
1404 (select det.table_name
1405 from dba_external_tables det
1406 where det.owner = dt.owner
1407 and det.table_name = dt.table_name)
1408 AND dt.table_name NOT LIKE 'AQ$%'
1409 AND dt.table_name NOT LIKE 'DR$%'
1410 and NVL(dt.temporary, 'N') = 'N'
1411 AND dt.table_name NOT LIKE 'BIN$%'
1412 UNION ALL
1413 -- LOBS in IOTs
1414 -- Minus AQ and Domain Index
1415 SELECT /*+ rule */ dl.owner,
1416 dl.segment_name table_name,
1417 dt.tablespace_name,
1418 fnd_ts_mig_util.l_def_tab_tsp object_class,
1419 dl.table_name parent,
1420 'LOB' object_type,
1421 'IOT' subobject_type,
1422 'NO' classified,
1423 nvl(dt.partitioned,'NO') partitioned
1424 FROM dba_lobs dl,
1425 dba_tables dt
1426 WHERE dt.owner IN (select oracle_username
1427 from fnd_oracle_userid
1428 where read_only_flag IN ('E','A','U','K','M'))
1429 AND dl.owner = dt.owner
1430 AND dl.table_name = dt.table_name
1431 AND NVL(dt.iot_type, 'ZZZ') IN ('IOT', 'IOT_OVERFLOW')
1432 AND NOT EXISTS
1433 (SELECT queue_table
1434 FROM dba_queue_tables dqt
1435 WHERE dqt.owner = dt.owner
1436 and dqt.queue_table = dt.table_name)
1437 AND NOT EXISTS ( select det.table_name
1438 from dba_external_tables det
1439 where det.owner = dt.owner
1440 and det.table_name = dt.table_name)
1441 AND dt.table_name NOT LIKE 'AQ$%'
1442 AND dt.table_name NOT LIKE 'DR$%'
1443 and NVL(dt.temporary, 'N') = 'N'
1444 AND dt.table_name NOT LIKE 'BIN$%'
1445 UNION ALL
1446 -- LOBs in MVs
1447 SELECT /*+ rule */ dl.owner,
1448 dl.segment_name table_name,
1449 dt.tablespace_name,
1450 fnd_ts_mig_util.l_def_mv_tsp object_class,
1451 dl.table_name parent,
1452 'LOB' object_type,
1453 'MVIEW' subobject_type,
1454 'NO' classified,
1455 nvl(dt.partitioned,'NO') partitioned
1456 FROM dba_lobs dl,
1457 dba_tables dt
1458 WHERE dt.owner IN (select oracle_username
1459 from fnd_oracle_userid
1460 where read_only_flag IN ('E','A','U','K','M'))
1461 AND dl.owner = dt.owner
1462 AND dl.table_name = dt.table_name
1463 AND EXISTS
1464 (SELECT table_name
1465 FROM dba_snapshots s
1466 WHERE s.owner = dt.owner
1467 and s.table_name = dt.table_name)
1468 AND NVL(dt.temporary, 'N') = 'N'
1469 AND dt.table_name NOT LIKE 'BIN$%'
1470 UNION ALL
1471 -- LOBS in AQs
1472 SELECT /*+ rule */ dl.owner,
1473 dl.segment_name table_name,
1474 dt.tablespace_name,
1475 fnd_ts_mig_util.l_aq_tab_tsp object_class,
1476 --fnd_ts_mig_util.l_def_tab_tsp object_class,
1477 dl.table_name parent,
1478 'LOB' object_type,
1479 'AQ' subobject_type,
1480 'NO' classified,
1481 nvl(dt.partitioned,'NO') partitioned
1482 FROM dba_lobs dl,
1483 dba_tables dt
1484 WHERE dt.owner IN (select oracle_username
1485 from fnd_oracle_userid
1486 where read_only_flag IN ('E','A','U','K','M'))
1487 AND dl.owner = dt.owner
1488 AND dl.table_name = dt.table_name
1489 AND (EXISTS
1490 (SELECT queue_table
1491 FROM dba_queue_tables dqt
1492 WHERE dqt.owner = dt.owner
1493 and dqt.queue_table = dt.table_name)
1494 OR dt.table_name LIKE 'AQ$%')
1495 AND NVL(dt.temporary, 'N') = 'N'
1496 AND dt.table_name NOT LIKE 'BIN$%'
1497 UNION ALL
1498 -- LOBS in Domain Index Objects
1499 SELECT /*+ rule */ dl.owner,
1500 dl.segment_name table_name,
1501 dt.tablespace_name,
1502 fnd_ts_mig_util.l_def_ind_tsp object_class,
1503 dl.table_name parent,
1504 'LOB' object_type,
1505 'DOMAIN' subobject_type,
1506 'NO' classified,
1507 nvl(dt.partitioned,'NO') partitioned
1508 from dba_lobs dl,
1509 dba_tables dt
1510 WHERE dt.owner IN (select oracle_username
1511 from fnd_oracle_userid
1512 where read_only_flag IN ('E','A','U','K','M'))
1513 and dl.owner = dt.owner
1514 and dl.table_name = dt.table_name
1515 AND dt.table_name LIKE 'DR$%'
1516 and NVL(dt.temporary, 'N') = 'N'
1517 AND dt.table_name NOT LIKE 'BIN$%'
1518 UNION ALL
1519 -- Parent AQ tables
1520 SELECT /*+ rule */ dt.owner,
1521 dqt.queue_table table_name,
1522 dt.tablespace_name,
1523 fnd_ts_mig_util.l_aq_tab_tsp object_class,
1524 --fnd_ts_mig_util.l_def_tab_tsp object_class,
1525 'table' parent,
1526 'TABLE' object_type,
1527 'AQ' subobject_type,
1528 'NO' classified,
1529 NVL(dt.partitioned,'NO') partitioned
1530 FROM dba_queue_tables dqt,
1531 dba_tables dt
1532 WHERE dt.owner IN (select oracle_username
1533 from fnd_oracle_userid
1534 where read_only_flag IN ('E','A','U','K','M'))
1535 AND dt.owner = dqt.owner
1536 AND dt.table_name = dqt.queue_table
1537 AND NVL(dt.temporary, 'N') = 'N'
1538 AND dt.table_name NOT LIKE 'BIN$%'
1539 UNION ALL
1540 -- Child AQ tables not IOTs
1541 SELECT /*+ rule */ dt.owner,
1542 dt.table_name table_name,
1543 dt.tablespace_name,
1544 fnd_ts_mig_util.l_aq_tab_tsp object_class,
1545 --fnd_ts_mig_util.l_def_tab_tsp object_class,
1546 SUBSTR(dt.table_name, 5, LENGTH(dt.table_name)-6) parent,
1547 'TABLE' object_type,
1548 'AQ' subobject_type,
1549 'NO' classified,
1550 NVL(dt.partitioned,'NO') partitioned
1551 FROM dba_tables dt
1552 WHERE dt.owner IN (select oracle_username
1553 from fnd_oracle_userid
1554 where read_only_flag IN ('E','A','U','K','M'))
1555 AND dt.table_name LIKE 'AQ$%'
1556 AND dt.iot_type IS NULL
1557 AND NVL(dt.temporary, 'N') = 'N'
1558 UNION ALL
1559 -- Domain Indexes tables not IOTs
1560 SELECT /*+ rule */ dt.owner owner,
1561 dt.table_name table_name,
1562 dt.tablespace_name,
1563 fnd_ts_mig_util.l_def_ind_tsp object_class,
1564 SUBSTR(dt.table_name, 4, LENGTH(dt.table_name)-5) parent,
1565 'TABLE' object_type,
1566 'DOMAIN' subobject_type,
1567 'NO' classified,
1568 nvl(dt.partitioned,'NO') partitioned
1569 FROM dba_tables dt
1570 WHERE dt.owner IN (select oracle_username
1571 from fnd_oracle_userid
1572 where read_only_flag IN ('E','A','U','K','M'))
1573 AND dt.table_name LIKE 'DR$%'
1574 AND dt.iot_type IS NULL
1575 AND NVL(dt.temporary, 'N') = 'N';
1576
1577 CURSOR get_tab_partition(p_owner VARCHAR2, p_table_name VARCHAR2) IS
1578 select /*+ ALL_ROWS */ partition_name, tablespace_name
1579 from dba_tab_partitions
1580 where table_name = p_table_name
1581 and table_owner = p_owner;
1582
1583 cursor get_lob_partition(p_owner varchar2, p_table_name varchar2, p_lob_name varchar2) is
1584 select /*+ ALL_ROWS */ lob_partition_name, lob_indpart_name, tablespace_name
1585 from dba_lob_partitions
1586 where table_owner = p_owner
1587 and table_name = p_table_name
1588 and lob_name = p_lob_name;
1589
1590 BEGIN
1591
1592 DELETE FROM fnd_ts_sizing;
1593 COMMIT;
1594
1595 OPEN tab_csr;
1596 LOOP
1597 FETCH tab_csr INTO cur_rec;
1598 EXIT WHEN tab_csr%NOTFOUND;
1599
1600 cur_rec.object_class := trim(cur_rec.object_class);
1601 cur_rec.parent := trim(cur_rec.parent);
1602 cur_rec.object_type := trim(cur_rec.object_type);
1603 cur_rec.subobject_type := trim(cur_rec.subobject_type);
1604 cur_rec.classified := trim(cur_rec.classified);
1605 cur_rec.partitioned := trim(cur_rec.partitioned);
1606
1607 if (cur_rec.partitioned = 'NO') then
1608 -- non-partitioned tables and LOBS will be sized here.
1609 ins_fnd_ts_sizing (
1610 cur_rec,
1611 p_uni_extent,
1612 p_allocation_type,
1613 p_creation_date);
1614 elsif (cur_rec.object_type = 'TABLE') then
1615 cur_rec.object_type := 'TABLE PARTITION';
1616 FOR tab_part_rec IN get_tab_partition (cur_rec.owner, cur_rec.table_name)
1617 LOOP
1618 cur_rec.tablespace_name := tab_part_rec.tablespace_name;
1619 ins_fnd_ts_sizing (
1620 cur_rec,
1621 p_uni_extent,
1622 p_allocation_type,
1623 p_creation_date,
1624 tab_part_rec.partition_name);
1625 END LOOP;
1626 elsif (cur_rec.object_type = 'LOB') then
1627 cur_rec.object_type := 'LOB PARTITION';
1628 FOR lob_part_rec IN get_lob_partition(cur_rec.owner, cur_rec.parent, cur_rec.table_name)
1629 LOOP
1630 cur_rec.tablespace_name := lob_part_rec.tablespace_name;
1631 ins_fnd_ts_sizing (
1632 cur_rec,
1633 P_uni_extent,
1634 p_allocation_type,
1635 p_creation_date,
1636 lob_part_rec.lob_partition_name);
1637 /*
1638 cur_rec.object_type := 'INDEX PARTITION';
1639 cur_rec.table_name := REPLACE(cur_rec.table_name, 'LOB', 'IL');
1640 ins_fnd_ts_sizing (
1641 cur_rec,
1642 p_uni_extent,
1643 p_allocation_type,
1644 p_creation_date,
1645 lob_part_rec.lob_indpart_name);
1646 */
1647 END LOOP;
1648 end if;
1649 COMMIT;
1650 END LOOP;
1651 CLOSE tab_csr;
1652 END gen_all_tab_sizing;
1653
1654 PROCEDURE gen_all_ind_sizing (p_uni_extent IN NUMBER,
1655 p_allocation_type IN VARCHAR2,
1656 p_creation_date IN DATE)
1657 IS
1658 cur_rec rec_type;
1659
1660 cursor ind_csr is
1661 -- Indexes on Classified Tables
1662 SELECT /*+ rule */ i.owner,
1663 i.index_name table_name,
1664 i.tablespace_name,
1665 decode(nvl(o.custom_tablespace_type, o.tablespace_type), fnd_ts_mig_util.l_def_tab_tsp, fnd_ts_mig_util.l_def_ind_tsp, o.tablespace_type) object_class,
1666 i.table_name parent,
1667 'INDEX' object_type,
1668 'INDEX' subobject_type,
1669 'YES' classified,
1670 nvl(i.partitioned,'NO') partitioned
1671 FROM fnd_object_tablespaces o,
1672 dba_indexes i
1673 WHERE i.table_owner IN (select oracle_username
1674 from fnd_oracle_userid
1675 where read_only_flag IN ('E','A','U','K','M'))
1676 AND o.oracle_username = i.table_owner
1677 AND o.object_name = i.table_name
1678 AND o.object_type = 'TABLE'
1679 AND i.index_type not in ('DOMAIN','IOT - TOP','CLUSTER','LOB')
1680 AND NVL(i.temporary, 'N') = 'N'
1681 AND i.index_name NOT LIKE 'BIN$%'
1682 UNION ALL
1683 -- Indexes on Classified Tables with lob
1684 SELECT /*+ rule */ i.owner,
1685 i.index_name table_name,
1686 i.tablespace_name,
1687 decode(nvl(o.custom_tablespace_type, o.tablespace_type), fnd_ts_mig_util.l_def_tab_tsp, fnd_ts_mig_util.l_def_tab_tsp, o.tablespace_type) object_class,
1688 i.table_name parent,
1689 'INDEX' object_type,
1690 'INDEX' subobject_type,
1691 'YES' classified,
1692 nvl(i.partitioned,'NO') partitioned
1693 FROM fnd_object_tablespaces o,
1694 dba_indexes i
1695 WHERE i.table_owner IN (select oracle_username
1696 from fnd_oracle_userid
1697 where read_only_flag IN ('E','A','U','K','M'))
1698 AND o.oracle_username = i.table_owner
1699 AND o.object_name = i.table_name
1700 AND o.object_type = 'TABLE'
1701 AND i.index_type ='LOB'
1702 AND NVL(i.temporary, 'N') = 'N'
1703 AND i.index_name NOT LIKE 'BIN$%'
1704 UNION ALL
1705 -- Indexes for Unclassified Tables
1706 -- (no IOTs, MVs, MV Logs, AQs, Domain Indexes)
1707 SELECT /*+ rule */ i.owner,
1708 i.index_name table_name,
1709 i.tablespace_name,
1710 fnd_ts_mig_util.l_unclass_ind_tsp object_class,
1711 i.table_name parent,
1712 'INDEX' object_type,
1713 'INDEX' subobject_type,
1714 'NO' classified,
1715 nvl(i.partitioned,'NO') partitioned
1716 FROM dba_indexes i,
1717 dba_tables dt
1718 WHERE i.table_owner IN (select oracle_username
1719 from fnd_oracle_userid
1720 where read_only_flag IN ('E','A','U','K','M'))
1721 AND i.table_owner = dt.owner
1722 AND i.table_name = dt.table_name
1723 AND NVL(dt.iot_type, 'ZZZ') NOT IN ('IOT', 'IOT_OVERFLOW')
1724 AND NVL(dt.temporary, 'N') = 'N'
1725 AND NOT EXISTS
1726 (SELECT object_name
1727 FROM fnd_object_tablespaces o
1728 WHERE o.oracle_username = i.table_owner
1729 and o.object_name = i.table_name)
1730 AND NOT EXISTS
1731 (SELECT table_name
1732 FROM dba_snapshots s
1733 WHERE s.owner = i.table_owner
1734 and s.table_name = i.table_name)
1735 AND NOT EXISTS
1736 (SELECT log_table
1737 FROM dba_snapshot_logs s
1738 WHERE s.log_owner = i.table_owner
1739 and s.log_table = i.table_name)
1740 AND NOT EXISTS
1741 (SELECT queue_table
1742 FROM dba_queue_tables dqt
1743 WHERE dqt.owner = i.table_owner
1744 and dqt.queue_table = i.table_name)
1745 AND i.index_type not in ('DOMAIN', 'IOT - TOP', 'CLUSTER','LOB')
1746 AND i.table_name NOT LIKE 'AQ$%'
1747 AND i.table_name NOT LIKE 'DR$%'
1748 AND NVL(i.temporary, 'N') = 'N'
1749 AND i.index_name NOT LIKE 'BIN$%'
1750
1751 UNION ALL
1752 -- Indexes for Unclassified Tables with lob
1753 -- (no IOTs, MVs, MV Logs, AQs, Domain Indexes)
1754 SELECT /*+ rule */ i.owner,
1755 i.index_name table_name,
1756 i.tablespace_name,
1757 fnd_ts_mig_util.l_unclass_tsp object_class,
1758 i.table_name parent,
1759 'INDEX' object_type,
1760 'INDEX' subobject_type,
1761 'NO' classified,
1762 nvl(i.partitioned,'NO') partitioned
1763 FROM dba_indexes i,
1764 dba_tables dt
1765 WHERE i.table_owner IN (select oracle_username
1766 from fnd_oracle_userid
1767 where read_only_flag IN ('E','A','U','K','M'))
1768 AND i.table_owner = dt.owner
1769 AND i.table_name = dt.table_name
1770 AND NVL(dt.iot_type, 'ZZZ') NOT IN ('IOT', 'IOT_OVERFLOW')
1771 AND NVL(dt.temporary, 'N') = 'N'
1772 AND NOT EXISTS
1773 (SELECT object_name
1774 FROM fnd_object_tablespaces o
1775 WHERE o.oracle_username = i.table_owner
1776 and o.object_name = i.table_name)
1777 AND NOT EXISTS
1778 (SELECT table_name
1779 FROM dba_snapshots s
1780 WHERE s.owner = i.table_owner
1781 and s.table_name = i.table_name)
1782 AND NOT EXISTS
1783 (SELECT log_table
1784 FROM dba_snapshot_logs s
1785 WHERE s.log_owner = i.table_owner
1786 and s.log_table = i.table_name)
1787 AND NOT EXISTS
1788 (SELECT queue_table
1789 FROM dba_queue_tables dqt
1790 WHERE dqt.owner = i.table_owner
1791 and dqt.queue_table = i.table_name)
1792 AND i.index_type ='LOB'
1793 AND i.table_name NOT LIKE 'AQ$%'
1794 AND i.table_name NOT LIKE 'DR$%'
1795 AND NVL(i.temporary, 'N') = 'N'
1796 AND i.index_name NOT LIKE 'BIN$%'
1797 UNION ALL
1798 -- IOT Tables (since IOT go to TRANSACTION data)
1799 -- Minus Child AQ IOTs
1800 -- Minus Domain Indexes IOTs
1801 SELECT /*+ rule */ d.owner,
1802 d.index_name table_name,
1803 d.tablespace_name,
1804 fnd_ts_mig_util.l_def_tab_tsp object_class,
1805 d.table_name parent,
1806 'INDEX' object_type,
1807 'IOT' subobject_type,
1808 'NO' classified,
1809 nvl(d.partitioned,'NO') partitioned
1810 FROM dba_indexes d
1811 WHERE d.table_owner IN (select oracle_username
1812 from fnd_oracle_userid
1813 where read_only_flag IN ('E','A','U','K','M'))
1814 AND d.index_type = 'IOT - TOP'
1815 AND d.table_name NOT LIKE 'AQ$%'
1816 AND d.table_name NOT LIKE 'DR$%'
1817 AND NVL(d.temporary, 'N') = 'N'
1818 AND d.index_name NOT LIKE 'BIN$%'
1819 UNION ALL
1820 -- Indexes on IOTs
1821 -- Minus Indexes on AQ IOTs and Domain Index IOTs
1822 SELECT /*+ rule */ i.owner,
1823 i.index_name table_name,
1824 i.tablespace_name,
1825 fnd_ts_mig_util.l_def_ind_tsp object_class,
1826 i.table_name parent,
1827 'INDEX' object_type,
1828 'IOT' subobject_type,
1829 'NO' classified,
1830 nvl(i.partitioned,'NO') partitioned
1831 FROM dba_indexes i,
1832 dba_tables dt
1833 WHERE i.table_owner IN (select oracle_username
1834 from fnd_oracle_userid
1835 where read_only_flag IN ('E','A','U','K','M'))
1836 AND i.table_owner = dt.owner
1837 AND i.table_name = dt.table_name
1838 AND NVL(dt.iot_type, 'ZZZ') IN ('IOT', 'IOT_OVERFLOW')
1839 AND NVL(dt.temporary, 'N') = 'N'
1840 AND NOT EXISTS
1841 (SELECT table_name
1842 FROM dba_snapshots s
1843 WHERE s.owner = i.table_owner
1844 and s.table_name = i.table_name)
1845 AND i.table_name NOT LIKE 'AQ$%'
1846 AND i.table_name NOT LIKE 'DR$%'
1847 AND i.index_type NOT IN ('DOMAIN', 'IOT - TOP', 'CLUSTER','LOB')
1848 AND NVL(i.temporary, 'N') = 'N'
1849 AND i.index_name NOT LIKE 'BIN$%'
1850
1851 UNION ALL
1852 -- Indexes on IOTs witj lob
1853 -- Minus Indexes on AQ IOTs and Domain Index IOTs
1854 SELECT /*+ rule */ i.owner,
1855 i.index_name table_name,
1856 i.tablespace_name,
1857 fnd_ts_mig_util.l_def_tab_tsp object_class,
1858 i.table_name parent,
1859 'INDEX' object_type,
1860 'IOT' subobject_type,
1861 'NO' classified,
1862 nvl(i.partitioned,'NO') partitioned
1863 FROM dba_indexes i,
1864 dba_tables dt
1865 WHERE i.table_owner IN (select oracle_username
1866 from fnd_oracle_userid
1867 where read_only_flag IN ('E','A','U','K','M'))
1868 AND i.table_owner = dt.owner
1869 AND i.table_name = dt.table_name
1870 AND NVL(dt.iot_type, 'ZZZ') IN ('IOT', 'IOT_OVERFLOW')
1871 AND NVL(dt.temporary, 'N') = 'N'
1872 AND NOT EXISTS
1873 (SELECT table_name
1874 FROM dba_snapshots s
1875 WHERE s.owner = i.table_owner
1876 and s.table_name = i.table_name)
1877 AND i.table_name NOT LIKE 'AQ$%'
1878 AND i.table_name NOT LIKE 'DR$%'
1879 AND i.index_type ='LOB'
1880 AND NVL(i.temporary, 'N') = 'N'
1881 AND i.index_name NOT LIKE 'BIN$%'
1882 UNION ALL
1883 -- Indexes on MVs
1884 select /*+ rule */ i.owner,
1885 i.index_name table_name,
1886 i.tablespace_name,
1887 fnd_ts_mig_util.l_def_mv_tsp object_class,
1888 i.table_name parent,
1889 'INDEX' object_type,
1890 'MVIEW' subobject_type,
1891 'NO' classified,
1892 nvl(i.partitioned,'NO') partitioned
1893 from dba_indexes i
1894 WHERE i.table_owner IN (select oracle_username
1895 from fnd_oracle_userid
1896 where read_only_flag IN ('E','A','U','K','M'))
1897 AND EXISTS
1898 (SELECT table_name
1899 FROM dba_snapshots s
1900 WHERE s.owner = i.table_owner
1901 and s.table_name = i.table_name)
1902 AND i.index_type not in ('DOMAIN','IOT - TOP','CLUSTER','LOB')
1903 AND NVL(i.temporary, 'N') = 'N'
1904 AND i.index_name NOT LIKE 'BIN$%'
1905 UNION ALL
1906 -- Indexes on MVs with lob
1907 select /*+ rule */ i.owner,
1908 i.index_name table_name,
1909 i.tablespace_name,
1910 fnd_ts_mig_util.l_def_mv_tsp object_class,
1911 i.table_name parent,
1912 'INDEX' object_type,
1913 'MVIEW' subobject_type,
1914 'NO' classified,
1915 nvl(i.partitioned,'NO') partitioned
1916 from dba_indexes i
1917 WHERE i.table_owner IN (select oracle_username
1918 from fnd_oracle_userid
1919 where read_only_flag IN ('E','A','U','K','M'))
1920 AND EXISTS
1921 (SELECT table_name
1922 FROM dba_snapshots s
1923 WHERE s.owner = i.table_owner
1924 and s.table_name = i.table_name)
1925 AND i.index_type = 'LOB'
1926 AND NVL(i.temporary, 'N') = 'N'
1927 AND i.index_name NOT LIKE 'BIN$%'
1928
1929 UNION ALL
1930 -- Indexes on MV Logs
1931 select /*+ rule */ i.owner,
1932 i.index_name table_name,
1933 i.tablespace_name,
1934 fnd_ts_mig_util.l_def_mv_tsp object_class,
1935 i.table_name parent,
1936 'INDEX' object_type,
1937 'MV_LOG' subobject_type,
1938 'NO' classified,
1939 nvl(i.partitioned,'NO') partitioned
1940 from dba_indexes i
1941 WHERE i.table_owner IN (select oracle_username
1942 from fnd_oracle_userid
1943 where read_only_flag IN ('E','A','U','K','M'))
1944 AND EXISTS
1945 (SELECT table_name
1946 FROM dba_snapshot_logs s
1947 WHERE s.log_owner = i.table_owner
1948 and s.log_table = i.table_name)
1949 AND i.index_type not in ('DOMAIN','IOT - TOP','CLUSTER','LOB')
1950 AND NVL(i.temporary, 'N') = 'N'
1951 AND i.index_name NOT LIKE 'BIN$%'
1952
1953 UNION ALL
1954 -- Indexes on MV Logs with lob
1955 select /*+ rule */ i.owner,
1956 i.index_name table_name,
1957 i.tablespace_name,
1958 fnd_ts_mig_util.l_def_mv_tsp object_class,
1959 i.table_name parent,
1960 'INDEX' object_type,
1961 'MV_LOG' subobject_type,
1962 'NO' classified,
1963 nvl(i.partitioned,'NO') partitioned
1964 from dba_indexes i
1965 WHERE i.table_owner IN (select oracle_username
1966 from fnd_oracle_userid
1967 where read_only_flag IN ('E','A','U','K','M'))
1968 AND EXISTS
1969 (SELECT table_name
1970 FROM dba_snapshot_logs s
1971 WHERE s.log_owner = i.table_owner
1972 and s.log_table = i.table_name)
1973 AND i.index_type ='LOB'
1974 AND NVL(i.temporary, 'N') = 'N'
1975 AND i.index_name NOT LIKE 'BIN$%'
1976 UNION ALL
1977 -- Child AQ IOTs
1978 SELECT /*+ rule */ d.owner,
1979 d.index_name table_name,
1980 d.tablespace_name,
1981 fnd_ts_mig_util.l_aq_tab_tsp object_class,
1982 -- fnd_ts_mig_util.l_def_tab_tsp object_class,
1983 SUBSTR(d.table_name, 5, LENGTH(d.table_name)-6) parent,
1984 'INDEX' object_type,
1985 'AQ' subobject_type,
1986 'NO' classified,
1987 nvl(d.partitioned,'NO') partitioned
1988 FROM dba_indexes d
1989 WHERE d.table_owner IN (select oracle_username
1990 from fnd_oracle_userid
1991 where read_only_flag IN ('E','A','U','K','M'))
1992 AND d.index_type = 'IOT - TOP'
1993 AND d.table_name LIKE 'AQ$%'
1994 AND NVL(d.temporary, 'N') = 'N'
1995 UNION ALL
1996 -- Indexes on Parent and Child AQ tables (including indexes on child IOTs)
1997 SELECT /*+ rule */ di.owner,
1998 di.index_name table_name,
1999 di.tablespace_name,
2000 fnd_ts_mig_util.l_aq_tab_tsp object_class,
2001 --fnd_ts_mig_util.l_def_ind_tsp object_class,
2002 di.table_name parent,
2003 'INDEX' object_type,
2004 'AQ' subobject_type,
2005 'NO' classified,
2006 nvl(di.partitioned,'NO') partitioned
2007 FROM dba_indexes di
2008 WHERE di.table_owner IN (select oracle_username
2009 from fnd_oracle_userid
2010 where read_only_flag IN ('E','A','U','K','M'))
2011 AND (EXISTS
2012 (SELECT queue_table
2013 FROM dba_queue_tables dqt
2014 WHERE dqt.owner = di.table_owner
2015 AND dqt.queue_table = di.table_name)
2016 OR di.table_name LIKE 'AQ$%')
2017 AND di.index_type not in ('DOMAIN','IOT - TOP','CLUSTER','LOB')
2018 AND NVL(di.temporary, 'N') = 'N'
2019 AND di.index_name NOT LIKE 'BIN$%'
2020
2021 UNION ALL
2022 -- Indexes on Parent and Child AQ tables (including indexes on child IOTs with lob)
2023 SELECT /*+ rule */ di.owner,
2024 di.index_name table_name,
2025 di.tablespace_name,
2026 fnd_ts_mig_util.l_aq_tab_tsp object_class,
2027 --fnd_ts_mig_util.l_def_ind_tsp object_class,
2028 di.table_name parent,
2029 'INDEX' object_type,
2030 'AQ' subobject_type,
2031 'NO' classified,
2032 nvl(di.partitioned,'NO') partitioned
2033 FROM dba_indexes di
2034 WHERE di.table_owner IN (select oracle_username
2035 from fnd_oracle_userid
2036 where read_only_flag IN ('E','A','U','K','M'))
2037 AND (EXISTS
2038 (SELECT queue_table
2039 FROM dba_queue_tables dqt
2040 WHERE dqt.owner = di.table_owner
2041 AND dqt.queue_table = di.table_name)
2042 OR di.table_name LIKE 'AQ$%')
2043 AND di.index_type ='LOB'
2044 AND NVL(di.temporary, 'N') = 'N'
2045 AND di.index_name NOT LIKE 'BIN$%'
2046 UNION ALL
2047 -- Child Domain Indexes IOTs
2048 SELECT /*+ rule */ d.owner,
2049 d.index_name table_name,
2050 d.tablespace_name,
2051 fnd_ts_mig_util.l_def_ind_tsp object_class,
2052 SUBSTR(d.table_name, 4, LENGTH(d.table_name)-5) parent,
2053 'INDEX' object_type,
2054 'DOMAIN' subobject_type,
2055 'NO' classified,
2056 nvl(d.partitioned,'NO') partitioned
2057 FROM dba_indexes d
2058 WHERE d.table_owner IN (select oracle_username
2059 from fnd_oracle_userid
2060 where read_only_flag IN ('E','A','U','K','M'))
2061 AND d.index_type = 'IOT - TOP'
2062 AND d.table_name LIKE 'DR$%'
2063 AND NVL(d.temporary, 'N') = 'N'
2064 AND d.index_name NOT LIKE 'BIN$%'
2065 UNION ALL
2066 -- Indexes on Child Domain Indexes tables including IOTs
2067 SELECT /*+ rule */ di.owner,
2068 di.index_name table_name,
2069 di.tablespace_name,
2070 fnd_ts_mig_util.l_def_ind_tsp object_class,
2071 di.table_name parent,
2072 'INDEX' object_type,
2073 'DOMAIN' subobject_type,
2074 'NO' classified,
2075 nvl(di.partitioned,'NO') partitioned
2076 FROM dba_indexes di
2077 WHERE di.table_owner IN (select oracle_username
2078 from fnd_oracle_userid
2079 where read_only_flag IN ('E','A','U','K','M'))
2080 AND di.table_name LIKE 'DR$%'
2081 AND di.index_type not in ('DOMAIN','IOT - TOP','CLUSTER','LOB')
2082 AND NVL(di.temporary, 'N') = 'N'
2083 AND di.index_name NOT LIKE 'BIN$%'
2084 UNION ALL
2085 -- Indexes on Child Domain Indexes tables including IOTs wit lob
2086 SELECT /*+ rule */ di.owner,
2087 di.index_name table_name,
2088 di.tablespace_name,
2089 fnd_ts_mig_util.l_def_tab_tsp object_class,
2090 di.table_name parent,
2091 'INDEX' object_type,
2092 'DOMAIN' subobject_type,
2093 'NO' classified,
2094 nvl(di.partitioned,'NO') partitioned
2095 FROM dba_indexes di
2096 WHERE di.table_owner IN (select oracle_username
2097 from fnd_oracle_userid
2098 where read_only_flag IN ('E','A','U','K','M'))
2099 AND di.table_name LIKE 'DR$%'
2100 AND di.index_type ='LOB'
2101 AND NVL(di.temporary, 'N') = 'N';
2102
2103 cursor get_ind_partition(p_owner VARCHAR2, p_index_name VARCHAR2) is
2104 select /*+ ALL_ROWS */ partition_name, tablespace_name
2105 from dba_ind_partitions
2106 where index_owner = p_owner
2107 and index_name = p_index_name;
2108 BEGIN
2109 /*
2110 DELETE FROM fnd_ts_sizing
2111 WHERE object_type IN ('INDEX', 'INDEX PARTITION', 'LOBINDEX');
2112 COMMIT;
2113 */
2114
2115 OPEN ind_csr;
2116 LOOP
2117 FETCH ind_csr INTO cur_rec;
2118 EXIT WHEN ind_csr%NOTFOUND;
2119
2120 cur_rec.object_class := trim(cur_rec.object_class);
2121 cur_rec.parent := trim(cur_rec.parent);
2122 cur_rec.object_type := trim(cur_rec.object_type);
2123 cur_rec.subobject_type := trim(cur_rec.subobject_type);
2124 cur_rec.classified := trim(cur_rec.classified);
2125 cur_rec.partitioned := trim(cur_rec.partitioned);
2126
2127 if (cur_rec.partitioned = 'NO') then
2128 -- non-partitioned indexes will be sized here.
2129 ins_fnd_ts_sizing (
2130 cur_rec,
2131 p_uni_extent,
2132 p_allocation_type,
2133 p_creation_date);
2134 elsif (cur_rec.object_type = 'INDEX') then
2135 cur_rec.object_type := 'INDEX PARTITION';
2136 FOR ind_part_rec IN get_ind_partition(cur_rec.owner, cur_rec.table_name)
2137 LOOP
2138 cur_rec.tablespace_name := ind_part_rec.tablespace_name;
2139 ins_fnd_ts_sizing (
2140 cur_rec,
2141 p_uni_extent,
2142 p_allocation_type,
2143 p_creation_date,
2144 ind_part_rec.partition_name);
2145 END LOOP;
2146 end if;
2147 COMMIT;
2148 END LOOP;
2149 CLOSE ind_csr;
2150 END gen_all_ind_sizing;
2151
2152 END fnd_ts_size;
[Home] [Help]