1 PACKAGE BODY fnd_ts_mig_util AS
2 /* $Header: fndptmub.pls 120.2 2005/11/15 16:28:16 mnovakov noship $ */
3 G_USER_ID NUMBER := FND_GLOBAL.USER_ID;
4 G_LOGIN_ID NUMBER := FND_GLOBAL.CONC_LOGIN_ID;
5
6
7 FUNCTION get_db_version
8 RETURN NUMBER
9 IS
10 CURSOR ver_csr IS
11 SELECT TO_NUMBER(SUBSTR(version, 1, 3))
12 FROM product_component_version
13 WHERE product like 'Oracle%Enterprise Edition%';
14 l_version NUMBER;
15 BEGIN
16 OPEN ver_csr;
17 FETCH ver_csr INTO l_version;
18 CLOSE ver_csr;
19 RETURN l_version;
20 END get_db_version;
21
22 -- Set the TABLESPACE_NAME in gl_storage_parameters to INTERFACE.
23 PROCEDURE upd_gl_storage_param (p_tablespace_type VARCHAR2)
24 IS
25 CURSOR tsp_csr IS
26 SELECT tablespace
27 FROM fnd_tablespaces
28 WHERE tablespace_type = p_tablespace_type;
29 l_tablespace_name FND_TABLESPACES.TABLESPACE%TYPE;
30 BEGIN
31 OPEN tsp_csr;
32 FETCH tsp_csr INTO l_tablespace_name;
33 if tsp_csr%NOTFOUND then
34 raise_application_error(-20001, 'Tablespace of type '||p_tablespace_type||' is not present in FND_TABLESPACES table.');
35 end if;
36 CLOSE tsp_csr;
37
38 UPDATE gl_storage_parameters
39 SET tablespace_name = l_tablespace_name;
40
41 END upd_gl_storage_param;
42
43 -- Migrate Dictionary managed tablespaces to locally managed
44 PROCEDURE migrate_tsp_to_local
45 IS
46 CURSOR tsp_csr IS
47 SELECT distinct dt.tablespace_name
48 FROM dba_tablespaces dt,
49 fnd_product_installations fpi
50 WHERE dt.extent_management = 'DICTIONARY'
51 AND (dt.tablespace_name = fpi.tablespace
52 OR dt.tablespace_name = fpi.index_tablespace);
53 l_tablespace_name DBA_TABLESPACES.TABLESPACE_NAME%TYPE;
54 BEGIN
55 OPEN tsp_csr;
56 LOOP
57 FETCH tsp_csr INTO l_tablespace_name;
58 EXIT WHEN tsp_csr%NOTFOUND;
59 DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL(l_tablespace_name);
60 END LOOP;
61 CLOSE tsp_csr;
62 END migrate_tsp_to_local;
63
64
65 FUNCTION get_tablespace_name (p_tablespace_type IN VARCHAR2)
66 RETURN VARCHAR2
67 IS
68 CURSOR tbs_csr IS
69 SELECT tablespace
70 FROM fnd_tablespaces
71 WHERE tablespace_type = p_tablespace_type;
72 l_tablespace_name FND_TABLESPACES.TABLESPACE%TYPE;
73 BEGIN
74 OPEN tbs_csr;
75 FETCH tbs_csr INTO l_tablespace_name;
76 if tbs_csr%NOTFOUND then
77 raise_application_error(-20001, 'Tablespace Type '||p_tablespace_type||' does n
78 ot exist.');
79 end if;
80 CLOSE tbs_csr;
81 RETURN l_tablespace_name;
82 END get_tablespace_name;
83
84 FUNCTION get_tablespace_ues (p_tablespace_name IN VARCHAR2)
85 RETURN NUMBER
86 IS
87 CURSOR tbs_ues_csr IS
88 SELECT initial_extent,
89 allocation_type
90 FROM dba_tablespaces
91 WHERE tablespace_name = p_tablespace_name;
92 l_ues NUMBER;
93 l_allocation_type DBA_TABLESPACES.ALLOCATION_TYPE%TYPE;
94 BEGIN
95 OPEN tbs_ues_csr;
96 FETCH tbs_ues_csr INTO l_ues, l_allocation_type;
97 if tbs_ues_csr%NOTFOUND then
98 raise_application_error(-20001, 'Tablespace '||p_tablespace_name||' does n
99 ot exist.');
100 end if;
101 CLOSE tbs_ues_csr;
102
103 if l_allocation_type = 'SYSTEM' then
104 l_ues := NULL;
105 end if;
106
107 RETURN l_ues;
108 END get_tablespace_ues;
109
110 PROCEDURE chk_new_tablespaces
111 IS
112 CURSOR tsp_csr IS
113 SELECT tablespace
114 FROM fnd_tablespaces
115 WHERE tablespace NOT IN (select tablespace_name
116 from dba_tablespaces)
117 AND tablespace_type IN ('TRANSACTION_TABLES', 'TRANSACTION_INDEXES', 'REFERENCE', 'ARCHIVE' ,'SUMMARY', 'INTERFACE', 'MEDIA', 'AQ', 'NOLOGGING', 'TOOLS');
118 l_tablespace_name VARCHAR2(30);
119 BEGIN
120 OPEN tsp_csr;
121 FETCH tsp_csr INTO l_tablespace_name;
122 if tsp_csr%FOUND then
123 raise_application_error(-20001, 'All the tablespaces required for the new tablespace design are not created.');
124 end if;
125 CLOSE tsp_csr;
126
127 END chk_new_tablespaces;
128
129 PROCEDURE chk_new_tables
130 IS
131 l_cnt NUMBER;
132 BEGIN
133 SELECT COUNT(1)
134 INTO l_cnt
135 FROM fnd_object_tablespaces;
136 if l_cnt = 0 then
137 raise_application_error(-20001, 'Object classification does not exist in FND_OBJECT_TABLESPACES.');
138 end if;
139
140 SELECT COUNT(1)
141 INTO l_cnt
142 FROM fnd_tablespaces;
143 if l_cnt < 6 then
144 raise_application_error(-20001, 'New Tablespaces definition does not exist in FND_TABLESPACES.');
145 end if;
146
147 END chk_new_tables;
148
149 PROCEDURE chk_product_defaults
150 IS
151 CURSOR usr_def_csr IS
152 SELECT '1'
153 FROM dba_users
154 WHERE username in (select oracle_username
155 from fnd_oracle_userid
156 where read_only_flag in ('E','A','U','K','M'))
157 AND default_tablespace <> (select tablespace
158 from fnd_tablespaces
159 where tablespace_type = l_def_tab_tsp);
160
161 CURSOR prd_inst_csr IS
162 SELECT '1'
163 FROM fnd_product_installations
164 WHERE oracle_id in (select oracle_id
165 from fnd_oracle_userid
166 where read_only_flag in ('E','A','U','K','M'))
167 AND (index_tablespace <> (select tablespace
168 from fnd_tablespaces
169 where tablespace_type = l_def_ind_tsp)
170 OR
171 tablespace <> (select tablespace
172 from fnd_tablespaces
173 where tablespace_type = l_def_tab_tsp));
174 l_dummy VARCHAR2(1);
175
176 CURSOR prd_grps_csr IS
177 SELECT is_new_ts_mode
178 FROM fnd_product_groups;
179 l_new_ts_mode FND_PRODUCT_GROUPS.IS_NEW_TS_MODE%TYPE;
180 BEGIN
181 OPEN usr_def_csr;
182 FETCH usr_def_csr INTO l_dummy;
183 if usr_def_csr%FOUND then
184 raise_application_error(-20001, 'The Default Tablespace of Oracle Users is not changed for the new tablesapce design.');
185 end if;
186 CLOSE usr_def_csr;
187
188 OPEN prd_inst_csr;
189 FETCH prd_inst_csr INTO l_dummy;
190 if prd_inst_csr%FOUND then
191 raise_application_error(-20001, 'The Tablespace and Index Tablespace for Oracle products in FND_PRODUCT_INSTALLATIONS is not changed for the new tablesapce design.');
192 end if;
193 CLOSE prd_inst_csr;
194
195 -- check if the new tablespace design flag is set in FND_PRODUCT_GROUPS.
196 OPEN prd_grps_csr;
197 FETCH prd_grps_csr INTO l_new_ts_mode;
198 CLOSE prd_grps_csr;
199 if l_new_ts_mode <> 'Y' then
200 raise_application_error(-20001, 'The New Tablespace Mode flag for the new tablesapce design is not set in FND_PRODUCT_GROUPS.');
201 end if;
202
203 END chk_product_defaults;
204
205
206 PROCEDURE crt_storage_pref (p_tablespace_type IN VARCHAR2,
207 l_pref_name IN VARCHAR2)
208 IS
209 CURSOR get_tsp_csr IS
210 SELECT tablespace
211 FROM fnd_tablespaces
212 WHERE tablespace_type = p_tablespace_type;
213 l_tablespace_name VARCHAR2(30);
214 l_ues NUMBER;
215 l_string VARCHAR2(4000);
216 l_storage_str VARCHAR2(4000);
217 BEGIN
218 OPEN get_tsp_csr;
219 FETCH get_tsp_csr INTO l_tablespace_name;
220 if get_tsp_csr%NOTFOUND then
221 raise_application_error(-20001, 'Tablespace Type '||p_tablespace_type||' does not exist in FND_TABLESPACES.');
222 end if;
223 CLOSE get_tsp_csr;
224
225 l_ues := get_tablespace_ues(l_tablespace_name);
226 if l_ues IS NOT NULL then
227 l_storage_str := 'STORAGE (INITIAL '||l_ues||')';
228 end if;
229
230 l_string := 'BEGIN
231 ctx_ddl.create_preference('''||l_pref_name||''', ''BASIC_STORAGE'');
232 ctx_ddl.set_attribute('''||l_pref_name||''', ''I_TABLE_CLAUSE'', ''tablespace '||l_tablespace_name||' '||l_storage_str||''');
233 ctx_ddl.set_attribute('''||l_pref_name||''', ''K_TABLE_CLAUSE'', ''tablespace '||l_tablespace_name||' '||l_storage_str||''');
234 ctx_ddl.set_attribute('''||l_pref_name||''', ''R_TABLE_CLAUSE'', ''tablespace '||l_tablespace_name||' '||l_storage_str||' lob (data) store as (cache)'');
235 ctx_ddl.set_attribute('''||l_pref_name||''', ''N_TABLE_CLAUSE'', ''tablespace '||l_tablespace_name||' '||l_storage_str||''');
236 ctx_ddl.set_attribute('''||l_pref_name||''', ''I_INDEX_CLAUSE'', ''tablespace '||l_tablespace_name||' '||l_storage_str||' compress 2'');
237 END;';
238
239 EXECUTE IMMEDIATE l_string;
240
241 END crt_storage_pref;
242
243 PROCEDURE upd_fot_username IS
244 BEGIN
245 UPDATE fnd_object_tablespaces fot
246 SET oracle_username = (select fou.oracle_username
247 from fnd_product_installations fpi,
248 fnd_oracle_userid fou
249 where fpi.oracle_id = fou.oracle_id
250 and fpi.application_id = fot.application_id)
251 WHERE oracle_username IS NULL;
252
253 END upd_fot_username;
254
255 PROCEDURE process_gl_storage_param(p_apps_schema_name IN VARCHAR2) IS
256 CURSOR gl_csr IS
257 SELECT gsp.object_name,
258 gsp.tablespace_name,
259 ft.tablespace_type
260 FROM gl_storage_parameters gsp,
261 fnd_tablespaces ft
262 WHERE gsp.tablespace_name = ft.tablespace
263 AND object_type = 'T';
264
265 CURSOR gl_tab_csr(l_table_name VARCHAR2) IS
266 SELECT dt.owner,
267 dt.table_name,
268 fot.object_source,
269 fot.tablespace_type,
270 fot.custom_tablespace_type,
271 fot.custom_flag
272 FROM dba_tables dt,
273 fnd_object_tablespaces fot
274 WHERE dt.table_name like l_table_name||'%'
275 AND fot.oracle_username(+) = dt.owner
276 AND fot.object_name(+) = dt.table_name
277 -- AND NVL(dt.iot_type, 'X') NOT IN ('IOT', 'IOT_OVERFLOW')
278 AND NVL(dt.temporary, 'N') = 'N'
279 AND NOT EXISTS ( select ds.table_name
280 from dba_snapshots ds
281 where ds.owner = dt.owner
282 and ds.table_name = dt.table_name)
283 AND NOT EXISTS ( select dsl.log_table
284 from dba_snapshot_logs dsl
285 where dsl.log_owner = dt.owner
286 and dsl.log_table = dt.table_name)
287 AND NOT EXISTS ( select dqt.queue_table
288 from dba_queue_tables dqt
289 where dqt.owner = dt.owner
290 and dqt.queue_table = dt.table_name)
291 AND dt.table_name not like 'AQ$%'
292 AND dt.table_name not like 'DR$'
293 AND dt.table_name NOT LIKE 'RUPD$%'
294 ORDER BY dt.owner;
295 l_owner DBA_TABLES.OWNER%TYPE;
296 l_last_owner DBA_TABLES.OWNER%TYPE;
297 l_table_name DBA_TABLES.TABLE_NAME%TYPE;
298 l_tablespace_type FND_OBJECT_TABLESPACES.TABLESPACE_TYPE%TYPE;
299 l_custom_tsp_type FND_OBJECT_TABLESPACES.TABLESPACE_TYPE%TYPE;
300 l_custom_flag FND_OBJECT_TABLESPACES.CUSTOM_FLAG%TYPE;
301 l_object_source FND_OBJECT_TABLESPACES.OBJECT_SOURCE%TYPE;
302 l_rowid ROWID;
303
304 CURSOR app_csr(l_oracle_username VARCHAR2) IS
305 SELECT fpi.application_id
306 FROM fnd_product_installations fpi,
307 fnd_oracle_userid fou
308 WHERE fpi.oracle_id = fou.oracle_id
309 AND fou.oracle_username = l_oracle_username
310 ORDER BY fpi.application_id;
311 l_app_id FND_PRODUCT_INSTALLATIONS.APPLICATION_ID%TYPE;
312 BEGIN
313
314 FOR gl_rec IN gl_csr
315 LOOP
316
317 OPEN gl_tab_csr(gl_rec.object_name);
318 LOOP
319 FETCH gl_tab_csr INTO l_owner, l_table_name, l_object_source, l_tablespace_type, l_custom_tsp_type, l_custom_flag;
320 EXIT WHEN gl_tab_csr%NOTFOUND;
321
322 --dbms_output.put_line('table name '||l_table_name);
323
324 if l_owner <> NVL(l_last_owner, 'X') then
325 -- Get the APP ID only if the owner changes
326 if l_owner = p_apps_schema_name then
327 l_app_id := -999;
328 else
329 OPEN app_csr(l_owner);
330 FETCH app_csr INTO l_app_id;
331 if app_csr%NOTFOUND then
332 raise_application_error(-20001, 'Application Id not found for '||l_owner||' in FND_PRODUCT_INSTALLATIONS.');
333 end if;
334 CLOSE app_csr;
335 end if;
336 end if;
337
338 if l_tablespace_type IS NULL then
339 FND_OBJECT_TABLESPACES_PKG.INSERT_ROW
340 (l_rowid,
341 l_app_id,
342 l_table_name,
343 'TABLE',
344 gl_rec.tablespace_type,
345 NULL,
346 'RULES',
347 l_owner,
348 NULL,
349 SYSDATE,
350 g_user_id,
351 SYSDATE,
352 g_user_id,
353 g_login_id);
354 elsif NVL(l_object_source, 'X') = 'RULES' and l_tablespace_type <> gl_rec.tablespace_type then
355 FND_OBJECT_TABLESPACES_PKG.UPDATE_ROW
356 (l_app_id,
357 l_table_name,
358 'TABLE',
359 gl_rec.tablespace_type,
360 l_custom_tsp_type,
361 'RULES',
362 l_owner,
363 l_custom_flag,
364 SYSDATE,
365 g_user_id,
366 g_login_id);
367 end if;
368 l_last_owner := l_owner;
369 END LOOP;
370 CLOSE gl_tab_csr;
371 END LOOP;
372 END process_gl_storage_param;
373
374 PROCEDURE process_rules(p_apps_schema_name IN VARCHAR2) IS
375 CURSOR rules_csr IS
376 SELECT rule_id,
377 rule_query,
378 tablespace_type
379 FROM fnd_ts_mig_rules
380 ORDER BY rule_id;
381 query VARCHAR2(4000);
382 TYPE rules_tab_csr_type IS REF CURSOR;
383 rules_tab_csr rules_tab_csr_type;
384 l_owner DBA_TABLES.OWNER%TYPE;
385 l_last_owner DBA_TABLES.OWNER%TYPE;
386 l_table_name DBA_TABLES.TABLE_NAME%TYPE;
387 l_tablespace_type FND_OBJECT_TABLESPACES.TABLESPACE_TYPE%TYPE;
388 l_custom_tsp_type FND_OBJECT_TABLESPACES.TABLESPACE_TYPE%TYPE;
389 l_custom_flag FND_OBJECT_TABLESPACES.CUSTOM_FLAG%TYPE;
390 l_object_source FND_OBJECT_TABLESPACES.OBJECT_SOURCE%TYPE;
391 l_rowid ROWID;
392 CURSOR app_csr(l_oracle_username VARCHAR2) IS
393 SELECT fpi.application_id
394 FROM fnd_product_installations fpi,
395 fnd_oracle_userid fou
396 WHERE fpi.oracle_id = fou.oracle_id
397 AND fou.oracle_username = l_oracle_username
398 ORDER BY fpi.application_id;
399 l_app_id FND_PRODUCT_INSTALLATIONS.APPLICATION_ID%TYPE;
400 BEGIN
401
402 upd_fot_username;
403
404 FOR rules_rec IN rules_csr
405 LOOP
406 query := 'SELECT dt.owner, dt.table_name, fot.object_source,
407 fot.tablespace_type, fot.custom_tablespace_type,
408 fot.custom_flag
409 FROM dba_tables dt, fnd_object_tablespaces fot
410 '||rules_rec.rule_query||'
411 AND fot.oracle_username(+) = dt.owner
412 AND fot.object_name(+) = dt.table_name
413 AND NVL(dt.iot_type, ''X'') NOT IN (''IOT'', ''IOT_OVERFLOW'')
414 AND NVL(dt.temporary, ''N'') = ''N''
415 AND dt.owner IN (select oracle_username
416 from fnd_oracle_userid
417 where read_only_flag IN (''E'',''A'',''U'',''K'',''M''))
418 AND NOT EXISTS ( select ds.table_name
419 from dba_snapshots ds
420 where ds.owner = dt.owner
421 and ds.table_name = dt.table_name)
422 AND NOT EXISTS ( select dsl.log_table
423 from dba_snapshot_logs dsl
424 where dsl.log_owner = dt.owner
425 and dsl.log_table = dt.table_name)
426 AND NOT EXISTS ( select dqt.queue_table
427 from dba_queue_tables dqt
428 where dqt.owner = dt.owner
429 and dqt.queue_table = dt.table_name)
430 AND dt.table_name not like ''AQ$%''
431 AND dt.table_name not like ''DR$''
432 AND dt.table_name NOT LIKE ''RUPD$%''
433 ORDER BY dt.owner';
434 --AND fot.object_type(+) = ''TABLE''
435 --dbms_output.put_line(substr(query,1,240));
436
437 OPEN rules_tab_csr FOR query;
438 LOOP
439 FETCH rules_tab_csr INTO l_owner, l_table_name, l_object_source, l_tablespace_type, l_custom_tsp_type, l_custom_flag;
440 EXIT WHEN rules_tab_csr%NOTFOUND;
441
442 --dbms_output.put_line('table name '||l_table_name);
443
444 if l_owner <> NVL(l_last_owner, 'X') then
445 -- Get the APP ID only if the owner changes
446 if l_owner = p_apps_schema_name then
447 l_app_id := -999;
448 else
449 OPEN app_csr(l_owner);
450 FETCH app_csr INTO l_app_id;
451 if app_csr%NOTFOUND then
452 raise_application_error(-20001, 'Application Id not found for '||l_owner||' in FND_PRODUCT_INSTALLATIONS.');
453 end if;
454 CLOSE app_csr;
455 end if;
456 end if;
457
458 if l_tablespace_type IS NULL then
459 FND_OBJECT_TABLESPACES_PKG.INSERT_ROW
460 (l_rowid,
461 l_app_id,
462 l_table_name,
463 'TABLE',
464 rules_rec.tablespace_type,
465 NULL,
466 'RULES',
467 l_owner,
468 NULL,
469 SYSDATE,
470 g_user_id,
471 SYSDATE,
472 g_user_id,
473 g_login_id);
474 elsif NVL(l_object_source, 'X') = 'RULES' and l_tablespace_type <> rules_rec.tablespace_type then
475 FND_OBJECT_TABLESPACES_PKG.UPDATE_ROW
476 (l_app_id,
477 l_table_name,
478 'TABLE',
479 rules_rec.tablespace_type,
480 l_custom_tsp_type,
481 'RULES',
482 l_owner,
483 l_custom_flag,
484 SYSDATE,
485 g_user_id,
486 g_login_id);
487 end if;
488 l_last_owner := l_owner;
489 END LOOP;
490 CLOSE rules_tab_csr;
491 END LOOP;
492
493 process_gl_storage_param(p_apps_schema_name);
494
495 END process_rules;
496
497 PROCEDURE set_defaults
498 IS
499 CURSOR usr_csr IS
500 SELECT oracle_id,
501 oracle_username
502 FROM fnd_oracle_userid
503 WHERE read_only_flag in ('E', 'A', 'U', 'K', 'M')
504 ORDER by oracle_username;
505
506 CURSOR usr_quota_csr(p_username VARCHAR2) IS
507 SELECT dtq.tablespace_name
508 FROM dba_ts_quotas dtq
509 WHERE dtq.username = p_username
510 AND EXISTS (select dt.tablespace_name
511 from dba_tablespaces dt
512 where dt.tablespace_name = dtq.tablespace_name)
513 ORDER by dtq.tablespace_name;
514
515 CURSOR txn_tsp_csr IS
516 SELECT tablespace
517 FROM fnd_tablespaces
518 WHERE tablespace_type = l_def_tab_tsp;
519
520 CURSOR txn_ind_tsp_csr IS
521 SELECT tablespace
522 FROM fnd_tablespaces
523 WHERE tablespace_type = l_def_ind_tsp;
524
525 CURSOR tsp_csr IS
526 SELECT ft.tablespace
527 FROM fnd_tablespaces ft;
528
529 l_string VARCHAR2(4000);
530 l_txn_tablespace VARCHAR2(30);
531 l_txn_ind_tablespace VARCHAR2(30);
532
533 BEGIN
534
535 OPEN txn_tsp_csr;
536 FETCH txn_tsp_csr INTO l_txn_tablespace;
537 if txn_tsp_csr%NOTFOUND then
538 raise_application_error(-20001, 'Tablespace of type '||l_def_tab_tsp||' is not present in FND_TABLESPACES table.');
539 end if;
540 CLOSE txn_tsp_csr;
541
542 OPEN txn_ind_tsp_csr;
543 FETCH txn_ind_tsp_csr INTO l_txn_ind_tablespace;
544 if txn_ind_tsp_csr%NOTFOUND then
545 raise_application_error(-20001, 'Tablespace of type '||l_def_ind_tsp||' is not present in FND_TABLESPACES table.');
546 end if;
547 CLOSE txn_ind_tsp_csr;
548
549 FOR usr_rec IN usr_csr
550 LOOP
551
552 /* Revoke only after all schemas are migrated
553 -- Revoke quota on all tablespaces
554 FOR usr_quota_rec IN usr_quota_csr(usr_rec.oracle_username)
555 LOOP
556 l_string := 'ALTER USER '||usr_rec.oracle_username||' QUOTA 0 ON '||usr_quota_rec.tablespace_name;
557 EXECUTE IMMEDIATE l_string;
558 END LOOP;
559 */
560
561 -- Change the default tablespace for the user as TRANSACTION_TABLES
562 l_string := 'ALTER USER '||usr_rec.oracle_username||' DEFAULT TABLESPACE '||l_txn_tablespace;
563 EXECUTE IMMEDIATE l_string;
564
565 -- Grant unlimited quota to the user for the new tablespaces.
566 FOR tsp_rec IN tsp_csr
567 LOOP
568 l_string := 'ALTER USER '||usr_rec.oracle_username||' QUOTA UNLIMITED ON '||tsp_rec.tablespace;
569 EXECUTE IMMEDIATE l_string;
570 END LOOP;
571
572 -- Set the data tablespace and index tablespace as TRANSACTION_TABLES in fnd_product_installations.
573 UPDATE fnd_product_installations
574 SET tablespace = l_txn_tablespace,
575 index_tablespace = l_txn_ind_tablespace
576 WHERE oracle_id = usr_rec.oracle_id;
577 END LOOP;
578
579 -- Set the TABLESPACE_NAME in gl_storage_parameters to INTERFACE.
580 fnd_ts_mig_util.upd_gl_storage_param('INTERFACE');
581
582 -- Set the new tablespace design flag to Y in fnd_product_groups.
583 UPDATE fnd_product_groups
584 SET is_new_ts_mode = 'Y';
585 END set_defaults;
586
587 PROCEDURE crt_txn_ind_pref
588 IS
589 CURSOR pref_csr IS
590 SELECT pre_name
591 FROM CTX_USER_PREFERENCES
592 WHERE PRE_NAME = 'TXN_IND_STORAGE_PREF';
593 l_pref_name CTX_USER_PREFERENCES.PRE_NAME%TYPE;
594 BEGIN
595 -- Drop storage preference if it exists.
596 OPEN pref_csr;
597 FETCH pref_csr INTO l_pref_name;
598 if pref_csr%FOUND then
599 ctx_ddl.drop_preference('TXN_IND_STORAGE_PREF');
600 end if;
601 CLOSE pref_csr;
602 -- Create storage preference for TRANSACTION_INDEXES tablespace for DOMAIN indexes.
603 crt_storage_pref(l_def_ind_tsp, 'txn_ind_storage_pref');
604 END crt_txn_ind_pref;
605
606 END fnd_ts_mig_util;
[Home] [Help]