[Home] [Help]
PACKAGE BODY: APPS.FND_GEN_MIG_CMDS
Source
1 PACKAGE BODY fnd_gen_mig_cmds AS
2 /* $Header: fndpgmcb.pls 120.10 2006/08/17 01:13:07 mnovakov noship $ */
3
4 G_USER_ID NUMBER := FND_GLOBAL.USER_ID;
5 G_LOGIN_ID NUMBER := FND_GLOBAL.CONC_LOGIN_ID;
6
7 g_threshold_size NUMBER;
8
9 TYPE NumTabType IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
10 TYPE CharTabType IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;
11
12
13 PROCEDURE write_out( p_owner IN VARCHAR2,
14 p_object_type IN VARCHAR2,
15 p_mig_cmd IN VARCHAR2,
16 p_object_name IN VARCHAR2 DEFAULT NULL,
17 p_old_tablespace IN VARCHAR2 DEFAULT NULL,
18 p_new_tablespace IN VARCHAR2 DEFAULT NULL,
19 p_subobject_type IN VARCHAR2 DEFAULT 'X',
20 p_parent_owner IN VARCHAR2 DEFAULT NULL,
21 p_parent_object_name IN VARCHAR2 DEFAULT NULL,
22 p_tot_blocks IN NUMBER DEFAULT 0,
23 p_index_parallel IN VARCHAR2 DEFAULT 'NOPARALLEL',
24 p_execution_mode IN VARCHAR2 DEFAULT NULL,
25 p_partitioned IN VARCHAR2 DEFAULT 'NO',
26 p_err_text IN VARCHAR2 DEFAULT NULL,
27 p_parent_lineno IN NUMBER DEFAULT NULL,
28 x_lineno OUT NOCOPY NUMBER)
29 IS
30 CURSOR lineno_csr IS
31 SELECT FND_TS_MIG_CMDS_S.nextval
32 FROM SYS.dual;
33 l_lineno NUMBER;
34
35 CURSOR cmd_csr IS
36 SELECT lineno,
37 new_tablespace,
38 object_type,
39 subobject_type,
40 migration_status
41 FROM fnd_ts_mig_cmds
42 WHERE owner = p_owner
43 AND object_type = p_object_type
44 AND object_name = p_object_name
45 AND index_parallel = NVL(p_index_parallel, 'NOPARALLEL')
46 AND subobject_type = NVL(p_subobject_type, 'X')
47 order by migration_status;
48 cmd_rec cmd_csr%ROWTYPE;
49 l_generated BOOLEAN := FALSE;
50 l_mig_cmd varchar2(4000);
51 BEGIN
52
53 OPEN cmd_csr;
54 FETCH cmd_csr INTO cmd_rec;
55 if cmd_csr%FOUND
56 then
57 if p_object_type NOT IN ('ENABLE_TRIGGER', 'ENABLE_CONSTRAINT', 'DISABLE_TRIGGER', 'DISABLE_CONSTRAINT', 'STOP_QUEUE', 'START_QUEUE', 'ENABLE_POLICY', 'DISABLE_POLICY', 'POSTMIG')
58 then
59 if cmd_rec.migration_status IN ('GENERATED', 'ERROR')
60 then
61 UPDATE fnd_ts_mig_cmds
62 SET migration_cmd = p_mig_cmd,
63 new_tablespace = p_new_tablespace,
64 old_tablespace = p_old_tablespace,
65 total_blocks = p_tot_blocks,
66 partitioned = p_partitioned,
67 parent_owner = p_parent_owner,
68 parent_object_name = p_parent_object_name,
69 generation_date = sysdate,
70 last_update_date = sysdate
71 WHERE lineno = cmd_rec.lineno;
72 l_generated := TRUE;
73 end if;
74 else
75 UPDATE fnd_ts_mig_cmds
76 SET migration_cmd = p_mig_cmd,
77 last_update_date = sysdate
78 WHERE lineno = cmd_rec.lineno;
79 l_generated := TRUE;
80 end if;
81 x_lineno := cmd_rec.lineno;
82 end if;
83 CLOSE cmd_csr;
84
85 if NOT l_generated
86 then
87 OPEN lineno_csr;
88 FETCH lineno_csr INTO l_lineno;
89 CLOSE lineno_csr;
90
91 INSERT INTO fnd_ts_mig_cmds (lineno,
92 owner,
93 object_type,
94 subobject_type,
95 index_parallel,
96 object_name,
97 parent_lineno,
98 old_tablespace,
99 new_tablespace,
100 migration_cmd,
101 migration_status,
102 parent_owner,
103 parent_object_name,
104 total_blocks,
105 execution_mode,
106 partitioned,
107 error_text,
108 generation_date,
109 last_update_date)
110 VALUES (l_lineno,
111 p_owner,
112 p_object_type,
113 NVL(p_subobject_type, 'X'),
114 NVL(p_index_parallel, 'NOPARALLEL'),
115 p_object_name,
116 p_parent_lineno,
117 p_old_tablespace,
118 p_new_tablespace,
119 p_mig_cmd,
120 'GENERATED',
121 p_parent_owner,
122 p_parent_object_name,
123 p_tot_blocks,
124 p_execution_mode,
125 p_partitioned,
126 p_err_text,
127 sysdate,
128 sysdate);
129 x_lineno := l_lineno;
130 end if;
131 END write_out;
132
133 FUNCTION get_txn_idx_tablespace
134 RETURN VARCHAR2
135 IS
136 CURSOR idx_tbs_csr IS
137 SELECT tablespace
138 FROM fnd_tablespaces
139 WHERE tablespace_type = fnd_ts_mig_util.l_def_ind_tsp;
140 l_tablespace_name VARCHAR2(30);
141 BEGIN
142 OPEN idx_tbs_csr;
143 FETCH idx_tbs_csr INTO l_tablespace_name;
144 if idx_tbs_csr%NOTFOUND then
145 raise_application_error(-20001, 'FND_TABLESPACES table does not have any entry for the Transaction index tablespace');
146 end if;
147 CLOSE idx_tbs_csr;
148 RETURN l_tablespace_name;
149 END get_txn_idx_tablespace;
150
151 FUNCTION get_idx_tablespace(p_tablespace_type IN VARCHAR2,
152 p_tab_tablespace IN VARCHAR2,
153 p_txn_idx_tablespace IN VARCHAR2)
154 RETURN VARCHAR2
155 IS
156 l_idx_tablespace VARCHAR2(30);
157 BEGIN
158 if p_tablespace_type = fnd_ts_mig_util.l_def_tab_tsp then
159 l_idx_tablespace := p_txn_idx_tablespace;
160 else
161 l_idx_tablespace := p_tab_tablespace;
162 end if;
163 RETURN l_idx_tablespace;
164 END get_idx_tablespace;
165
166
167 FUNCTION get_tot_blocks ( p_owner IN VARCHAR2,
168 p_object_type IN VARCHAR2,
169 p_object_name IN VARCHAR2,
170 p_partition_name IN VARCHAR2)
171 RETURN NUMBER
172 IS
173 tot_blks NUMBER;
174 tot_byts NUMBER;
175 unused_blks NUMBER;
176 unused_byts NUMBER;
177 lst_ext_file NUMBER;
178 lst_ext_blk NUMBER;
179 lst_usd_blk NUMBER;
180 l_version NUMBER;
181
182 CURSOR lob_part_siz_csr IS
183 SELECT blocks
184 FROM dba_segments
185 WHERE owner = p_owner
186 AND segment_name = p_object_name
187 AND partition_name = p_partition_name;
188 BEGIN
189 if p_object_type = 'LOB PARTITION' then
190 l_version := fnd_ts_mig_util.get_db_version;
191 end if;
192
193 if p_object_type = 'LOB PARTITION' AND l_version < 10 then
194 -- LOB PARTITIONS are not supported in DBMS_SPACE in 9i Bug# 2169303
195 OPEN lob_part_siz_csr;
196 FETCH lob_part_siz_csr INTO tot_blks;
197 CLOSE lob_part_siz_csr;
198 else
199 DBMS_SPACE.UNUSED_SPACE (
200 p_owner,
201 p_object_name,
202 p_object_type,
203 tot_blks,
204 tot_byts,
205 unused_blks,
206 unused_byts,
207 lst_ext_file,
208 lst_ext_blk,
209 lst_usd_blk,
210 p_partition_name);
211 end if;
212 RETURN (NVL(tot_blks, 0) - NVL(unused_blks, 0));
213 EXCEPTION WHEN OTHERS THEN
214 RETURN NVL(tot_blks, 0);
215 -- DBMS_OUTPUT.PUT_LINE(p_owner||p_object_name||p_object_type||nvl(p_partition_name,'null'));
216 -- RAISE_APPLICATION_ERROR(-20001, p_owner||p_object_name||p_object_type||nvl(p_partition_name,'null'));
217 END get_tot_blocks;
218
219
220 PROCEDURE gen_move_obj ( p_owner IN VARCHAR2,
221 p_obj_type IN VARCHAR2,
222 p_sub_obj_type IN VARCHAR2,
223 p_obj_name IN VARCHAR2,
224 p_partitioned IN VARCHAR2,
225 p_logging IN VARCHAR2,
226 p_old_tablespace IN VARCHAR2,
227 p_new_tablespace IN VARCHAR2,
228 p_parent_owner IN VARCHAR2 DEFAULT NULL,
229 p_parent_obj_name IN VARCHAR2 DEFAULT NULL,
230 p_parent_lineno IN NUMBER DEFAULT NULL,
231 x_execution_mode OUT NOCOPY VARCHAR2,
232 x_lineno OUT NOCOPY NUMBER)
233 IS
234 CURSOR col_csr(l_owner VARCHAR2, l_table_name VARCHAR2) IS
235 SELECT owner,
236 table_name,
237 column_name,
238 data_type
239 FROM dba_tab_columns
240 WHERE owner = l_owner
241 AND table_name = l_table_name
242 AND data_type IN ('CLOB', 'BLOB', 'NCLOB');
243 -- ORDER by column_id;
244
245 CURSOR lob_chunk_csr(l_owner VARCHAR2, l_table_name VARCHAR2) IS
246 SELECT MIN(dl.chunk)
247 FROM dba_lobs dl
248 WHERE dl.owner = l_owner
249 AND dl.table_name = l_table_name;
250 l_chunk_size DBA_LOBS.CHUNK%TYPE;
251
252 CURSOR col_lob_csr (l_owner VARCHAR2, l_table_name VARCHAR2) IS
253 SELECT /*+ FIRST_ROWS */ d.column_name ,
254 d.table_name,
255 d.segment_name,
256 d.owner
257 FROM dba_lobs d
258 WHERE owner = l_owner
259 AND table_name = l_table_name
260 AND NOT EXISTS (select column_name
261 from dba_tab_columns c
262 where c.data_type in ('CLOB','BLOB','NCLOB')
263 and c.owner = l_owner
264 and c.table_name = l_table_name
265 and c.column_name = d.column_name)
266 AND EXISTS (select attr_name
267 from dba_type_attrs ta,
268 dba_tab_columns tc
269 where tc.owner = l_owner
270 and tc.table_name = l_table_name
271 and tc.column_name = SUBSTR(d.column_name, 2, INSTR(d.column_name, '.', 1) - 3)
272 and tc.data_type_owner = ta.owner
273 and tc.data_type = ta.type_name
274 and ta.attr_type_name in ('CLOB','BLOB','NCLOB')
275 and ta.attr_name = RTRIM(SUBSTR(d.column_name,INSTR(d.column_name, '.', -1) + 2), '"'));
276
277 -- Get all the LOBs (SEGMENT and INDEX) for sizing
278 CURSOR lob_csr (l_owner VARCHAR2, l_table_name VARCHAR2) IS
279 SELECT owner,
280 column_name,
281 table_name,
282 segment_name,
283 index_name
284 FROM dba_lobs d
285 WHERE owner = l_owner
286 AND table_name = l_table_name;
287
288 CURSOR iot_csr(l_owner VARCHAR2, l_table_name VARCHAR2) IS
289 SELECT table_name
290 FROM dba_tables
291 WHERE owner = l_owner
292 AND iot_type = 'IOT_OVERFLOW'
293 AND iot_name = l_table_name;
294 l_iot_over_name DBA_TABLES.TABLE_NAME%TYPE;
295
296 CURSOR iot_top_csr(l_owner VARCHAR2, l_table_name VARCHAR2) IS
297 SELECT index_name
298 FROM dba_indexes
299 WHERE owner = l_owner
300 AND table_name = l_table_name
301 AND index_type = 'IOT - TOP';
302 l_iot_top_name DBA_INDEXES.INDEX_NAME%TYPE;
303
304 CURSOR part_csr(l_owner VARCHAR2, l_table_name VARCHAR2) IS
305 SELECT partitioning_type,
306 subpartitioning_type,
307 def_tablespace_name
308 FROM dba_part_tables
309 WHERE owner = l_owner
310 AND table_name = l_table_name;
311
312 CURSOR tab_part_csr(l_owner VARCHAR2, l_table_name VARCHAR2) IS
313 SELECT partition_name,
314 tablespace_name,
315 logging
316 FROM dba_tab_partitions
317 WHERE table_owner = l_owner
318 AND table_name = l_table_name;
319
320 -- Get all the LOB Partitions (SEGMENT and INDEX) for sizing
321 CURSOR lob_part_csr (l_owner VARCHAR2,
322 l_table_name VARCHAR2,
323 l_part_name IN VARCHAR2) IS
324 SELECT lob_name,
325 column_name,
326 lob_partition_name,
327 lob_indpart_name
328 FROM dba_lob_partitions
329 WHERE table_owner = l_owner
330 AND table_name = l_table_name
331 AND partition_name = l_part_name;
332
333 l_obj_type FND_TS_MIG_CMDS.OBJECT_TYPE%TYPE;
334 l_obj_name FND_TS_MIG_CMDS.OBJECT_NAME%TYPE := p_obj_name;
335 l_parent_owner FND_TS_MIG_CMDS.PARENT_OBJECT_NAME%TYPE := p_parent_owner;
336 l_parent_obj_name FND_TS_MIG_CMDS.PARENT_OBJECT_NAME%TYPE := p_parent_obj_name;
337 l_string VARCHAR2(4000);
338 l_lob_str VARCHAR2(4000);
339 l_store_str VARCHAR2(4000);
340 l_chunk_str VARCHAR2(4000);
341 l_iot_str VARCHAR2(4000);
342 l_ues NUMBER;
343 l_parallel VARCHAR2(30) := 'NOPARALLEL';
344 l_long_cmd_type VARCHAR2(30);
345 l_storage_str VARCHAR2(4000);
346 l_logging_str VARCHAR2(30) := 'NOLOGGING';
347 l_tot_blocks NUMBER := 0;
348 l_sum_blocks NUMBER := 0;
349 l_lineno NUMBER;
350 l_parent_lineno NUMBER := p_parent_lineno;
351 l_execution_mode FND_TS_MIG_CMDS.EXECUTION_MODE%TYPE := 'P';
352 l_def_tablespace_name DBA_PART_TABLES.DEF_TABLESPACE_NAME%TYPE;
353 l_logging VARCHAR2(30) := 'YES';
354 l_version NUMBER;
355 BEGIN
356
357 if p_obj_type = 'LONG_TABLE' then
358 l_obj_type := 'TABLE';
359 l_long_cmd_type := 'ALTER'; --since there are multiple cmds for LONG_TABLE
360 elsif p_obj_type = 'MV_LOG' then
361 l_obj_type := 'MATERIALIZED VIEW LOG ON';
362 l_obj_name := p_parent_obj_name; -- Log table name
363 elsif p_obj_type = 'LONG_MVLOG' then
364 l_obj_type := 'MATERIALIZED VIEW LOG ON';
365 l_obj_name := p_parent_obj_name; -- Log table name
366 elsif p_obj_type = 'MVIEW' then
367 l_obj_type := 'TABLE';
368 else
369 l_obj_type := p_obj_type;
370 end if;
371
372 FOR col_rec IN col_csr(p_owner, l_obj_name)
373 LOOP
374 if col_csr%ROWCOUNT = 1 then
375 l_lob_str := 'LOB ('||col_rec.column_name;
376 else
377 l_lob_str := l_lob_str||', '||col_rec.column_name;
378 end if;
379 END LOOP;
380
381 FOR col_lob_rec IN col_lob_csr(p_owner, l_obj_name)
382 LOOP
383 if l_lob_str IS NULL then
384 l_lob_str := 'LOB ('||col_lob_rec.column_name;
385 else
386 l_lob_str := l_lob_str||', '||col_lob_rec.column_name;
387 end if;
388 END LOOP;
389
390 l_ues := fnd_ts_mig_util.get_tablespace_ues(p_new_tablespace);
391 if l_ues IS NOT NULL then
392 l_storage_str := 'STORAGE (INITIAL '||TO_CHAR(l_ues)||' NEXT '||TO_CHAR(l_ues)||') ';
393 end if;
394
395 if l_lob_str IS NOT NULL then
396 OPEN lob_chunk_csr(p_owner, l_obj_name);
397 FETCH lob_chunk_csr INTO l_chunk_size;
398 CLOSE lob_chunk_csr;
399
400 l_chunk_str := ' CHUNK '||l_chunk_size||' NOCACHE '||l_storage_str;
401 l_store_str := l_lob_str||') STORE AS (TABLESPACE '||p_new_tablespace;
402 l_lob_str := l_store_str;
403
404 l_parallel := 'NOPARALLEL';
405 l_execution_mode := 'P';
406 end if;
407
408 if NVL(p_sub_obj_type, 'N') = 'IOT' then
409 OPEN iot_csr(p_owner, l_obj_name);
410 FETCH iot_csr INTO l_iot_over_name;
411 if iot_csr%FOUND then
412 l_iot_str := ' OVERFLOW TABLESPACE '||p_new_tablespace||' '||l_storage_str;
413 end if;
414 CLOSE iot_csr;
415
416 OPEN iot_top_csr(p_owner, l_obj_name);
417 FETCH iot_top_csr INTO l_iot_top_name;
418 CLOSE iot_top_csr;
419 end if;
420
421 if NVL(p_partitioned, 'NO') = 'NO' then
422 -- Get the sizing for TABLE only since MV_LOG, MVIEW, LONG_MVLOG and
423 -- LONG_TABLE will be truncated before the move.
424 if p_obj_type = 'TABLE' then
425 -- Get tot blocks for table/iot
426 if NVL(p_sub_obj_type, 'N') = 'IOT' then
427 l_tot_blocks := get_tot_blocks(p_owner,
428 'INDEX',
429 l_iot_top_name,
430 NULL);
431 if l_iot_over_name IS NOT NULL then
432 l_tot_blocks := NVL(l_tot_blocks, 0) + get_tot_blocks(p_owner,
433 'TABLE',
434 l_iot_over_name,
435 NULL);
436 end if;
437 else
438 l_tot_blocks := get_tot_blocks(p_owner,
439 'TABLE',
440 p_obj_name,
441 NULL);
442 end if;
443 -- Get the total blocks for all LOBs
444 if l_lob_str IS NOT NULL then
445 FOR lob_rec IN lob_csr(p_owner, p_obj_name)
446 LOOP
447 l_tot_blocks := NVL(l_tot_blocks, 0) + get_tot_blocks(p_owner,
448 'LOB',
449 lob_rec.segment_name,
450 NULL);
451
452 l_tot_blocks := NVL(l_tot_blocks, 0) + get_tot_blocks(p_owner,
453 'INDEX',
454 lob_rec.index_name,
455 NULL);
456 END LOOP;
457 end if;
458 if l_lob_str IS NULL AND l_tot_blocks >= g_threshold_size then
459 l_parallel := 'PARALLEL';
460 l_execution_mode := 'S';
461 end if;
462 elsif p_obj_type = 'MV_LOG' then
463 l_string := 'TRUNCATE TABLE "'||p_owner||'"."'||p_parent_obj_name||'"';
464 write_out(
465 p_owner => p_owner,
466 p_object_type => p_obj_type,
467 p_mig_cmd => l_string,
468 p_object_name => l_obj_name,
469 p_subobject_type => 'TRUNCATE',
470 p_tot_blocks => l_tot_blocks,
471 p_parent_owner => NULL,
472 p_parent_object_name => NULL,
473 p_parent_lineno => NULL,
474 p_execution_mode => l_execution_mode,
475 p_partitioned => 'NO',
476 x_lineno => l_parent_lineno);
477 l_parent_owner := p_owner;
478 l_parent_obj_name := l_obj_name;
479 elsif p_obj_type = 'LONG_MVLOG' then
480 l_string := 'TRUNCATE TABLE "'||p_owner||'"."'||p_parent_obj_name||'"';
481 write_out(
482 p_owner => p_owner,
483 p_object_type => p_obj_type,
484 p_mig_cmd => l_string,
485 p_object_name => l_obj_name,
486 p_subobject_type => 'TRUNCATE',
487 p_tot_blocks => l_tot_blocks,
488 p_parent_owner => l_parent_owner,
489 p_parent_object_name => p_obj_name,
490 p_parent_lineno => p_parent_lineno,
491 p_execution_mode => l_execution_mode,
492 p_partitioned => 'NO',
493 x_lineno => l_parent_lineno);
494 l_parent_owner := p_owner;
495 l_parent_obj_name := p_obj_name;
496 elsif p_obj_type = 'MVIEW' then
497 l_string := 'TRUNCATE TABLE "'||p_owner||'"."'||p_obj_name||'"';
498 write_out(
499 p_owner => p_owner,
500 p_object_type => 'MVIEW',
501 p_mig_cmd => l_string,
502 p_object_name => l_obj_name,
503 p_subobject_type => 'TRUNCATE',
504 p_tot_blocks => l_tot_blocks,
505 p_parent_owner => p_parent_owner,
506 p_parent_object_name => p_parent_obj_name,
507 p_parent_lineno => p_parent_lineno,
508 p_execution_mode => l_execution_mode,
509 p_partitioned => 'NO',
510 x_lineno => l_parent_lineno);
511 l_parent_owner := p_owner;
512 l_parent_obj_name := l_obj_name;
513 end if;
514
515 l_lob_str := l_store_str||l_chunk_str;
516 if length(l_lob_str)>0 then l_lob_str := l_lob_str||')'; end if;
517 -- added ) at the end Mladena
518
519 l_string := 'ALTER '||l_obj_type||' "'||p_owner||'"."'||p_obj_name||'" MOVE TABLESPACE '||p_new_tablespace||' '||l_storage_str||' '||l_iot_str||' '||l_lob_str||' '||l_parallel||' '||l_logging_str;
520 write_out(
521 p_owner => p_owner,
522 p_object_type => p_obj_type,
523 p_mig_cmd => l_string,
524 p_object_name => l_obj_name,
525 p_old_tablespace => p_old_tablespace,
526 p_new_tablespace => p_new_tablespace,
527 p_subobject_type => l_long_cmd_type,
528 p_tot_blocks => l_tot_blocks,
529 p_parent_owner => l_parent_owner,
530 p_parent_object_name => l_parent_obj_name,
531 p_execution_mode => l_execution_mode,
532 p_partitioned => 'NO',
533 p_parent_lineno => l_parent_lineno,
534 x_lineno => l_lineno);
535 l_sum_blocks := l_tot_blocks;
536 x_lineno := l_lineno;
537 if p_logging = 'YES' then
538 l_logging_str := '" LOGGING';
539 else
540 l_logging_str := '" NOLOGGING';
541 end if;
542 l_string := 'ALTER '||l_obj_type||' "'||p_owner||'"."'||p_obj_name||l_logging_str;
543 write_out(
544 p_owner => p_owner,
545 p_object_type => p_obj_type,
546 p_mig_cmd => l_string,
547 p_object_name => l_obj_name,
548 p_old_tablespace => p_old_tablespace,
549 p_new_tablespace => p_new_tablespace,
550 p_subobject_type => 'LOGGING',
551 p_tot_blocks => l_tot_blocks,
552 p_parent_owner => l_parent_owner,
553 p_parent_object_name => l_parent_obj_name,
554 p_execution_mode => l_execution_mode,
555 p_partitioned => 'NO',
556 p_parent_lineno => NVL(l_lineno, l_parent_lineno),
557 x_lineno => l_lineno);
558 x_lineno := l_lineno;
559
560 -- x_lineno := l_lineno;
561
562 elsif NVL(p_partitioned, 'NO') = 'YES' then
563
564 if p_obj_type = 'TABLE' then
565 -- All partitioned tables will be moved sequentially irrespective of
566 -- the total block size except if they have LOBs.
567 if l_lob_str IS NOT NULL then
568 l_parallel := 'NOPARALLEL';
569 else
570 l_parallel := 'PARALLEL';
571 end if;
572 l_execution_mode := 'S';
573 elsif p_obj_type = 'MV_LOG' then
574 l_string := 'TRUNCATE TABLE "'||p_owner||'"."'||p_parent_obj_name||'"';
575 write_out(
576 p_owner => p_owner,
577 p_object_type => p_obj_type,
578 p_mig_cmd => l_string,
579 p_object_name => l_obj_name,
580 p_subobject_type => 'TRUNCATE',
581 p_parent_object_name => NULL,
582 p_parent_lineno => NULL,
583 p_execution_mode => l_execution_mode,
584 p_partitioned => 'NO',
585 x_lineno => l_parent_lineno);
586 l_parent_owner := p_owner;
587 l_parent_obj_name := l_obj_name;
588 elsif p_obj_type = 'LONG_MVLOG' then
589 l_string := 'TRUNCATE TABLE "'||p_owner||'"."'||p_parent_obj_name||'"';
590 write_out(
591 p_owner => p_owner,
592 p_object_type => p_obj_type,
593 p_mig_cmd => l_string,
594 p_object_name => l_obj_name,
595 p_subobject_type => 'TRUNCATE',
596 p_tot_blocks => l_tot_blocks,
597 p_parent_owner => l_parent_owner,
598 p_parent_object_name => p_obj_name,
599 p_parent_lineno => NULL,
600 p_execution_mode => l_execution_mode,
601 p_partitioned => 'NO',
602 x_lineno => l_parent_lineno);
603 l_parent_owner := p_owner;
604 l_parent_obj_name := p_obj_name;
605 elsif p_obj_type = 'MVIEW' then
606 -- All MVs will be truncated before move
607 l_string := 'TRUNCATE TABLE "'||p_owner||'"."'||p_obj_name||'"';
608 write_out(
609 p_owner => p_owner,
610 p_object_type => 'MVIEW',
611 p_mig_cmd => l_string,
612 p_object_name => l_obj_name,
613 p_subobject_type => 'TRUNCATE',
614 p_parent_object_name => p_parent_obj_name,
615 p_parent_lineno => p_parent_lineno,
616 p_execution_mode => l_execution_mode,
617 p_partitioned => 'NO',
618 x_lineno => l_parent_lineno);
619 l_parent_owner := p_owner;
620 l_parent_obj_name := l_obj_name;
621 end if;
622
623 FOR part_rec IN part_csr(p_owner, p_obj_name)
624 LOOP
625 l_def_tablespace_name := part_rec.def_tablespace_name;
626 if NVL(part_rec.partitioning_type, 'X') = 'HASH' OR NVL(part_rec.subpartitioning_type, 'X') = 'HASH'
627 then
628 l_storage_str := NULL;
629 l_logging_str := NULL;
630 l_version := fnd_ts_mig_util.get_db_version;
631 if l_version >= 10 then
632 l_chunk_str := '';
633 end if;
634 -- l_chunk_str := l_chunk_str || ')'; -- added Mladena
635 end if;
636 END LOOP;
637
638 l_logging := 'YES';
642 then
639 FOR tab_part_rec IN tab_part_csr(p_owner, p_obj_name)
640 LOOP
641 if tab_part_rec.tablespace_name <> p_new_tablespace
643 if p_obj_type = 'TABLE' then
644 -- Get the sizing for TABLEs only since MV Logs and MVs will be
645 -- truncated before the move(Tables with LONG cannot be partitioned)
646 if NVL(p_sub_obj_type, 'N') = 'IOT' then
647 l_tot_blocks := get_tot_blocks(p_owner,
648 'INDEX PARTITION',
649 l_iot_top_name,
650 tab_part_rec.partition_name);
651 else
652 l_tot_blocks := get_tot_blocks(p_owner,
653 'TABLE PARTITION',
654 p_obj_name,
655 tab_part_rec.partition_name);
656 if l_iot_over_name IS NOT NULL then
657 l_tot_blocks := NVL(l_tot_blocks, 0) +
658 get_tot_blocks(p_owner,
659 'TABLE PARTITION',
660 l_iot_over_name,
661 tab_part_rec.partition_name);
662 end if;
663 end if;
664 -- Get the total blocks for all LOB Partitions
665 if l_lob_str IS NOT NULL then
666 FOR lob_part_rec IN lob_part_csr(p_owner,
667 p_obj_name,
668 tab_part_rec.partition_name)
669 LOOP
670
671 l_tot_blocks := NVL(l_tot_blocks, 0) +
672 get_tot_blocks(p_owner,
673 'LOB PARTITION',
674 lob_part_rec.lob_name,
675 lob_part_rec.lob_partition_name);
676
677 l_tot_blocks := NVL(l_tot_blocks, 0) +
678 get_tot_blocks(p_owner,
679 'INDEX PARTITION',
680 REPLACE(lob_part_rec.lob_name, 'LOB', 'IL'),
681 lob_part_rec.lob_indpart_name);
682 END LOOP;
683 end if;
684 end if;
685
686 l_lob_str := l_store_str||l_chunk_str;
687 if length(l_lob_str)>0 then l_lob_str := l_lob_str||')'; end if;
688 -- added ) at the end Mladena
689
690 l_string := 'ALTER '||l_obj_type||' "'||p_owner||'"."'||p_obj_name||'" MOVE PARTITION '||tab_part_rec.partition_name||' TABLESPACE '||p_new_tablespace||' '||l_storage_str||' '||l_iot_str||' '||l_lob_str||' '||l_parallel||' '||l_logging_str;
691 write_out(
692 p_owner => p_owner,
693 p_object_type => p_obj_type,
694 p_mig_cmd => l_string,
695 p_object_name => l_obj_name,
696 p_old_tablespace => tab_part_rec.tablespace_name,
697 p_new_tablespace => p_new_tablespace,
698 p_subobject_type => tab_part_rec.partition_name,
699 p_tot_blocks => l_tot_blocks,
700 p_parent_owner => l_parent_owner,
701 p_parent_object_name => l_parent_obj_name,
702 p_execution_mode => l_execution_mode,
703 p_partitioned => 'YES',
704 p_parent_lineno => NVL(l_lineno, l_parent_lineno),
705 x_lineno => l_lineno);
706 l_sum_blocks := l_sum_blocks + l_tot_blocks;
707 l_logging := tab_part_rec.logging;
708 end if;
709 END LOOP;
710
711 if l_def_tablespace_name <> p_new_tablespace then
712 l_string := 'ALTER TABLE "'||p_owner||'"."'||l_obj_name||'" MODIFY DEFAULT ATTRIBUTES TABLESPACE '||p_new_tablespace;
713 write_out(
714 p_owner => p_owner,
715 p_object_type => p_obj_type,
716 p_mig_cmd => l_string,
717 p_object_name => l_obj_name,
718 p_new_tablespace => p_new_tablespace,
719 p_subobject_type => 'DEFAULT_TSP',
720 p_parent_owner => l_parent_owner,
721 p_parent_object_name => l_parent_obj_name,
722 p_execution_mode => l_execution_mode,
723 p_partitioned => 'NO',
724 p_parent_lineno => NVL(l_lineno, l_parent_lineno),
725 x_lineno => l_lineno);
726
727 end if;
728 if l_logging = 'YES' then
729 l_logging_str := '" LOGGING';
730 else
731 l_logging_str := '" NOLOGGING';
732 end if;
733 l_string := 'ALTER TABLE "'||p_owner||'"."'||l_obj_name||l_logging_str;
734 write_out(
735 p_owner => p_owner,
736 p_object_type => p_obj_type,
737 p_mig_cmd => l_string,
738 p_object_name => l_obj_name,
739 p_new_tablespace => p_new_tablespace,
740 p_subobject_type => 'LOGGING',
741 p_parent_owner => l_parent_owner,
742 p_parent_object_name => l_parent_obj_name,
743 p_execution_mode => l_execution_mode,
744 p_partitioned => 'NO',
745 p_parent_lineno => NVL(l_lineno, l_parent_lineno),
746 x_lineno => l_lineno);
747 x_lineno := l_lineno;
748 end if;
749
750 -- Update the MV_LOG command with sum blocks
751 -- as the MV_LOG TRUNCATE is the parent which will be enqueued initially.
752 BEGIN
753 if p_obj_type = 'TABLE' then
754 UPDATE fnd_ts_mig_cmds
755 SET total_blocks = l_sum_blocks,
759 AND object_name = p_parent_obj_name;
756 execution_mode = l_execution_mode
757 WHERE owner = p_owner
758 AND object_type = 'MV_LOG'
760 end if;
761 EXCEPTION WHEN OTHERS THEN
762 NULL;
763 END;
764
765 x_execution_mode := l_execution_mode;
766 END gen_move_obj;
767
768
769 PROCEDURE gen_rebuild_idx( p_owner IN VARCHAR2,
770 p_table_name IN VARCHAR2,
771 p_parent_obj_type IN VARCHAR2,
772 p_tab_moved IN BOOLEAN,
773 p_tablespace_name IN VARCHAR2,
774 p_parent_lineno IN NUMBER,
775 p_execution_mode IN VARCHAR2,
776 p_type IN VARCHAR2 DEFAULT 'INDEX')
777 IS
778 -- get all the indexes on the table
779 CURSOR ind_csr(l_owner VARCHAR2, l_table_name VARCHAR2) IS
780 SELECT owner,
781 index_name,
782 index_type,
783 tablespace_name,
784 partitioned,
785 status,
786 ityp_owner,
787 ityp_name,
788 domidx_opstatus,
789 domidx_status,
790 ltrim(rtrim(degree)) degree,
791 ltrim(rtrim(logging)) logging
792 FROM dba_indexes
793 WHERE table_owner = l_owner
794 AND table_name = l_table_name
795 AND NVL(temporary, 'N') = 'N'
796 AND index_type NOT IN ('IOT - TOP', 'LOB', 'CLUSTER')
797 ORDER by index_type DESC;
798
799 CURSOR part_csr(l_index_owner VARCHAR2, l_index_name VARCHAR2) IS
800 SELECT partitioning_type,
801 subpartitioning_type,
802 def_tablespace_name
803 FROM dba_part_indexes
804 WHERE owner = l_index_owner
805 AND index_name = l_index_name;
806
807 CURSOR ind_part_csr(l_index_owner VARCHAR2, l_index_name VARCHAR2) IS
808 SELECT /*+ ALL_ROWS */ partition_name,
809 tablespace_name,
810 status,
811 ltrim(rtrim(logging)) logging
812 FROM dba_ind_partitions
813 WHERE index_owner = l_index_owner
814 AND index_name = l_index_name;
815
816 -- Check to see if all the dependent tables of context index are in the correct tablesapce, move them if not.
817 CURSOR ctx_csr(l_index_owner VARCHAR2,
818 l_ctx_tabs VARCHAR2,
819 l_tablespace_name VARCHAR2) IS
820 SELECT dt.owner,
821 dt.table_name,
822 dt.tablespace_name,
823 dt.partitioned,
824 dt.logging,
825 dt.iot_type
826 FROM dba_tables dt
827 WHERE dt.owner = l_index_owner
828 AND dt.table_name LIKE l_ctx_tabs
829 AND dt.tablespace_name <> l_tablespace_name
830 AND NVL(dt.iot_type, 'N') NOT IN ('IOT', 'IOT_OVERFLOW')
831 AND NVL(dt.temporary, 'N') = 'N'
832 UNION
833 SELECT di.owner,
834 di.table_name,
835 di.tablespace_name,
836 di.partitioned,
837 di.logging,
838 'IOT' iot_type
839 FROM dba_indexes di
840 WHERE di.owner = l_index_owner
841 AND di.table_name LIKE l_ctx_tabs
842 AND di.tablespace_name <> l_tablespace_name
843 AND di.index_type = 'IOT _ TOP'
844 AND NVL(di.temporary, 'N') = 'N';
845 ctx_rec ctx_csr%ROWTYPE;
846
847 l_query VARCHAR2(4000);
848 TYPE sdo_csr_type is REF CURSOR;
849 sdo_csr sdo_csr_type;
850 TYPE ctx_csr_type is REF CURSOR;
851 ctx_stat_csr ctx_csr_type;
852 l_sdo_metadata_table VARCHAR2(100);
853
854 TYPE sdo_rec_type IS RECORD (
855 sdo_index_type VARCHAR2(32),
856 sdo_tsname VARCHAR2(32),
857 sdo_index_table VARCHAR2(32),
858 sdo_tablespace VARCHAR2(32),
859 sdo_index_dims NUMBER,
860 sdo_rtree_pctfree NUMBER,
861 sdo_commit_interval NUMBER,
862 sdo_level NUMBER,
863 sdo_numtiles NUMBER);
864 sdo_rec sdo_rec_type;
865
866 l_dummy INTEGER;
867
868 l_storage_pref VARCHAR2(60) := 'APPS.TXN_IND_STORAGE_PREF';
869 l_string VARCHAR2(4000);
870 l_sdo_params VARCHAR2(4000);
871 l_ues NUMBER;
872 l_parallel VARCHAR2(30);
873 l_storage_str VARCHAR2(4000);
874 l_logging_str VARCHAR2(30);
875 l_tot_blocks NUMBER := 0;
876 l_lineno1 NUMBER;
877 l_lineno2 NUMBER;
878 l_lineno3 NUMBER;
879 l_execution_mode FND_TS_MIG_CMDS.EXECUTION_MODE%TYPE := nvl(p_execution_mode, 'P');
880 -- l_parent_exec_mode FND_TS_MIG_CMDS.EXECUTION_MODE%TYPE := p_execution_mode;
881 l_parent_lineno NUMBER := p_parent_lineno;
882 l_def_tablespace_name DBA_PART_INDEXES.DEF_TABLESPACE_NAME%TYPE;
883 l_tablespace_name FND_TABLESPACES.TABLESPACE%TYPE;
884 l_part NUMBER;
885 l_logging VARCHAR2(30) := 'YES';
886
887 BEGIN
888 FOR ind_rec IN ind_csr(p_owner, p_table_name)
889 LOOP
890 --dbms_output.put_line('index name '||ind_rec.index_name);
891 l_lineno1 := null;
892 l_lineno2 := null;
893 l_ues := fnd_ts_mig_util.get_tablespace_ues(p_tablespace_name);
894 if l_ues IS NOT NULL then
895 l_storage_str := 'STORAGE (INITIAL '||TO_CHAR(l_ues)||' NEXT '||TO_CHAR(l_ues)||') ';
896 end if;
897
898 l_logging_str := 'NOLOGGING';
899 if p_execution_mode = 'P' then
903 end if;
900 l_parallel := 'NOPARALLEL';
901 elsif p_execution_mode = 'S' then
902 l_parallel := 'PARALLEL';
904
905 if ind_rec.index_type <> 'DOMAIN' AND NVL(ind_rec.partitioned, 'NO') = 'NO'
906 then
907 -- Rebuild the index only if the table was moved OR the index is
908 -- not in the correct tablespace.
909 if p_tab_moved OR (ind_rec.tablespace_name <> p_tablespace_name)
910 then
911 /* Execution mode will be same as that of the parent object.
912 if p_parent_obj_type = 'TABLE' then
913 -- Get the sizing for indexes of TABLEs only since MV_LOGs, MVIEWs
914 -- and LONG_TABLEs will truncated before the move.
915 l_tot_blocks := get_tot_blocks(ind_rec.owner,
916 'INDEX',
917 ind_rec.index_name,
918 NULL);
919 if l_tot_blocks >= g_threshold_size AND l_parent_exec_mode = 'S'
920 then
921 l_parallel := 'PARALLEL';
922 l_execution_mode := 'S';
923 end if;
924 end if;
925 */
926 l_string := 'ALTER INDEX "'||ind_rec.owner||'"."'||ind_rec.index_name||'" REBUILD TABLESPACE '||p_tablespace_name||' '||l_storage_str||' '||l_parallel||' '||l_logging_str;
927 write_out(
928 p_owner => ind_rec.owner,
929 p_object_type => p_type,
930 p_mig_cmd => l_string,
931 p_object_name => ind_rec.index_name,
932 p_old_tablespace => ind_rec.tablespace_name,
933 p_new_tablespace => p_tablespace_name,
934 p_parent_owner => p_owner,
935 p_parent_object_name => p_table_name,
936 p_tot_blocks => l_tot_blocks,
937 p_index_parallel => 'PARALLEL', -- l_parallel,
938 p_execution_mode => l_execution_mode,
939 p_parent_lineno => l_parent_lineno,
940 p_partitioned => 'NO',
941 x_lineno => l_lineno1);
942
943 l_parent_lineno := l_lineno1;
944 -- l_parent_exec_mode := l_execution_mode;
945
946 /* Mladena */
947 -- if l_parallel = 'PARALLEL' then
948 -- Set the Degree of parallelism back to 1.
949 -- Set the Degree of parallelism back to original value.
950 -- Set the LOGGING back to original value.
951 -- l_string := 'ALTER INDEX "'||ind_rec.owner||'"."'||ind_rec.index_name||'" NOPARALLEL';
952 if ind_rec.degree='1' then
953 l_string := ' NOPARALLEL';
954 elsif ind_rec.degree='DEFAULT' then
955 l_string := 'PARALLEL';
956 elsif ind_rec.degree>1 then
957 l_string := 'PARALLEL '||ind_rec.degree;
958 end if;
959
960 if ind_rec.logging = 'YES' then
961 l_string := l_string||' LOGGING';
962 end if;
963
964 l_string := 'ALTER INDEX "'||ind_rec.owner||'"."'||ind_rec.index_name||'" '||l_string;
965 write_out(
966 p_owner => ind_rec.owner,
967 p_object_type => p_type,
968 p_mig_cmd => l_string,
969 p_object_name => ind_rec.index_name,
970 p_old_tablespace => ind_rec.tablespace_name,
971 p_new_tablespace => p_tablespace_name,
972 p_parent_owner => ind_rec.owner,
973 p_parent_object_name => p_table_name,
974 p_tot_blocks => l_tot_blocks,
975 p_index_parallel => 'NOPARALLEL',
976 p_execution_mode => l_execution_mode,
977 p_parent_lineno => l_parent_lineno,
978 p_partitioned => 'NO',
979 x_lineno => l_lineno2);
980
981 l_parent_lineno := l_lineno2;
982 -- end if;
983 end if;
984 elsif ind_rec.index_type <> 'DOMAIN' AND NVL(ind_rec.partitioned, 'NO') = 'YES'
985 then
986 FOR part_rec IN part_csr(ind_rec.owner, ind_rec.index_name)
987 LOOP
988 l_def_tablespace_name := part_rec.def_tablespace_name;
989 if NVL(part_rec.partitioning_type, 'X') = 'HASH' OR NVL(part_rec.subpartitioning_type, 'X') = 'HASH'
990 then
991 l_storage_str := NULL;
992 l_logging_str := NULL;
993 end if;
994 END LOOP;
995
996 l_part := 0;
997 l_logging := 'YES';
998 FOR ind_part_rec IN ind_part_csr(ind_rec.owner, ind_rec.index_name)
999 LOOP
1000 if p_tab_moved OR (ind_part_rec.tablespace_name <> p_tablespace_name)
1001 then
1002 /* Execution mode will be same as that of the parent object.
1003 if p_parent_obj_type = 'TABLE' then
1004 -- Get the sizing for indexes of TABLEs only since MV_LOGs, MVIEWs
1005 -- and LONG_TABLEs will truncated before the move.
1006 l_tot_blocks := get_tot_blocks(ind_rec.owner,
1007 'INDEX PARTITION',
1008 ind_rec.index_name,
1009 ind_part_rec.partition_name);
1010
1011 if l_tot_blocks >= g_threshold_size AND l_parent_exec_mode = 'S'
1012 then
1013 l_parallel := 'PARALLEL';
1014 l_execution_mode := 'S';
1015 end if;
1016 end if;
1017 */
1018 l_string := 'ALTER INDEX "'||ind_rec.owner||'"."'||ind_rec.index_name||'" REBUILD PARTITION '||ind_part_rec.partition_name||' TABLESPACE '||p_tablespace_name||' '||l_storage_str||' '||l_parallel||' '||l_logging_str;
1019 write_out(
1020 p_owner => ind_rec.owner,
1021 p_object_type => p_type,
1025 p_new_tablespace => p_tablespace_name,
1022 p_mig_cmd => l_string,
1023 p_object_name => ind_rec.index_name,
1024 p_old_tablespace => ind_part_rec.tablespace_name,
1026 p_subobject_type => ind_part_rec.partition_name,
1027 p_parent_owner => p_owner,
1028 p_parent_object_name => p_table_name,
1029 p_tot_blocks => l_tot_blocks,
1030 p_index_parallel => 'PARALLEL', -- l_parallel,
1031 p_execution_mode => l_execution_mode,
1032 p_parent_lineno => l_parent_lineno,
1033 p_partitioned => 'YES',
1034 x_lineno => l_lineno1);
1035 l_parent_lineno := l_lineno1;
1036 -- l_parent_exec_mode := l_execution_mode;
1037 l_part := l_part + 1;
1038 l_logging := ind_part_rec.logging;
1039 end if;
1040 END LOOP;
1041 -- Set the Degree of parallelism back to 1 for the Index if it was set
1042 -- to PARALLEL for any partition
1043 /* Mladena */
1044 if l_part>0 then
1045
1046 if ind_rec.degree='1' then
1047 l_string := ' NOPARALLEL';
1048 elsif ind_rec.degree='DEFAULT' then
1049 l_string := 'PARALLEL';
1050 elsif ind_rec.degree>1 then
1051 l_string := 'PARALLEL '||ind_rec.degree;
1052 end if;
1053
1054 if l_logging = 'YES' then
1055 l_string := l_string||' LOGGING';
1056 end if;
1057
1058 l_string := 'ALTER INDEX "'||ind_rec.owner||'"."'
1059 ||ind_rec.index_name||'" '||l_string;
1060
1061 write_out(
1062 p_owner => ind_rec.owner,
1063 p_object_type => p_type,
1064 p_mig_cmd => l_string,
1065 p_object_name => ind_rec.index_name,
1066 p_new_tablespace => p_tablespace_name,
1067 p_parent_owner => ind_rec.owner,
1068 -- p_parent_object_name => ind_rec.index_name,
1069 p_parent_object_name => p_table_name,
1070 p_index_parallel => 'NOPARALLEL',
1071 p_execution_mode => l_execution_mode,
1072 p_parent_lineno => l_lineno1,
1073 p_partitioned => 'YES',
1074 x_lineno => l_lineno2);
1075 l_parent_lineno := l_lineno2;
1076 end if;
1077 -- end if;
1078 if l_def_tablespace_name <> p_tablespace_name then
1079 l_string := 'ALTER INDEX "'||ind_rec.owner||'"."'||ind_rec.index_name||'" MODIFY DEFAULT ATTRIBUTES TABLESPACE '||p_tablespace_name;
1080 write_out(
1081 p_owner => ind_rec.owner,
1082 p_object_type => p_type,
1083 p_mig_cmd => l_string,
1084 p_object_name => ind_rec.index_name,
1085 p_new_tablespace => p_tablespace_name,
1086 p_subobject_type => 'DEFAULT_TSP',
1087 p_parent_owner => ind_rec.owner,
1088 p_parent_object_name => ind_rec.index_name,
1089 p_index_parallel => 'NOPARALLEL',
1090 p_execution_mode => l_execution_mode,
1091 p_parent_lineno => l_parent_lineno,
1092 p_partitioned => 'NO',
1093 x_lineno => l_lineno3);
1094 l_parent_lineno := l_lineno3;
1095 end if;
1096
1097 elsif ind_rec.index_type = 'DOMAIN' AND ind_rec.domidx_opstatus = 'VALID' AND ind_rec.domidx_status = 'VALID'
1098 then
1099 -- All Domain indexes go to transaction indexes tablespace.
1100 l_tablespace_name := fnd_ts_mig_util.get_tablespace_name(fnd_ts_mig_util.l_def_ind_tsp);
1101 if ind_rec.ityp_owner = 'CTXSYS'
1102 then
1103
1104 l_query := 'SELECT 1
1105 FROM ctxsys.ctx_indexes
1106 WHERE idx_owner = :1
1107 AND idx_name = :2
1108 AND idx_status = ''INDEXED''';
1109
1110 OPEN ctx_stat_csr FOR l_query USING ind_rec.owner, ind_rec.index_name;
1111 FETCH ctx_stat_csr INTO l_dummy;
1112 if ctx_stat_csr%FOUND then
1113 if NOT p_tab_moved then
1114 OPEN ctx_csr(ind_rec.owner,
1115 'DR$'||ind_rec.index_name||'$%',
1116 l_tablespace_name);
1117 FETCH ctx_csr INTO ctx_rec;
1118 CLOSE ctx_csr;
1119 end if;
1120 if p_tab_moved OR (ctx_rec.tablespace_name IS NOT NULL) then
1121 l_string := 'ALTER INDEX "'||ind_rec.owner||'"."'||ind_rec.index_name||'" REBUILD parameters (''replace storage '||l_storage_pref||''') ';
1122 write_out(
1123 p_owner => ind_rec.owner,
1124 p_object_type => p_type,
1125 p_mig_cmd => l_string,
1126 p_object_name => ind_rec.index_name,
1127 p_old_tablespace => ind_rec.tablespace_name,
1128 p_new_tablespace => l_tablespace_name,
1129 p_subobject_type => 'INTERMEDIA',
1130 p_parent_owner => p_owner,
1131 p_parent_object_name => p_table_name,
1132 p_execution_mode => l_execution_mode,
1133 p_parent_lineno => l_parent_lineno,
1134 x_lineno => l_lineno1);
1135 l_parent_lineno := l_lineno1;
1136 end if;
1137 end if;
1138 CLOSE ctx_stat_csr;
1139 elsif ind_rec.ityp_owner = 'MDSYS'
1140 then
1141 l_sdo_metadata_table := 'MDSYS.SDO_INDEX_METADATA_TABLE';
1142 l_query := 'SELECT sdo_index_type,
1143 sdo_tsname,
1147 NVL(sdo_rtree_pctfree, 10) sdo_rtree_pctfree,
1144 sdo_index_table,
1145 nvl(sdo_tablespace, ''X'') sdo_tablespace,
1146 NVL(sdo_index_dims, 2) sdo_index_dims,
1148 sdo_commit_interval,
1149 sdo_level,
1150 sdo_numtiles
1151 FROM '||l_sdo_metadata_table||'
1152 WHERE sdo_index_owner = :1
1153 AND sdo_index_name = :2';
1154 OPEN sdo_csr FOR l_query USING ind_rec.owner, ind_rec.index_name;
1155 LOOP
1156 FETCH sdo_csr INTO sdo_rec;
1157 EXIT WHEN sdo_csr%NOTFOUND;
1158 if p_tab_moved OR (sdo_rec.sdo_tablespace <> l_tablespace_name) then
1159 if sdo_rec.sdo_index_type = 'RTREE' then
1160 l_sdo_params := 'rebuild_index='||sdo_rec.sdo_index_table||' sdo_indx_dims='||sdo_rec.sdo_index_dims||' sdo_rtr_pctfree='||sdo_rec.sdo_rtree_pctfree||' tablespace='||l_tablespace_name;
1161 elsif sdo_rec.sdo_index_type = 'QTREE' then
1162 l_sdo_params := 'rebuild_index='||sdo_rec.sdo_index_table||' sdo_commit_interval='||sdo_rec.sdo_commit_interval||' sdo_level='||sdo_rec.sdo_level||' sdo_numtiles='||sdo_rec.sdo_numtiles||' tablespace='||l_tablespace_name;
1163 end if;
1164 l_string := 'ALTER INDEX "'||ind_rec.owner||'"."'||ind_rec.index_name||'" REBUILD parameters ('''||l_sdo_params||''')';
1165 write_out(
1166 p_owner => ind_rec.owner,
1167 p_object_type => p_type,
1168 p_mig_cmd => l_string,
1169 p_object_name => ind_rec.index_name,
1170 p_old_tablespace => ind_rec.tablespace_name,
1171 p_new_tablespace => l_tablespace_name,
1172 p_subobject_type => sdo_rec.sdo_index_table,
1173 p_parent_owner => p_owner,
1174 p_parent_object_name => p_table_name,
1175 p_execution_mode => l_execution_mode,
1176 p_parent_lineno => l_parent_lineno,
1177 x_lineno => l_lineno1);
1178 l_parent_lineno := l_lineno1;
1179 end if;
1180 END LOOP;
1181 CLOSE sdo_csr;
1182 end if;
1183 end if;
1184 END LOOP;
1185 END gen_rebuild_idx;
1186
1187 FUNCTION get_iot_tablespace(p_owner IN VARCHAR2,
1188 p_iot_name IN VARCHAR2)
1189 RETURN VARCHAR2 IS
1190 CURSOR iot_ind_csr IS
1191 SELECT tablespace_name
1192 FROM dba_indexes
1193 WHERE table_owner = p_owner
1194 AND table_name = p_iot_name
1195 AND index_type = 'IOT - TOP';
1196 l_tablespace_name VARCHAR2(30);
1197 BEGIN
1198 OPEN iot_ind_csr;
1199 FETCH iot_ind_csr INTO l_tablespace_name;
1200 CLOSE iot_ind_csr;
1201
1202 RETURN l_tablespace_name;
1203 END get_iot_tablespace;
1204
1205
1206 PROCEDURE gen_move_aqs (p_owner IN VARCHAR2)
1207 IS
1208 TYPE AQRecTabType IS RECORD
1209 (owner CharTabType,
1210 queue_table CharTabType,
1211 tablespace_type CharTabType,
1212 new_tablespace CharTabType,
1213 tablespace_name CharTabType,
1214 iot_type CharTabType,
1215 partitioned CharTabType,
1216 logging CharTabType);
1217 aq_rec_tab AQRecTabType;
1218
1219 CURSOR aq_csr IS
1220 SELECT /*+ RULE */ dqt.owner owner,
1221 dqt.queue_table queue_table,
1222 fnd_ts_mig_util.l_aq_tab_tsp tablespace_type,
1223 ft.tablespace new_tablespace,
1224 dt.tablespace_name tablespace_name,
1225 dt.iot_type iot_type,
1226 dt.partitioned partitioned,
1227 dt.logging logging
1228 FROM dba_queue_tables dqt,
1229 dba_tables dt,
1230 fnd_tablespaces ft
1231 WHERE dqt.owner = p_owner
1232 AND dqt.owner = dt.owner
1233 AND dqt.queue_table = dt.table_name
1234 AND ft.tablespace_type = fnd_ts_mig_util.l_aq_tab_tsp
1235 AND dt.table_name NOT LIKE 'BIN$%'
1236 AND NVL(dt.temporary, 'N') = 'N';
1237
1238 qry VARCHAR2(4000);
1239 TYPE child_aq_csr_type is REF CURSOR;
1240 child_aq_csr child_aq_csr_type;
1241
1242 TYPE child_aq_rec_type IS RECORD (
1243 owner VARCHAR2(30),
1244 table_name VARCHAR2(30),
1245 tablespace_name VARCHAR2(30),
1246 iot_type VARCHAR2(12),
1247 partitioned VARCHAR2(3),
1248 logging VARCHAR2(3));
1249 child_aq_rec child_aq_rec_type;
1250 l_tab_moved BOOLEAN := FALSE;
1251 l_lineno NUMBER;
1252 l_child_lineno NUMBER;
1253 l_execution_mode FND_TS_MIG_CMDS.EXECUTION_MODE%TYPE;
1254
1255 BEGIN
1256 OPEN aq_csr;
1257 LOOP
1258 aq_rec_tab.owner.DELETE;
1259 FETCH aq_csr BULK COLLECT INTO
1260 aq_rec_tab.owner, aq_rec_tab.queue_table, aq_rec_tab.tablespace_type,
1261 aq_rec_tab.new_tablespace, aq_rec_tab.tablespace_name,
1262 aq_rec_tab.iot_type, aq_rec_tab.partitioned, aq_rec_tab.logging LIMIT 1000;
1263 EXIT WHEN aq_rec_tab.owner.COUNT = 0;
1264 FOR i IN aq_rec_tab.owner.FIRST..aq_rec_tab.owner.LAST
1265 LOOP
1266 l_tab_moved := FALSE;
1267 l_lineno := NULL;
1268 l_execution_mode := NULL;
1269
1270 if aq_rec_tab.tablespace_name(i) <> aq_rec_tab.new_tablespace(i) OR
1271 NVL(aq_rec_tab.partitioned(i), 'NO') = 'YES'
1272 then
1276 p_obj_name => aq_rec_tab.queue_table(i),
1273 gen_move_obj ( p_owner => aq_rec_tab.owner(i),
1274 p_obj_type => 'TABLE',
1275 p_sub_obj_type => aq_rec_tab.iot_type(i),
1277 p_partitioned => aq_rec_tab.partitioned(i),
1278 p_logging => aq_rec_tab.logging(i),
1279 p_old_tablespace => aq_rec_tab.tablespace_name(i),
1280 p_new_tablespace => aq_rec_tab.new_tablespace(i),
1281 p_parent_owner => NULL,
1282 p_parent_obj_name => NULL,
1283 p_parent_lineno => NULL,
1284 x_execution_mode => l_execution_mode,
1285 x_lineno => l_lineno);
1286 l_tab_moved := TRUE;
1287 end if;
1288
1289 gen_rebuild_idx( p_owner => aq_rec_tab.owner(i),
1290 p_table_name => aq_rec_tab.queue_table(i),
1291 p_parent_obj_type => 'TABLE',
1292 p_tab_moved => l_tab_moved,
1293 p_tablespace_name => aq_rec_tab.new_tablespace(i),
1294 p_parent_lineno => l_lineno,
1295 p_execution_mode => l_execution_mode,
1296 p_type => 'INDEX');
1297
1298 -- Move all the dependent AQ tables
1299 qry := ' SELECT owner,
1300 table_name,
1301 tablespace_name,
1302 iot_type,
1303 partitioned,
1304 logging
1305 FROM dba_tables
1306 WHERE owner = :1
1307 AND NVL(temporary, ''N'') = ''N''
1308 AND table_name like ''AQ$_''||:2||''%''';
1309 OPEN child_aq_csr FOR qry USING aq_rec_tab.owner(i), aq_rec_tab.queue_table(i);
1310 LOOP
1311 FETCH child_aq_csr INTO child_aq_rec;
1312 EXIT WHEN child_aq_csr%NOTFOUND;
1313 l_tab_moved := FALSE;
1314 l_child_lineno := NULL;
1315 l_execution_mode := NULL;
1316
1317 if NVL(child_aq_rec.iot_type, 'X') = 'IOT' then
1318 child_aq_rec.tablespace_name := get_iot_tablespace(child_aq_rec.owner,
1319 child_aq_rec.table_name);
1320 end if;
1321
1322 if child_aq_rec.tablespace_name <> aq_rec_tab.new_tablespace(i) OR
1323 NVL(child_aq_rec.partitioned, 'NO') = 'YES'
1324 then
1325 gen_move_obj ( p_owner => aq_rec_tab.owner(i),
1326 p_obj_type => 'TABLE',
1327 p_sub_obj_type => child_aq_rec.iot_type,
1328 p_obj_name => child_aq_rec.table_name,
1329 p_partitioned => child_aq_rec.partitioned,
1330 p_logging => child_aq_rec.logging,
1331 p_old_tablespace => child_aq_rec.tablespace_name,
1332 p_new_tablespace => aq_rec_tab.new_tablespace(i),
1333 p_parent_owner => NULL,
1334 p_parent_obj_name => NULL,
1335 p_parent_lineno => NULL,
1336 x_execution_mode => l_execution_mode,
1337 x_lineno => l_child_lineno);
1338 l_tab_moved := TRUE;
1339 end if;
1340
1341 gen_rebuild_idx( p_owner => child_aq_rec.owner,
1342 p_table_name => child_aq_rec.table_name,
1343 p_parent_obj_type => 'TABLE',
1344 p_tab_moved => l_tab_moved,
1345 p_tablespace_name => aq_rec_tab.new_tablespace(i),
1346 p_parent_lineno => l_child_lineno,
1347 p_execution_mode => l_execution_mode,
1348 p_type => 'INDEX');
1349 END LOOP;
1350 CLOSE child_aq_csr;
1351
1352 END LOOP;
1353 END LOOP;
1354 CLOSE aq_csr;
1355
1356 END gen_move_aqs;
1357
1358
1359 PROCEDURE gen_move_mvlogs (p_owner IN VARCHAR2,
1360 p_table_name IN VARCHAR2,
1361 x_parent_obj_name OUT NOCOPY VARCHAR2,
1362 p_lineno IN NUMBER,
1363 x_lineno OUT NOCOPY NUMBER,
1364 p_type IN VARCHAR2 DEFAULT 'MV_LOG')
1365 IS
1366 TYPE MVLogRecTabType IS RECORD
1367 (log_owner CharTabType,
1368 master CharTabType,
1369 log_table CharTabType,
1370 tablespace_type CharTabType,
1371 new_tablespace CharTabType,
1372 tablespace_name CharTabType,
1373 iot_type CharTabType,
1374 partitioned CharTabType,
1375 logging CharTabType);
1376 mvlog_rec_tab MVLogRecTabType;
1377
1378 CURSOR mvlog_csr IS
1379 SELECT /*+ RULE */ distinct dsl.log_owner log_owner,
1380 dsl.master master,
1381 dsl.log_table,
1382 fnd_ts_mig_util.l_def_mv_tsp tablespace_type,
1383 ft.tablespace new_tablespace,
1384 dt.tablespace_name,
1385 dt.iot_type,
1386 dt.partitioned,
1387 dt.logging
1388 FROM dba_snapshot_logs dsl,
1389 dba_tables dt,
1390 fnd_tablespaces ft
1391 WHERE dsl.log_owner = p_owner
1392 AND dsl.master = p_table_name
1393 AND dsl.log_owner = dt.owner
1394 AND dsl.log_table = dt.table_name
1395 AND ft.tablespace_type = fnd_ts_mig_util.l_def_mv_tsp
1396 AND dt.table_name NOT LIKE 'BIN$%'
1397 AND NVL(dt.temporary, 'N') = 'N';
1398
1399 l_tab_moved BOOLEAN := FALSE;
1400 l_string VARCHAR2(4000);
1401 l_lineno NUMBER;
1405 OPEN mvlog_csr;
1402 l_execution_mode FND_TS_MIG_CMDS.EXECUTION_MODE%TYPE;
1403
1404 BEGIN
1406 LOOP
1407 mvlog_rec_tab.log_owner.DELETE;
1408 FETCH mvlog_csr BULK COLLECT INTO
1409 mvlog_rec_tab.log_owner, mvlog_rec_tab.master, mvlog_rec_tab.log_table,
1410 mvlog_rec_tab.tablespace_type, mvlog_rec_tab.new_tablespace,
1411 mvlog_rec_tab.tablespace_name, mvlog_rec_tab.iot_type,
1412 mvlog_rec_tab.partitioned, mvlog_rec_tab.logging LIMIT 1000;
1413 EXIT WHEN mvlog_rec_tab.log_owner.COUNT = 0;
1414 FOR i IN mvlog_rec_tab.log_owner.FIRST..mvlog_rec_tab.log_owner.LAST
1415 LOOP
1416 l_tab_moved := FALSE;
1417 l_lineno := NULL;
1418 l_execution_mode := NULL;
1419
1420 if mvlog_rec_tab.tablespace_name(i) <> mvlog_rec_tab.new_tablespace(i)
1421 then
1422 gen_move_obj ( p_owner => mvlog_rec_tab.log_owner(i),
1423 p_obj_type => p_type,
1424 p_sub_obj_type => mvlog_rec_tab.iot_type(i),
1425 p_obj_name => mvlog_rec_tab.master(i),
1426 p_partitioned => mvlog_rec_tab.partitioned(i),
1427 p_logging => mvlog_rec_tab.logging(i),
1428 p_old_tablespace => mvlog_rec_tab.tablespace_name(i),
1429 p_new_tablespace => mvlog_rec_tab.new_tablespace(i),
1430 p_parent_owner => mvlog_rec_tab.log_owner(i),
1431 p_parent_obj_name => mvlog_rec_tab.log_table(i),
1432 p_parent_lineno => p_lineno,
1433 x_execution_mode => l_execution_mode,
1434 x_lineno => l_lineno);
1435 l_tab_moved := TRUE;
1436 end if;
1437
1438 -- Rebuild all the indexes on the MV log, if any, in the new tablespace
1439 gen_rebuild_idx( p_owner => mvlog_rec_tab.log_owner(i),
1440 p_table_name => mvlog_rec_tab.log_table(i),
1441 p_parent_obj_type => p_type,
1442 p_tab_moved => l_tab_moved,
1443 p_tablespace_name => mvlog_rec_tab.new_tablespace(i),
1444 p_parent_lineno => l_lineno,
1445 p_execution_mode => l_execution_mode,
1446 p_type => 'INDEX');
1447 x_parent_obj_name := mvlog_rec_tab.log_table(i);
1448 x_lineno := l_lineno;
1449 END LOOP;
1450 END LOOP;
1451 CLOSE mvlog_csr;
1452
1453 END gen_move_mvlogs;
1454
1455 PROCEDURE gen_move_mvs (p_owner IN VARCHAR2)
1456 IS
1457 TYPE MVRecTabType IS RECORD
1458 (owner CharTabType,
1459 name CharTabType,
1460 table_name CharTabType,
1461 tablespace_type CharTabType,
1462 new_tablespace CharTabType,
1463 tablespace_name CharTabType,
1464 iot_type CharTabType,
1465 partitioned CharTabType,
1466 logging CharTabType);
1467 mv_rec_tab MVRecTabType;
1468
1469 CURSOR mv_csr IS
1470 SELECT /*+ RULE */ ds.owner,
1471 ds.name,
1472 ds.table_name,
1473 fnd_ts_mig_util.l_def_mv_tsp tablespace_type,
1474 ft.tablespace new_tablespace,
1475 dt.tablespace_name,
1476 dt.iot_type,
1477 dt.partitioned,
1478 dt.logging
1479 FROM dba_snapshots ds,
1480 dba_tables dt,
1481 fnd_tablespaces ft
1482 WHERE ds.owner = p_owner
1483 AND ds.owner = dt.owner
1484 AND ds.table_name = dt.table_name
1485 AND dt.cluster_name IS NULL
1486 AND ft.tablespace_type = fnd_ts_mig_util.l_def_mv_tsp
1487 AND dt.table_name NOT LIKE 'BIN$%'
1488 AND NVL(dt.temporary, 'N') = 'N';
1489
1490 CURSOR part_tsp_csr(l_owner VARCHAR2, l_table_name VARCHAR2, l_tablespace_name VARCHAR2) IS
1491 SELECT '1'
1492 FROM dba_tab_partitions
1493 WHERE table_owner = l_owner
1494 AND table_name = l_table_name
1495 AND tablespace_name <> l_tablespace_name;
1496 l_dummy VARCHAR2(1);
1497
1498 l_tab_moved BOOLEAN := FALSE;
1499 l_string VARCHAR2(4000);
1500 l_parent_lineno NUMBER;
1501 l_lineno NUMBER;
1502 l_parent_owner FND_TS_MIG_CMDS.PARENT_OWNER%TYPE;
1503 l_parent_obj_name FND_TS_MIG_CMDS.PARENT_OBJECT_NAME%TYPE;
1504 l_execution_mode FND_TS_MIG_CMDS.EXECUTION_MODE%TYPE;
1505
1506 BEGIN
1507 OPEN mv_csr;
1508 LOOP
1509 mv_rec_tab.owner.DELETE;
1510 FETCH mv_csr BULK COLLECT INTO
1511 mv_rec_tab.owner, mv_rec_tab.name, mv_rec_tab.table_name,
1512 mv_rec_tab.tablespace_type, mv_rec_tab.new_tablespace,
1513 mv_rec_tab.tablespace_name, mv_rec_tab.iot_type,
1514 mv_rec_tab.partitioned, mv_rec_tab.logging LIMIT 1000;
1515 EXIT WHEN mv_rec_tab.owner.COUNT = 0;
1516 FOR i IN mv_rec_tab.owner.FIRST..mv_rec_tab.owner.LAST
1517 LOOP
1518 l_tab_moved := FALSE;
1519 l_lineno := NULL;
1520 l_parent_lineno := NULL;
1521 l_parent_owner := NULL;
1522 l_parent_obj_name := NULL;
1523 l_execution_mode := NULL;
1524
1525 if NVL(mv_rec_tab.partitioned(i), 'NO') = 'YES' then
1526 -- Check if any partition needs to be moved
1527 OPEN part_tsp_csr(mv_rec_tab.owner(i), mv_rec_tab.table_name(i), mv_rec_tab.new_tablespace(i));
1528 FETCH part_tsp_csr INTO l_dummy;
1529 if part_tsp_csr%FOUND then
1530 l_tab_moved := TRUE;
1531 end if;
1532 CLOSE part_tsp_csr;
1533 end if;
1534
1535 -- Move any MV Logs on the MV in the new tablespace
1536 gen_move_mvlogs (p_owner => mv_rec_tab.owner(i),
1537 p_table_name => mv_rec_tab.table_name(i),
1541 p_type => 'MV_LOG');
1538 x_parent_obj_name => l_parent_obj_name,
1539 p_lineno => NULL,
1540 x_lineno => l_parent_lineno,
1542 if l_parent_obj_name IS NOT NULL then
1543 l_parent_owner := mv_rec_tab.owner(i);
1544 end if;
1545
1546 if mv_rec_tab.tablespace_name(i) <> mv_rec_tab.new_tablespace(i) OR
1547 l_tab_moved
1548 then
1549 gen_move_obj ( p_owner => mv_rec_tab.owner(i),
1550 p_obj_type => 'MVIEW',
1551 p_sub_obj_type => mv_rec_tab.iot_type(i),
1552 p_obj_name => mv_rec_tab.table_name(i),
1553 p_partitioned => mv_rec_tab.partitioned(i),
1554 p_logging => mv_rec_tab.logging(i),
1555 p_old_tablespace => mv_rec_tab.tablespace_name(i),
1556 p_new_tablespace => mv_rec_tab.new_tablespace(i),
1557 p_parent_owner => l_parent_owner,
1558 p_parent_obj_name => l_parent_obj_name,
1559 p_parent_lineno => l_parent_lineno,
1560 x_execution_mode => l_execution_mode,
1561 x_lineno => l_lineno);
1562 l_tab_moved := TRUE;
1563 end if;
1564
1565 -- Rebuild all the indexes on the MV in the new tablespace
1566 gen_rebuild_idx( p_owner => mv_rec_tab.owner(i),
1567 p_table_name => mv_rec_tab.table_name(i),
1568 p_parent_obj_type => 'MVIEW',
1569 p_tab_moved => l_tab_moved,
1570 p_tablespace_name => mv_rec_tab.new_tablespace(i),
1571 p_parent_lineno => l_lineno,
1572 p_execution_mode => l_execution_mode,
1573 p_type => 'INDEX');
1574
1575 END LOOP;
1576 END LOOP;
1577 CLOSE mv_csr;
1578
1579 END gen_move_mvs;
1580
1581 PROCEDURE gen_truncate_tab( p_owner IN VARCHAR2,
1582 p_table_name IN VARCHAR2,
1583 p_new_tablespace IN VARCHAR2,
1584 x_lineno OUT NOCOPY NUMBER)
1585 IS
1586 l_string VARCHAR2(4000);
1587 l_lineno NUMBER;
1588 BEGIN
1589 l_string := 'TRUNCATE TABLE "'||p_owner||'"."'||p_table_name||'"';
1590 write_out(
1591 p_owner => p_owner,
1592 p_object_type => 'LONG_TABLE',
1593 p_mig_cmd => l_string,
1594 p_object_name => p_table_name,
1595 p_new_tablespace => p_new_tablespace,
1596 p_subobject_type => 'TRUNCATE',
1597 p_execution_mode => 'P',
1598 x_lineno => l_lineno);
1599 x_lineno := l_lineno;
1600 END gen_truncate_tab;
1601
1602 PROCEDURE get_long_col( p_owner IN VARCHAR2,
1603 p_table_name IN VARCHAR2,
1604 x_col_name OUT NOCOPY VARCHAR2,
1605
1606 x_data_type OUT NOCOPY VARCHAR2)
1607 IS
1608 CURSOR long_col_csr IS
1609 SELECT column_name,
1610 data_type
1611 FROM dba_tab_columns
1612 WHERE owner = p_owner
1613 AND table_name = p_table_name
1614 AND data_type IN ('LONG', 'LONG RAW');
1615 BEGIN
1616 OPEN long_col_csr;
1617 FETCH long_col_csr INTO x_col_name, x_data_type;
1618 CLOSE long_col_csr;
1619 END get_long_col;
1620
1621 PROCEDURE gen_alter_tab( p_owner IN VARCHAR2,
1622 p_table_name IN VARCHAR2,
1623 p_alter_type IN VARCHAR2,
1624 p_col_list IN VARCHAR2,
1625 p_new_tablespace IN VARCHAR2,
1626 p_parent_lineno IN NUMBER,
1627 x_lineno OUT NOCOPY NUMBER)
1628 IS
1629 l_string VARCHAR2(4000);
1630 l_lineno NUMBER;
1631 BEGIN
1632 l_string := 'ALTER TABLE "'||p_owner||'"."'||p_table_name||'" '||p_alter_type||' ('||p_col_list||')';
1633 write_out(
1634 p_owner => p_owner,
1635 p_object_type => 'LONG_TABLE',
1636 p_mig_cmd => l_string,
1637 p_object_name => p_table_name,
1638 p_new_tablespace => p_new_tablespace,
1639 p_subobject_type => p_alter_type,
1640 p_execution_mode => 'P',
1641 p_parent_object_name => p_table_name,
1642 p_parent_lineno => p_parent_lineno,
1643 x_lineno => l_lineno);
1644 x_lineno := l_lineno;
1645 END gen_alter_tab;
1646
1647
1648 PROCEDURE gen_move_longs (p_owner IN VARCHAR2,
1649 p_threshold_size IN NUMBER DEFAULT NULL)
1650 IS
1651 CURSOR userid_csr IS
1652 SELECT oracle_username
1653 FROM fnd_oracle_userid
1654 WHERE oracle_username = p_owner
1655 AND read_only_flag IN ('E', 'A', 'U', 'M', 'K');
1656 l_schema VARCHAR2(30);
1657
1658 TYPE TabRecTabType IS RECORD
1659 (owner CharTabType,
1660 table_name CharTabType,
1661 tablespace_type CharTabType,
1662 new_tablespace CharTabType,
1663 tablespace_name CharTabType,
1664 iot_type CharTabType,
1665 partitioned CharTabType,
1666 logging CharTabType);
1667 tab_rec_tab TabRecTabType;
1668
1669 CURSOR tab_csr IS
1670 SELECT dt.owner,
1671 dt.table_name,
1672 NVL(fot.custom_tablespace_type, fot.tablespace_type) tablespace_type,
1673 ft.tablespace new_tablespace,
1674 dt.tablespace_name,
1675 dt.iot_type,
1676 dt.partitioned,
1680 fnd_tablespaces ft
1677 dt.logging
1678 FROM dba_tables dt,
1679 fnd_object_tablespaces fot,
1681 WHERE dt.owner = p_owner
1682 AND dt.owner = fot.oracle_username
1683 AND dt.table_name = fot.object_name
1684 AND NVL(fot.custom_tablespace_type, fot.tablespace_type) = ft.tablespace_type
1685 AND fot.object_type = 'TABLE'
1686 AND dt.cluster_name IS NULL
1687 AND dt.table_name NOT LIKE 'BIN$%'
1688 AND EXISTS ( select dtc.table_name
1689 from dba_tab_columns dtc
1690 where dtc.owner = p_owner
1691 and dtc.table_name = dt.table_name
1692 and dtc.data_type in ('LONG', 'LONG RAW'))
1693 UNION ALL
1694 -- all unclassified tables go to TRANSACTION_TABLE tablespace
1695 SELECT dt.owner,
1696 dt.table_name,
1697 fnd_ts_mig_util.l_unclass_tsp tablespace_type,
1698 ft.tablespace new_tablespace,
1699 dt.tablespace_name,
1700 dt.iot_type,
1701 dt.partitioned,
1702 dt.logging
1703 FROM dba_tables dt,
1704 fnd_tablespaces ft
1705 WHERE dt.owner = p_owner
1706 AND ft.tablespace_type = fnd_ts_mig_util.l_unclass_tsp
1707 AND dt.cluster_name IS NULL
1708 AND dt.table_name NOT LIKE 'BIN$%'
1709 AND EXISTS ( select dtc.table_name
1710 from dba_tab_columns dtc
1711 where dtc.owner = p_owner
1712 and dtc.table_name = dt.table_name
1713 and dtc.data_type in ('LONG', 'LONG RAW'))
1714 AND NOT EXISTS ( SELECT object_name
1715 FROM fnd_object_tablespaces fot
1716 WHERE fot.oracle_username = p_owner
1717 AND fot.object_type = 'TABLE'
1718 AND fot.object_name = dt.table_name);
1719
1720 CURSOR c1 IS
1721 SELECT fnd_ts_mig_cmds_s.nextval from dual;
1722 l_seq NUMBER;
1723 l_stag_tab_name VARCHAR2(30);
1724
1725 l_idx_tablespace VARCHAR2(30);
1726 l_txn_idx_tablespace VARCHAR2(30) := get_txn_idx_tablespace;
1727 l_long_col_name VARCHAR2(30);
1728 l_long_data_type VARCHAR2(30);
1729 l_string VARCHAR2(4000);
1730 l_tab_moved BOOLEAN := FALSE;
1731 l_lineno1 NUMBER;
1732 l_lineno2 NUMBER;
1733 l_lineno3 NUMBER;
1734 l_lineno4 NUMBER;
1735 l_lineno5 NUMBER;
1736 l_lineno6 NUMBER;
1737 l_execution_mode FND_TS_MIG_CMDS.EXECUTION_MODE%TYPE;
1738 l_parent_owner VARCHAR2(30);
1739 l_parent_obj_name VARCHAR2(30);
1740
1741 BEGIN
1742 g_threshold_size := p_threshold_size;
1743 -- Null threshold signifies all PARALLEL.
1744
1745 OPEN userid_csr;
1746 FETCH userid_csr INTO l_schema;
1747 if userid_csr%NOTFOUND then
1748 raise_application_error(-20001, 'Schema '||p_owner||' is invalid for migration');
1749 end if;
1750 CLOSE userid_csr;
1751
1752
1753 OPEN tab_csr;
1754 LOOP
1755 tab_rec_tab.owner.DELETE;
1756 FETCH tab_csr BULK COLLECT INTO
1757 tab_rec_tab.owner, tab_rec_tab.table_name,
1758 tab_rec_tab.tablespace_type, tab_rec_tab.new_tablespace,
1759 tab_rec_tab.tablespace_name, tab_rec_tab.iot_type,
1760 tab_rec_tab.partitioned, tab_rec_tab.logging LIMIT 1000;
1761 EXIT WHEN tab_rec_tab.owner.COUNT = 0;
1762 FOR i IN tab_rec_tab.owner.FIRST..tab_rec_tab.owner.LAST
1763 LOOP
1764 l_tab_moved := FALSE;
1765 l_lineno1 := NULL;
1766 l_lineno2 := NULL;
1767 l_lineno3 := NULL;
1768 l_lineno4 := NULL;
1769 l_lineno5 := NULL;
1770 l_lineno6 := NULL;
1771 l_execution_mode := NULL;
1772
1773 l_parent_owner := NULL;
1774 l_parent_obj_name:= NULL;
1775
1776 if tab_rec_tab.tablespace_name(i) <> tab_rec_tab.new_tablespace(i) then
1777 OPEN c1;
1778 FETCH c1 INTO l_seq;
1779 CLOSE c1;
1780
1781 l_stag_tab_name := SUBSTR(tab_rec_tab.table_name(i), 1, 30-LENGTH(TO_CHAR(l_seq)))||TO_CHAR(l_seq);
1782 write_out(
1783 p_owner => tab_rec_tab.owner(i),
1784 p_object_type => 'LONG_TABLE',
1785 p_mig_cmd => l_stag_tab_name,
1786 p_object_name => tab_rec_tab.table_name(i),
1787 p_new_tablespace => tab_rec_tab.new_tablespace(i),
1788 p_subobject_type => 'COPY_TO_STAGE',
1789 p_execution_mode => 'P',
1790 x_lineno => l_lineno1);
1791
1792 get_long_col( tab_rec_tab.owner(i),
1793 tab_rec_tab.table_name(i),
1794 l_long_col_name,
1795 l_long_data_type);
1796
1797 -- Check to see if there is any index on this LONG col and get its DDL
1798 -- Drop the index on LONG col (TBD)
1799
1800 gen_alter_tab( tab_rec_tab.owner(i),
1801 tab_rec_tab.table_name(i),
1802 'DROP',
1803 l_long_col_name,
1804 tab_rec_tab.new_tablespace(i),
1805 l_lineno1,
1806 l_lineno2);
1807
1808 -- move the table to new tablespace
1809 gen_move_obj ( p_owner => tab_rec_tab.owner(i),
1810 p_obj_type => 'LONG_TABLE',
1811 p_sub_obj_type => tab_rec_tab.iot_type(i),
1812 p_obj_name => tab_rec_tab.table_name(i),
1813 p_partitioned => tab_rec_tab.partitioned(i),
1817 p_parent_owner => tab_rec_tab.owner(i),
1814 p_logging => tab_rec_tab.logging(i),
1815 p_old_tablespace => tab_rec_tab.tablespace_name(i),
1816 p_new_tablespace => tab_rec_tab.new_tablespace(i),
1818 p_parent_obj_name => tab_rec_tab.table_name(i),
1819 p_parent_lineno => l_lineno2,
1820 x_execution_mode => l_execution_mode,
1821 x_lineno => l_lineno3);
1822
1823 -- Add the long column back to the table
1824 gen_alter_tab( tab_rec_tab.owner(i),
1825 tab_rec_tab.table_name(i),
1826 'ADD',
1827 l_long_col_name||' '||l_long_data_type,
1828 tab_rec_tab.new_tablespace(i),
1829 l_lineno3,
1830 l_lineno4);
1831
1832 write_out(
1833 p_owner => tab_rec_tab.owner(i),
1834 p_object_type => 'LONG_TABLE',
1835 p_mig_cmd => l_stag_tab_name,
1836 p_object_name => tab_rec_tab.table_name(i),
1837 p_new_tablespace => tab_rec_tab.new_tablespace(i),
1838 p_subobject_type => 'COPY_FROM_STAGE',
1839 p_parent_lineno => l_lineno4,
1840 p_execution_mode => 'P',
1841 x_lineno => l_lineno5);
1842
1843 -- Create the index on LONG col, if any(TBD)
1844
1845 l_tab_moved := TRUE;
1846 end if;
1847
1848 -- 02/25/05 Check to see if there are any MV logs on Long Table and if present, truncate them and move them to the correct tablespace
1849 gen_move_mvlogs (p_owner => tab_rec_tab.owner(i),
1850 p_table_name => tab_rec_tab.table_name(i),
1851 x_parent_obj_name => l_parent_obj_name,
1852 p_lineno => l_lineno5,
1853 x_lineno => l_lineno6,
1854 p_type => 'LONG_MVLOG');
1855
1856 l_idx_tablespace := get_idx_tablespace(tab_rec_tab.tablespace_type(i),
1857 tab_rec_tab.new_tablespace(i),
1858 l_txn_idx_tablespace);
1859
1860 -- Rebuild all the indexes on the table in the new tablespace
1861 gen_rebuild_idx( p_owner => tab_rec_tab.owner(i),
1862 p_table_name => tab_rec_tab.table_name(i),
1863 p_parent_obj_type => 'LONG_TABLE',
1864 p_tab_moved => l_tab_moved,
1865 p_tablespace_name => l_idx_tablespace,
1866 p_parent_lineno => l_lineno5,
1867 p_execution_mode => l_execution_mode,
1868 p_type => 'LONG_INDEX');
1869
1870 END LOOP;
1871 END LOOP;
1872 CLOSE tab_csr;
1873
1874 END gen_move_longs;
1875
1876
1877 PROCEDURE gen_move_tabs (p_owner IN VARCHAR2)
1878 IS
1879 TYPE TabRecTabType IS RECORD
1880 (owner CharTabType,
1881 table_name CharTabType,
1882 tablespace_type CharTabType,
1883 new_tablespace CharTabType,
1884 tablespace_name CharTabType,
1885 iot_type CharTabType,
1886 partitioned CharTabType,
1887 logging CharTabType);
1888 tab_rec_tab TabRecTabType;
1889
1890 CURSOR tab_csr IS
1891 -- Classified tables only
1892 SELECT dt.owner,
1893 dt.table_name,
1894 NVL(fot.custom_tablespace_type, fot.tablespace_type) tablespace_type,
1895 ft.tablespace new_tablespace,
1896 dt.tablespace_name,
1897 dt.iot_type,
1898 dt.partitioned,
1899 dt.logging
1900 FROM dba_tables dt,
1901 fnd_object_tablespaces fot,
1902 fnd_tablespaces ft
1903 WHERE dt.owner = p_owner
1904 AND dt.owner = fot.oracle_username
1905 AND dt.table_name = fot.object_name
1906 AND NVL(fot.custom_tablespace_type, fot.tablespace_type) = ft.tablespace_type
1907 AND fot.object_type = 'TABLE'
1908 AND NVL(dt.temporary, 'N') = 'N'
1909 AND dt.cluster_name IS NULL
1910 AND NOT EXISTS ( select dtc.table_name
1911 from dba_tab_columns dtc
1912 where dtc.owner = p_owner
1913 and dtc.table_name = dt.table_name
1914 and dtc.data_type in ('LONG', 'LONG RAW'))
1915 AND NOT EXISTS ( select ds.table_name
1916 from dba_snapshots ds
1917 where ds.owner = p_owner
1918 and ds.table_name = dt.table_name)
1919 AND NOT EXISTS ( select dsl.log_table
1920 from dba_snapshot_logs dsl
1921 where dsl.log_owner = p_owner
1922 and dsl.log_table = dt.table_name)
1923 AND NOT EXISTS ( select dqt.queue_table
1924 from dba_queue_tables dqt
1925 where dqt.owner = p_owner
1926 and dqt.queue_table = dt.table_name)
1927 AND NOT EXISTS ( select det.table_name
1928 from dba_external_tables det
1929 where det.owner = p_owner
1930 and det.table_name = dt.table_name)
1931 AND dt.table_name NOT LIKE 'AQ$%' -- tables for AQ tables
1932 AND (dt.table_name NOT LIKE 'DR$%' -- tables for INTERMEDIA indexes
1933 OR dt.owner = 'CTXSYS')
1934 AND dt.table_name NOT LIKE 'RUPD$%' -- tables for snapshot logs
1935 AND dt.table_name NOT LIKE 'MDRT%$' -- tables for SPATIAL indexes
1936 AND dt.table_name NOT LIKE 'BIN$%'
1937 AND dt.nested='NO'
1938 UNION ALL
1942 dt.table_name,
1939 -- all unclassified tables go to TRANSACTION_TABLES tablespace
1940 -- Not IOTs, AQs, Domain Index tables, MVs, MV logs
1941 SELECT dt.owner,
1943 fnd_ts_mig_util.l_unclass_tsp tablespace_type,
1944 ft.tablespace new_tablespace,
1945 dt.tablespace_name,
1946 dt.iot_type,
1947 dt.partitioned,
1948 dt.logging
1949 FROM dba_tables dt,
1950 fnd_tablespaces ft
1951 WHERE dt.owner = p_owner
1952 AND ft.tablespace_type = fnd_ts_mig_util.l_unclass_tsp
1953 AND NOT EXISTS ( SELECT object_name
1954 FROM fnd_object_tablespaces fot
1955 WHERE fot.oracle_username = p_owner
1956 AND fot.object_type = 'TABLE'
1957 AND fot.object_name = dt.table_name)
1958 AND NVL(dt.temporary, 'N') = 'N'
1959 AND NVL(dt.iot_type, 'X') NOT IN ('IOT', 'IOT_OVERFLOW')
1960 AND dt.cluster_name IS NULL
1961 AND NOT EXISTS ( select dtc.table_name
1962 from dba_tab_columns dtc
1963 where dtc.owner = p_owner
1964 and dtc.table_name = dt.table_name
1965 and dtc.data_type in ('LONG', 'LONG RAW'))
1966 AND NOT EXISTS ( select ds.table_name
1967 from dba_snapshots ds
1968 where ds.owner = p_owner
1969 and ds.table_name = dt.table_name)
1970 AND NOT EXISTS ( select dsl.log_table
1971 from dba_snapshot_logs dsl
1972 where dsl.log_owner = p_owner
1973 and dsl.log_table = dt.table_name)
1974 AND NOT EXISTS ( select dqt.queue_table
1975 from dba_queue_tables dqt
1976 where dqt.owner = p_owner
1977 and dqt.queue_table = dt.table_name)
1978 AND NOT EXISTS ( select det.table_name
1979 from dba_external_tables det
1980 where det.owner = p_owner
1981 and det.table_name = dt.table_name)
1982 AND dt.table_name NOT LIKE 'AQ$%' -- tables for AQ tables
1983 AND (dt.table_name NOT LIKE 'DR$%' -- tables for INTERMEDIA indexes
1984 OR dt.owner = 'CTXSYS')
1985 AND dt.table_name NOT LIKE 'RUPD$%' -- tables for snapshot logs
1986 AND dt.table_name NOT LIKE 'MDRT%$' -- tables for SPATIAL indexes
1987 AND dt.table_name NOT LIKE 'BIN$%'
1988 AND dt.nested='NO'
1989 UNION ALL
1990 -- all IOTs go to TRANSACTION_TABLES (default rule)
1991 -- Not AQs, Domain Index tables
1992 SELECT dt.owner,
1993 dt.table_name,
1994 fnd_ts_mig_util.l_def_tab_tsp tablespace_type,
1995 ft.tablespace new_tablespace,
1996 di.tablespace_name,
1997 dt.iot_type,
1998 dt.partitioned,
1999 dt.logging
2000 FROM dba_tables dt,
2001 dba_indexes di,
2002 fnd_tablespaces ft
2003 WHERE dt.owner = p_owner
2004 AND ft.tablespace_type = fnd_ts_mig_util.l_def_tab_tsp
2005 AND dt.owner = di.table_owner
2006 AND dt.table_name = di.table_name
2007 AND di.index_type = 'IOT - TOP'
2008 AND NVL(dt.temporary, 'N') = 'N'
2009 AND NVL(dt.iot_type, 'X') = 'IOT'
2010 AND dt.cluster_name IS NULL
2011 AND NOT EXISTS ( select dqt.queue_table
2012 from dba_queue_tables dqt
2013 where dqt.owner = p_owner
2014 and dqt.queue_table = dt.table_name)
2015 AND NOT EXISTS ( select det.table_name
2016 from dba_external_tables det
2017 where det.owner = p_owner
2018 and det.table_name = dt.table_name)
2019 AND dt.table_name NOT LIKE 'AQ$%' -- tables for AQ tables
2020 AND (dt.table_name NOT LIKE 'DR$%' -- tables for INTERMEDIA indexes
2021 OR dt.owner = 'CTXSYS')
2022 AND dt.table_name NOT LIKE 'MDRT%$' -- tables for SPATIAL indexes
2023 AND dt.table_name NOT LIKE 'BIN$%'
2024 AND dt.table_name NOT LIKE 'RUPD$%'; -- tables for snapshot logs
2025
2026 CURSOR part_tsp_csr(l_owner VARCHAR2, l_table_name VARCHAR2, l_tablespace_name VARCHAR2) IS
2027 SELECT '1'
2028 FROM dba_tab_partitions
2029 WHERE table_owner = l_owner
2030 AND table_name = l_table_name
2031 AND tablespace_name <> l_tablespace_name;
2032 l_dummy VARCHAR2(1);
2033
2034 l_idx_tablespace VARCHAR2(30);
2035 l_txn_idx_tablespace VARCHAR2(30) := get_txn_idx_tablespace;
2036 l_tab_moved BOOLEAN := FALSE;
2037 l_lineno NUMBER;
2038 l_parent_lineno NUMBER;
2039 l_parent_owner FND_TS_MIG_CMDS.PARENT_OWNER%TYPE;
2040 l_parent_obj_name FND_TS_MIG_CMDS.PARENT_OBJECT_NAME%TYPE;
2041 l_execution_mode FND_TS_MIG_CMDS.EXECUTION_MODE%TYPE;
2042
2043 BEGIN
2044
2045 OPEN tab_csr;
2046 LOOP
2047 tab_rec_tab.owner.DELETE;
2048 FETCH tab_csr BULK COLLECT INTO
2049 tab_rec_tab.owner, tab_rec_tab.table_name,
2050 tab_rec_tab.tablespace_type, tab_rec_tab.new_tablespace,
2051 tab_rec_tab.tablespace_name, tab_rec_tab.iot_type,
2052 tab_rec_tab.partitioned, tab_rec_tab.logging LIMIT 1000;
2053 EXIT WHEN tab_rec_tab.owner.COUNT = 0;
2054 FOR i IN tab_rec_tab.owner.FIRST..tab_rec_tab.owner.LAST
2055 LOOP
2056 l_tab_moved := FALSE;
2057 l_lineno := NULL;
2058 l_parent_lineno := NULL;
2059 l_parent_owner := NULL;
2060 l_parent_obj_name := NULL;
2061 l_execution_mode := NULL;
2065 OPEN part_tsp_csr(tab_rec_tab.owner(i),tab_rec_tab.table_name(i),tab_rec_tab.new_tablespace(i));
2062
2063 if NVL(tab_rec_tab.partitioned(i), 'NO') = 'YES' then
2064 -- Check if any partition needs to be moved
2066 FETCH part_tsp_csr INTO l_dummy;
2067 if part_tsp_csr%FOUND then
2068 l_tab_moved := TRUE;
2069 end if;
2070 CLOSE part_tsp_csr;
2071 end if;
2072
2073 -- 04/21/03 Check to see if there are any MV logs on this Table and if present, truncate them and move them to the correct tablespace before moving the table.
2074 gen_move_mvlogs (p_owner => tab_rec_tab.owner(i),
2075 p_table_name => tab_rec_tab.table_name(i),
2076 x_parent_obj_name => l_parent_obj_name,
2077 p_lineno => NULL,
2078 x_lineno => l_parent_lineno,
2079 p_type => 'MV_LOG');
2080 if l_parent_obj_name IS NOT NULL then
2081 l_parent_owner := tab_rec_tab.owner(i);
2082 end if;
2083
2084 if tab_rec_tab.tablespace_name(i) <> tab_rec_tab.new_tablespace(i) OR
2085 l_tab_moved
2086 then
2087 gen_move_obj ( p_owner => tab_rec_tab.owner(i),
2088 p_obj_type => 'TABLE',
2089 p_sub_obj_type => tab_rec_tab.iot_type(i),
2090 p_obj_name => tab_rec_tab.table_name(i),
2091 p_partitioned => tab_rec_tab.partitioned(i),
2092 p_logging => tab_rec_tab.logging(i),
2093 p_old_tablespace => tab_rec_tab.tablespace_name(i),
2094 p_new_tablespace => tab_rec_tab.new_tablespace(i),
2095 p_parent_owner => l_parent_owner,
2096 p_parent_obj_name => l_parent_obj_name,
2097 p_parent_lineno => l_parent_lineno,
2098 x_execution_mode => l_execution_mode,
2099 x_lineno => l_lineno);
2100 l_tab_moved := TRUE;
2101 end if;
2102
2103 l_idx_tablespace := get_idx_tablespace(tab_rec_tab.tablespace_type(i),
2104 tab_rec_tab.new_tablespace(i),
2105 l_txn_idx_tablespace);
2106
2107 -- Rebuild all the indexes on the table in the new tablespace
2108 gen_rebuild_idx( p_owner => tab_rec_tab.owner(i),
2109 p_table_name => tab_rec_tab.table_name(i),
2110 p_parent_obj_type => 'TABLE',
2111 p_tab_moved => l_tab_moved,
2112 p_tablespace_name => l_idx_tablespace,
2113 p_parent_lineno => l_lineno,
2114 p_execution_mode => l_execution_mode,
2115 p_type => 'INDEX');
2116
2117 END LOOP;
2118 END LOOP;
2119 CLOSE tab_csr;
2120
2121 END gen_move_tabs;
2122
2123
2124 PROCEDURE gen_migrate_schema (p_schema IN VARCHAR2,
2125 p_threshold_size IN NUMBER DEFAULT NULL)
2126 IS
2127 CURSOR userid_csr IS
2128 SELECT oracle_username
2129 FROM fnd_oracle_userid
2130 WHERE oracle_username = p_schema
2131 AND read_only_flag IN ('E', 'A', 'U', 'M', 'K');
2132 l_schema VARCHAR2(30);
2133 l_string VARCHAR2(4000);
2134 BEGIN
2135 OPEN userid_csr;
2136 FETCH userid_csr INTO l_schema;
2137 if userid_csr%NOTFOUND then
2138 raise_application_error(-20001, 'Schema '||p_schema||' is invalid for migration');
2139 end if;
2140 CLOSE userid_csr;
2141
2142 g_threshold_size := p_threshold_size;
2143
2144 gen_move_tabs (l_schema);
2145
2146 gen_move_mvs (l_schema);
2147
2148 gen_move_aqs (l_schema);
2149
2150 END gen_migrate_schema;
2151
2152
2153 PROCEDURE gen_alter_constraint (p_schema IN VARCHAR2)
2154 IS
2155 CURSOR cons_csr(l_owner VARCHAR2) IS
2156 SELECT dc.owner, dc.table_name, dc.constraint_name
2157 FROM dba_constraints dc
2158 WHERE r_owner = l_owner
2159 AND constraint_type = 'R'
2160 AND status = 'ENABLED';
2161 l_string VARCHAR2(4000);
2162 l_lineno NUMBER;
2163 BEGIN
2164 FOR cons_rec IN cons_csr(p_schema)
2165 LOOP
2166 l_string := 'ALTER TABLE "'||cons_rec.owner||'"."'||cons_rec.table_name||'" DISABLE CONSTRAINT '||cons_rec.constraint_name;
2167 write_out(
2168 p_owner => p_schema,
2169 p_object_type => 'DISABLE_CONSTRAINT',
2170 p_mig_cmd => l_string,
2171 p_object_name => cons_rec.constraint_name,
2172 x_lineno => l_lineno);
2173
2174 l_string := 'ALTER TABLE "'||cons_rec.owner||'"."'||cons_rec.table_name||'" ENABLE NOVALIDATE CONSTRAINT '||cons_rec.constraint_name;
2175 write_out(
2176 p_owner => p_schema,
2177 p_object_type => 'ENABLE_CONSTRAINT',
2178 p_mig_cmd => l_string,
2179 p_object_name => cons_rec.constraint_name,
2180 x_lineno => l_lineno);
2181 END LOOP;
2182 END gen_alter_constraint;
2183
2184 PROCEDURE gen_alter_trigger (p_schema IN VARCHAR2)
2185 IS
2186 CURSOR trg_csr(l_owner VARCHAR2) IS
2187 SELECT dt.owner, dt.trigger_name
2188 FROM dba_triggers dt
2189 WHERE table_owner = l_owner
2190 AND status = 'ENABLED';
2191 l_string VARCHAR2(4000);
2192 l_lineno NUMBER;
2193 BEGIN
2194 FOR trg_rec IN trg_csr(p_schema)
2195 LOOP
2196 l_string := 'ALTER TRIGGER "'||trg_rec.owner||'"."'||trg_rec.trigger_name||'" DISABLE';
2197 write_out(
2198 p_owner => p_schema,
2199 p_object_type => 'DISABLE_TRIGGER',
2200 p_mig_cmd => l_string,
2204 l_string := 'ALTER TRIGGER "'||trg_rec.owner||'"."'||trg_rec.trigger_name||'" ENABLE';
2201 p_object_name => trg_rec.trigger_name,
2202 x_lineno => l_lineno);
2203
2205 write_out(
2206 p_owner => p_schema,
2207 p_object_type => 'ENABLE_TRIGGER',
2208 p_mig_cmd => l_string,
2209 p_object_name => trg_rec.trigger_name,
2210 x_lineno => l_lineno);
2211 END LOOP;
2212 END gen_alter_trigger;
2213
2214 PROCEDURE gen_alter_queue (p_schema IN VARCHAR2)
2215 IS
2216 CURSOR queue_csr(l_owner VARCHAR2) IS
2217 SELECT dq.owner, dq.name
2218 FROM dba_queues dq
2219 WHERE owner = l_owner
2220 AND queue_type = 'NORMAL_QUEUE'
2221 AND TRIM(enqueue_enabled) = 'YES';
2222 l_string VARCHAR2(4000);
2223 l_lineno NUMBER;
2224 BEGIN
2225 FOR queue_rec IN queue_csr(p_schema)
2226 LOOP
2227 l_string := 'BEGIN DBMS_AQADM.STOP_QUEUE('''||queue_rec.owner||'.'||queue_rec.name||'''); END;';
2228 write_out(
2229 p_owner => p_schema,
2230 p_object_type => 'STOP_QUEUE',
2231 p_mig_cmd => l_string,
2232 p_object_name => queue_rec.name,
2233 x_lineno => l_lineno);
2234
2235 l_string := 'BEGIN DBMS_AQADM.START_QUEUE('''||queue_rec.owner||'.'||queue_rec.name||'''); END;';
2236 write_out(
2237 p_owner => p_schema,
2238 p_object_type => 'START_QUEUE',
2239 p_mig_cmd => l_string,
2240 p_object_name => queue_rec.name,
2241 x_lineno => l_lineno);
2242 END LOOP;
2243 END gen_alter_queue;
2244
2245 PROCEDURE gen_alter_policy (p_schema IN VARCHAR2)
2246 IS
2247 CURSOR policy_csr(l_owner VARCHAR2) IS
2248 SELECT object_owner,
2249 object_name,
2250 policy_group,
2251 policy_name
2252 FROM dba_policies
2253 WHERE object_owner = l_owner
2254 AND enable = 'YES';
2255 l_string VARCHAR2(4000);
2256 l_lineno NUMBER;
2257 BEGIN
2258 FOR policy_rec IN policy_csr(p_schema)
2259 LOOP
2260 l_string := 'BEGIN DBMS_RLS.ENABLE_GROUPED_POLICY('''||policy_rec.object_owner||''', '''||policy_rec.object_name||''', '''||policy_rec.policy_group||''', '''||policy_rec.policy_name||''', FALSE); END;';
2261 write_out(
2262 p_owner => p_schema,
2263 p_object_type => 'DISABLE_POLICY',
2264 p_mig_cmd => l_string,
2265 p_object_name => policy_rec.policy_name,
2266 p_subobject_type => policy_rec.object_name,
2267 x_lineno => l_lineno);
2268
2269 l_string := 'BEGIN DBMS_RLS.ENABLE_GROUPED_POLICY('''||policy_rec.object_owner||''', '''||policy_rec.object_name||''', '''||policy_rec.policy_group||''', '''||policy_rec.policy_name||''', TRUE); END;';
2270 write_out(
2271 p_owner => p_schema,
2272 p_object_type => 'ENABLE_POLICY',
2273 p_mig_cmd => l_string,
2274 p_object_name => policy_rec.policy_name,
2275 p_subobject_type => policy_rec.object_name,
2276 x_lineno => l_lineno);
2277 END LOOP;
2278 END gen_alter_policy;
2279
2280 PROCEDURE gen_postmig_cmd (p_schema IN VARCHAR2)
2281 IS
2282 CURSOR lineno_csr IS
2283 SELECT FND_TS_MIG_CMDS_S.nextval
2284 FROM SYS.dual;
2285 l_lineno NUMBER;
2286
2287 CURSOR cmd_csr IS
2288 SELECT lineno, subobject_type
2289 FROM fnd_ts_mig_cmds
2290 WHERE object_type = 'POSTMIG'
2291 AND object_name = 'AQ_TM_PROCESSES';
2292
2293 CURSOR aq_tm_csr IS
2294 SELECT value
2295 FROM v$parameter
2296 WHERE name='aq_tm_processes';
2297 l_string VARCHAR2(1000);
2298 l_value1 VARCHAR2(1000);
2299 l_value2 VARCHAR2(1000);
2300 BEGIN
2301 OPEN cmd_csr;
2302 FETCH cmd_csr INTO l_lineno, l_value1;
2303 CLOSE cmd_csr;
2304
2305 OPEN aq_tm_csr;
2306 FETCH aq_tm_csr INTO l_value2;
2307 CLOSE aq_tm_csr;
2308
2309 if NVL(l_value2, 0) <> 0 AND l_value1 IS NULL then
2310 OPEN lineno_csr;
2311 FETCH lineno_csr INTO l_lineno;
2312 CLOSE lineno_csr;
2313
2314 l_string := 'ALTER SYSTEM SET AQ_TM_PROCESSES = '||l_value2;
2315 INSERT INTO fnd_ts_mig_cmds (lineno,
2316 owner,
2317 object_type,
2318 subobject_type,
2319 index_parallel,
2320 object_name,
2321 migration_cmd,
2322 migration_status,
2323 execution_mode,
2324 partitioned,
2325 generation_date,
2326 last_update_date)
2327 VALUES (l_lineno,
2328 p_schema,
2329 'POSTMIG',
2330 l_value2,
2331 'NOPARALLEL',
2332 'AQ_TM_PROCESSES',
2333 l_string,
2334 'GENERATED',
2335 'P',
2336 'NO',
2337 sysdate,
2338 sysdate);
2339 elsif NVL(l_value2, 0) <> 0 AND l_value1 <> l_value2 then
2340 l_string := 'ALTER SYSTEM SET AQ_TM_PROCESSES = '||l_value2;
2341 UPDATE fnd_ts_mig_cmds
2342 SET migration_cmd = l_string,
2343 subobject_type = l_value2,
2344 generation_date = sysdate,
2345 last_update_date = sysdate
2346 WHERE lineno = l_lineno;
2347 end if;
2348
2349 END gen_postmig_cmd;
2350
2351 PROCEDURE gen_disable_cmds (p_schema IN VARCHAR2)
2352 IS
2353 BEGIN
2354
2355 gen_alter_constraint (p_schema);
2356
2357 gen_alter_trigger (p_schema);
2358
2359 gen_alter_queue (p_schema);
2360
2361 gen_alter_policy (p_schema);
2362
2363 gen_postmig_cmd (p_schema);
2364
2365 END gen_disable_cmds;
2366
2367 END fnd_gen_mig_cmds;