[Home] [Help]
PACKAGE BODY: APPS.GMA_PURGE_DDL
Source
1 PACKAGE BODY GMA_PURGE_DDL AS
2 /* $Header: GMAPRGDB.pls 120.1.12010000.2 2008/11/11 20:56:38 srpuri ship $ */
3
4 FUNCTION altertableconstraint
5 (p_purge_id sy_purg_mst.purge_id%TYPE,
6 p_owner user_users.username%TYPE,
7 p_appl_short_name fnd_application.application_short_name%TYPE,
8 p_tablename user_tables.table_name%TYPE,
9 p_constraint_name user_constraints.constraint_name%TYPE,
10 p_disable BOOLEAN,
11 p_debug_flag BOOLEAN)
12 RETURN BOOLEAN;
13
14 /**********************************************************/
15
16 FUNCTION createarctable(p_purge_id sy_purg_mst.purge_id%TYPE,
17 p_tablename user_tables.table_name%TYPE,
18 p_tablespace user_tablespaces.tablespace_name%TYPE,
19 p_owner user_users.username%TYPE,
20 p_appl_short_name fnd_application.application_short_name%TYPE,
21 p_sizing_flag BOOLEAN,
22 p_arctablename user_tables.table_name%TYPE,
23 p_debug_flag BOOLEAN)
24 RETURN BOOLEAN IS
25 -- create archive table in database from named database table
26
27 l_newtablename user_tables.table_name%TYPE;
28 -- holds name of new table to be created
29 l_objectexists EXCEPTION; -- table already exists
30 l_badtable EXCEPTION; -- table wasn't created
31 l_rowcount NUMBER; -- the number of rows in a table
32
33 l_sqlstatement sy_purg_def.sqlstatement%TYPE;
34 l_cursor INTEGER;
35 l_dummy NUMBER;
36
37 l_bytes NUMBER; -- size of new table in blocks
38
39 l_trans_allowed NUMBER := 5;
40
41 PRAGMA EXCEPTION_INIT(l_objectexists,-955);
42
43 l_storage_clause sy_purg_def.sqlstatement%TYPE;
44
45 BEGIN
46
47 -- create new table name and create statement
48 l_newtablename := GMA_PURGE_UTILITIES.makearcname(p_purge_id,p_tablename);
49 l_cursor := DBMS_SQL.OPEN_CURSOR;
50
51 -- coalesce the tablespace
52 GMA_PURGE_DDL.coalescetablespace(p_purge_id,
53 p_tablespace,
54 p_debug_flag);
55
56 -- set up storage clause if so asked
57 l_storage_clause := NULL;
58 IF (p_sizing_flag = TRUE) THEN
59 l_sqlstatement := 'SELECT COUNT(DISTINCT ' ||
60 p_tablename ||
61 ') FROM ' ||p_owner||'.'||
62 p_arctablename;
63
64 GMA_PURGE_UTILITIES.printdebug(p_purge_id,l_sqlstatement,p_debug_flag);
65
66 DBMS_SQL.PARSE(l_cursor,l_sqlstatement,DBMS_SQL.NATIVE);
67 DBMS_SQL.DEFINE_COLUMN(l_cursor,1,l_rowcount);
68 l_dummy := DBMS_SQL.EXECUTE_AND_FETCH(l_cursor);
69 DBMS_SQL.COLUMN_VALUE(l_cursor,1,l_rowcount);
70
71 l_bytes := GMA_PURGE_DDL.tab_size(p_purge_id,
72 p_tablename,
73 l_rowcount,
74 l_trans_allowed,
75 0); -- p_pctfree
76
77 GMA_PURGE_UTILITIES.printlong(p_purge_id,
78 'The ' ||
79 p_tablename ||
80 ' table will need ' ||
81 to_char(l_bytes) ||
82 ' bytes of storage for ' ||
83 to_char(l_rowcount) ||
84 ' rows.');
85
86 l_storage_clause := ' STORAGE ( INITIAL ' || to_char(l_bytes) ||
87 ' MINEXTENTS 1 ' ||
88 ' PCTINCREASE 0)';
89
90 END IF;
91
92 -- create table
93 l_sqlstatement := 'CREATE TABLE ' || p_owner || '.' || l_newtablename
94 || ' TABLESPACE ' || p_tablespace ||
95 ' PCTFREE 0 ' ||
96 'PCTUSED 60' ||
97 ' INITRANS ' || to_char(l_trans_allowed) ||
98 ' MAXTRANS ' || to_char(l_trans_allowed) ||
99 l_storage_clause
100 || ' AS SELECT * ' || 'FROM ' || p_tablename ||
101 ' WHERE ROWNUM < 1';
102
103 GMA_PURGE_UTILITIES.printdebug(p_purge_id,l_sqlstatement,p_debug_flag);
104
105 -- let fly with dynamic sql
106 DECLARE
107 l_extent_size EXCEPTION;
108 PRAGMA EXCEPTION_INIT(l_extent_size,-1658);
109 BEGIN
110 -- Made by Khaja
111 -- IF (GMA_PURGE_ENGINE.use_ad_ddl = TRUE) THEN
112 -- AD_DDL.DO_DDL(p_owner,p_appl_short_name,AD_DDL.CREATE_TABLE,
113 -- l_sqlstatement,l_newtablename);
114 -- ELSE
115 DBMS_SQL.PARSE(l_cursor,l_sqlstatement,DBMS_SQL.NATIVE);
116 -- END IF;
117
118 EXCEPTION
119 WHEN l_extent_size THEN
120 GMA_PURGE_UTILITIES.printlong(p_purge_id,
121 'Not enough contiguous space in tablespace for table.');
122 GMA_PURGE_UTILITIES.printlong(p_purge_id,
123 'Using unspecified table creation.');
124 l_sqlstatement := 'CREATE TABLE ' || p_owner || '.' || l_newtablename
125 || ' TABLESPACE ' || p_tablespace ||
126 ' PCTFREE 0 ' ||
127 'PCTUSED 60' ||
128 ' INITRANS ' || to_char(l_trans_allowed) ||
129 ' MAXTRANS ' || to_char(l_trans_allowed) ||
130 ' AS SELECT * ' || 'FROM ' || p_tablename ||
131 ' WHERE ROWNUM < 1';
132 -- IF (GMA_PURGE_ENGINE.use_ad_ddl = TRUE) THEN
133 -- AD_DDL.DO_DDL(p_owner,p_appl_short_name,AD_DDL.CREATE_TABLE,
134 -- l_sqlstatement,l_newtablename);
135 -- ELSE
136 DBMS_SQL.PARSE(l_cursor,l_sqlstatement,DBMS_SQL.NATIVE);
137 -- END IF;
138 WHEN OTHERS THEN
139 RAISE;
140 END;
141
142 DBMS_SQL.CLOSE_CURSOR(l_cursor);
143
144 GMA_PURGE_UTILITIES.printlong(p_purge_id,l_newtablename || ' table created.');
145
146 -- make sure table got created
147 IF GMA_PURGE_VALIDATE.is_table(p_purge_id,l_newtablename) <> TRUE THEN
148 GMA_PURGE_UTILITIES.printlong(p_purge_id,
149 'Problem Can''''t create table ' || l_newtablename);
150 RAISE l_badtable;
151 ELSE
152 RETURN TRUE;
153 END IF;
154
155 EXCEPTION
156
157 WHEN l_objectexists THEN
158 GMA_PURGE_UTILITIES.printlong(p_purge_id,
159 'Problem with arc row table - ' || l_newtablename ||
160 ' exists.');
161 RETURN NULL;
162
163 WHEN OTHERS THEN
164 GMA_PURGE_UTILITIES.printlong(p_purge_id,
165 'Problem raised in GMA_PURGE_DDL.createarctable with '
166 || p_tablename);
167 GMA_PURGE_UTILITIES.printlong(p_purge_id,
168 'Unhandled EXCEPTION - ' || sqlerrm);
169 RAISE;
170
171 END createarctable;
172
173
174 /***********************************************************/
175
176 PROCEDURE droparctable(p_purge_id sy_purg_mst.purge_id%TYPE,
177 p_owner user_users.username%TYPE,
178 p_appl_short_name fnd_application.application_short_name%TYPE,
179 p_tablename user_tables.table_name%TYPE) IS
180 -- drop named table from database
181
182 l_sqlstatement sy_purg_def.sqlstatement%TYPE;
183 l_cursor INTEGER;
184
185 BEGIN
186
187 l_sqlstatement := 'DROP TABLE '||p_owner||'.'||GMA_PURGE_UTILITIES.makearcname(p_purge_id, --Bug#6681753
188 p_tablename);
189
190 -- Made comments by Khaja
191 -- let fly with dynamic sql
192 -- IF (GMA_PURGE_ENGINE.use_ad_ddl = TRUE) THEN
193 -- AD_DDL.DO_DDL(p_owner,p_appl_short_name,AD_DDL.DROP_TABLE,
194 -- l_sqlstatement,GMA_PURGE_UTILITIES.makearcname(p_purge_id,
195 -- p_tablename));
196 -- ELSE
197 l_cursor := DBMS_SQL.OPEN_CURSOR;
198 DBMS_SQL.PARSE(l_cursor,l_sqlstatement,DBMS_SQL.NATIVE);
199 DBMS_SQL.CLOSE_CURSOR(l_cursor);
200 -- END IF;
201 GMA_PURGE_UTILITIES.printlong(p_purge_id,p_owner||'.'||GMA_PURGE_UTILITIES.makearcname(p_purge_id,
202 p_tablename)
203 || ' table dropped.');
204
205 RETURN;
206
207 EXCEPTION
208
209 WHEN OTHERS THEN
210 GMA_PURGE_UTILITIES.printlong(p_purge_id,
211 'Problem raised in GMA_PURGE_DDL.droparctable.');
212 GMA_PURGE_UTILITIES.printlong(p_purge_id,
213 'Unhandled EXCEPTION - ' || sqlerrm);
214 RAISE;
215
216 END droparctable;
217
218 /***********************************************************/
219
220 PROCEDURE createarcviews(p_purge_id sy_purg_mst.purge_id%TYPE,
221 p_purge_type sy_purg_def.purge_type%TYPE,
222 p_owner user_users.username%TYPE,
223 p_appl_short_name fnd_application.application_short_name%TYPE,
224 p_debug_flag BOOLEAN) IS
225 -- create views of archive tables
226
227 /* CURSOR l_viewtables_cur(cp_purge_type sy_purg_def.purge_type%TYPE) IS
228 SELECT table_name
229 FROM sy_purg_def_act
230 WHERE purge_type = cp_purge_type;
231 l_tablename user_tables.table_name%TYPE;
232
233 CURSOR l_arctables_cur(cp_tablename user_tables.table_name%TYPE) IS
234 SELECT table_name
235 , owner
236 FROM all_tables
237 WHERE SUBSTR(table_name,8) = cp_tablename
238 AND SUBSTR(table_name,7,1) = '_'
239 AND SUBSTR(table_name,6,1) IN
240 ('1','2','3','4','5','6','7','8','9','0')
241 AND SUBSTR(table_name,5,1) IN
242 ('1','2','3','4','5','6','7','8','9','0')
243 AND SUBSTR(table_name,4,1) IN
244 ('1','2','3','4','5','6','7','8','9','0')
245 AND SUBSTR(table_name,3,1) IN
246 ('1','2','3','4','5','6','7','8','9','0')
247 AND SUBSTR(table_name,2,1) IN
248 ('1','2','3','4','5','6','7','8','9','0')
249 AND SUBSTR(table_name,1,1) = 'A'
250 AND owner='GMA';
251
252 l_tables INTEGER;
253 l_return INTEGER;
254 l_owner user_users.username%TYPE;
255
256 l_sqlstatement sy_purg_def.sqlstatement%TYPE;
257 l_cursor INTEGER;
258
259 l_noobject EXCEPTION;
260 PRAGMA EXCEPTION_INIT(l_noobject,-942);
261 l_viewname all_tables.table_name%type;
262 */
263
264 BEGIN
265
266 -- we are going to create or freshen the view for all of the tables in this
267 -- purge type definition
268
269 /* l_cursor := DBMS_SQL.OPEN_CURSOR;
270
271 FOR l_viewtable IN l_viewtables_cur(p_purge_type) LOOP
272 l_tables := 0;
273
274 -- this is a workaround for an apparent bug with 'CREATE OR REPLACE VIEW'
275 BEGIN
276 l_sqlstatement := 'DROP VIEW '
277 || 'A'|| l_viewtable.table_name;
278 -- IF (GMA_PURGE_ENGINE.use_ad_ddl = TRUE) THEN
279 -- AD_DDL.DO_DDL(p_owner,p_appl_short_name,AD_DDL.DROP_VIEW,
280 -- l_sqlstatement,'A' || l_viewtable.table_name);
281 -- ELSE
282 DBMS_SQL.PARSE(l_cursor,l_sqlstatement, DBMS_SQL.NATIVE);
283 l_return := DBMS_SQL.EXECUTE(l_cursor);
284 -- END IF;
285 EXCEPTION
286 WHEN l_noobject THEN
287 NULL;
288 WHEN OTHERS THEN
289 RAISE;
290 END;
291
292 -- l_sqlstatement := 'CREATE VIEW ' || p_owner
293 l_sqlstatement := 'CREATE VIEW '
294 || 'A' || l_viewtable.table_name || ' AS ';
295
296 -- get names of individual tables for view
297 FOR l_arctable IN l_arctables_cur(l_viewtable.table_name) LOOP
298 l_owner := l_arctable.owner;
299 l_sqlstatement := l_sqlstatement
300 || 'SELECT * FROM ' || l_owner
301 || '.' || l_arctable.table_name;
302 l_sqlstatement := l_sqlstatement || ' UNION ';
303 l_tables := l_tables + 1;
304 END LOOP;
305
306 -- create a stub based on the production table if no archive tables
307 -- exist or to make the last 'UNION' work out right
308 -- || 'SELECT * FROM ' ||p_owner
309 l_sqlstatement := l_sqlstatement
310 || 'SELECT * FROM ' ||
311 l_viewtable.table_name || ' WHERE ROWNUM < 1';
312
313 GMA_PURGE_UTILITIES.printdebug(p_purge_id,l_sqlstatement,p_debug_flag);
314
315 l_viewname :=l_viewtable.table_name;
316
317 -- IF (GMA_PURGE_ENGINE.use_ad_ddl = TRUE) THEN
318 -- AD_DDL.DO_DDL(p_owner,p_appl_short_name,AD_DDL.CREATE_VIEW,
319 -- l_sqlstatement,'A' || l_viewtable.table_name);
320 -- ELSE
321 DBMS_SQL.PARSE(l_cursor,l_sqlstatement,DBMS_SQL.NATIVE);
322 l_return := DBMS_SQL.EXECUTE(l_cursor);
323 -- END IF;
324 GMA_PURGE_UTILITIES.printlong(p_purge_id,
325 'A' || l_viewtable.table_name || ' view created.');
326
327 END LOOP; -- each table
328
329 DBMS_SQL.CLOSE_CURSOR(l_cursor);
330
331 EXCEPTION
332
333 WHEN OTHERS THEN
334 IF SQLCODE=-01789 THEN
335 -- This code is added to ignore the view creation if query has no of columns mismatch ORA-01789
336 GMA_PURGE_UTILITIES.printlong(p_purge_id,
337 'Warning: '||'A'||l_viewname||' View cannot get replaced (column mismatch)');
338 ELSE
339
340 GMA_PURGE_UTILITIES.printlong(p_purge_id,
341 'Problem raised in GMA_PURGE_DDL.createarcviews.');
342 GMA_PURGE_UTILITIES.printlong(p_purge_id,
343 'Unhandled EXCEPTION - ' || SQLERRM);
344 RAISE;
345 END IF;
346 */
347 null;
348
349 END createarcviews;
350
351 /***********************************************************/
352
353 FUNCTION altertableconstraint
354 (p_purge_id sy_purg_mst.purge_id%TYPE,
355 p_owner user_users.username%TYPE,
356 p_appl_short_name fnd_application.application_short_name%TYPE,
357 p_tablename user_tables.table_name%TYPE,
358 p_constraint_name user_constraints.constraint_name%TYPE,
359 p_disable BOOLEAN,
360 p_debug_flag BOOLEAN)
361 RETURN BOOLEAN IS
362
363 l_sqlstatement VARCHAR2(100);
364 l_cursor INTEGER;
365
366 BEGIN
367
368 l_sqlstatement := 'ALTER TABLE ' || p_tablename || ' ';
369
370 IF (p_disable = TRUE) THEN
371 l_sqlstatement := l_sqlstatement || 'DISABLE';
372 ELSE
373 l_sqlstatement := l_sqlstatement || 'ENABLE';
374 END IF;
375
376 l_sqlstatement := l_sqlstatement || ' CONSTRAINT ' || p_constraint_name;
377
378 GMA_PURGE_UTILITIES.printdebug(p_purge_id,l_sqlstatement,p_debug_flag);
379
380 IF (GMA_PURGE_ENGINE.use_ad_ddl = TRUE) THEN
381 AD_DDL.DO_DDL(p_owner,p_appl_short_name,AD_DDL.ALTER_TABLE,
382 l_sqlstatement,p_tablename);
383 ELSE
384 l_cursor := DBMS_SQL.OPEN_CURSOR;
385 DBMS_SQL.PARSE(l_cursor,l_sqlstatement,DBMS_SQL.NATIVE);
386 DBMS_SQL.CLOSE_CURSOR(l_cursor);
387 END IF;
388
389 RETURN TRUE;
390
391 EXCEPTION
392
393 WHEN OTHERS THEN
394 IF (p_debug_flag = TRUE) THEN
395 GMA_PURGE_UTILITIES.printlong(p_purge_id,
396 'Raised in GMA_PURGE_DDL.altertableconstraint.');
397 GMA_PURGE_UTILITIES.printlong(p_purge_id,
398 'Ignored EXCEPTION - ' || SQLERRM);
399 END IF;
400 RETURN FALSE;
401
402 END;
403
404 /***********************************************************/
405
406 PROCEDURE alterconstraints
407 (p_purge_id sy_purg_mst.purge_id%TYPE,
408 p_tablenames_tab g_tablename_tab_type,
409 p_tableactions_tab g_tableaction_tab_type,
410 p_tablecount INTEGER,
411 p_idx_tablespace_tab IN OUT NOCOPY g_tablespace_name_tab_type,
412 p_idx_tablespace_count IN OUT NOCOPY INTEGER,
413 p_owner user_users.username%TYPE,
414 p_appl_short_name fnd_application.application_short_name%TYPE,
415 p_action VARCHAR2,
416 p_debug_flag BOOLEAN) IS
417 -- disable or enable all constraints for named table
418
419 CURSOR l_tablename_cur(P_purge_id sy_purg_mst.purge_id%TYPE) IS
420 SELECT table_name,archive_action
421 FROM sy_purg_def_act
422 WHERE
423 Purge_type=(select PURGE_TYPE from sy_purg_mst where Purge_id=P_purge_id);
424
425 TYPE archive_table_rec_type IS RECORD(
426 archive_table user_tables.table_name%TYPE,
427 archive_action char(1));
428
429 TYPE archive_tab_type IS TABLE OF archive_table_rec_type index by binary_integer;
430 l_archive archive_tab_type;
431 a_tablecount number(3);
432
433 CURSOR l_constraints_cur (c_tablename user_tables.table_name%TYPE) IS
434 SELECT b.table_name pk_table_name,a.owner, a.table_name,a.CONSTRAINT_NAME,a.status
435 FROM all_constraints a, all_constraints b
436 WHERE a.r_constraint_name = b.constraint_name
437 -- AND a.constraint_type = 'R'
438 -- AND b.constraint_type = 'P'
439 AND a.r_owner=b.owner
440 AND b.owner not in ('SYS', 'SYSTEM')
441 AND b.table_name = upper(c_tablename)
442 ORDER by b.TABLE_NAME;
443
444 TYPE constraint_table_rec_type IS RECORD(
445 pk_table_name all_constraints.table_name%TYPE,
446 owner all_constraints.owner%TYPE,
447 table_name all_constraints.table_name%TYPE,
448 constraint_name all_constraints.constraint_name%TYPE,
449 status all_constraints.status%TYPE
450 );
451
452 TYPE constraint_tab_type IS TABLE OF constraint_table_rec_type index by binary_integer;
453 l_constraint constraint_tab_type;
454
455
456 c_tablecount number(3):=0;
457
458 l_disable_flag BOOLEAN;
459 l_disable_text VARCHAR2(50);
460
461 l_continue BOOLEAN;
462 TYPE l_success_tab_type IS TABLE OF BOOLEAN
463 INDEX BY BINARY_INTEGER;
464 l_success_tab l_success_tab_type;
465
466 -- while loop control variable
467 l_failure_exists BOOLEAN;
468
469 BEGIN
470
471 -- This will disable or enables the constraint of PM_MATL_DTL
472 IF p_appl_short_name='KHG' THEN
473
474 a_tablecount:=1;
475 l_archive(a_tablecount).archive_table:='PM_MATL_DTL';
476 l_archive(a_tablecount).archive_action:='D';
477 ELSE
478
479 for archive_rec in l_tablename_cur(P_Purge_id) LOOP
480 a_tablecount:=l_tablename_cur%rowcount;
481 l_archive(a_tablecount).archive_table:=archive_rec.table_name;
482 l_archive(a_tablecount).archive_action:=archive_rec.archive_action;
483 end loop;
484
485 END IF;
486
487 -- Is delete specified for any of the target tables?
488 l_continue := FALSE;
489 FOR l_counter IN 1.. a_tablecount LOOP
490 IF (l_archive(l_counter).archive_action= 'D') THEN
491 l_continue := TRUE;
492 EXIT;
493 END IF;
494 END LOOP;
495
496 -- If none of the tables are to be deleted from, leave the constraints alone
497 IF (l_continue = FALSE) THEN
498 RETURN;
499 END IF;
500
501 -- set the action
502 IF (p_action = 'DISABLE') THEN
503 l_disable_flag := TRUE;
504 l_disable_text := 'Disabled';
505 ELSE
506 l_disable_flag := FALSE;
507 l_disable_text := 'Enabled';
508 END IF;
509
510 -- if this is the disable pass, create a list of index tablespaces
511 for i in 1..a_tablecount loop
512
513 for constraint_rec in l_constraints_cur(l_archive(i).archive_table) LOOP
514 c_tablecount:=c_tablecount+1;
515 l_constraint(c_tablecount).pk_table_name:=constraint_rec.pk_table_name;
516 l_constraint(c_tablecount).owner:=constraint_rec.owner;
517 l_constraint(c_tablecount).table_name:=constraint_rec.table_name;
518 l_constraint(c_tablecount).constraint_name:=constraint_rec.constraint_name;
519 l_constraint(c_tablecount).status:=constraint_rec.status;
520 end loop;
521 end loop;
522
523 FOR i IN 1..c_tablecount LOOP
524
525 l_success_tab(i) := altertableconstraint(
526 p_purge_id,
527 l_constraint(i).owner,
528 p_appl_short_name,
529 l_constraint(i).owner||'.'||l_constraint(i).table_name,
530 l_constraint(i).constraint_name,
531 l_disable_flag,
532 p_debug_flag);
533 IF (l_success_tab(i) = TRUE) THEN
534 -- log that the constraint was altered
535 GMA_PURGE_UTILITIES.printlong(p_purge_id,
536 l_disable_text ||
537 ' ' ||
538 l_constraint(i).owner||'.'||l_constraint(i).table_name||
539 '/' ||
540 l_constraint(i).constraint_name||
541 ' - ' ||
542 GMA_PURGE_UTILITIES.chartime);
543 ELSIF (l_success_tab(i) <> TRUE) THEN
544 -- Report failed constraints
545 GMA_PURGE_UTILITIES.printlong(p_purge_id,
546 'WARNING: ' ||
547 l_constraint(i).owner||'.'||l_constraint(i).table_name||
548 '/' ||
549 l_constraint(i).constraint_name||
550 ' not ' ||
551 l_disable_text);
552 END IF;
553 END LOOP;
554
555 EXCEPTION
556
557 WHEN OTHERS THEN
558 GMA_PURGE_UTILITIES.printlong(p_purge_id,
559 'Problem raised in GMA_PURGE_DDL.alterconstraints.');
560 GMA_PURGE_UTILITIES.printlong(p_purge_id,
561 'Unhandled EXCEPTION - ' || SQLERRM);
562 RAISE;
563 END;
564
565 /***********************************************************/
566
567 FUNCTION tab_size(p_purge_id sy_purg_mst.purge_id%TYPE,
568 p_tablename user_tables.table_name%TYPE,
569 p_rowcount NUMBER,
570 p_initrans NUMBER,
571 p_pctfree NUMBER)
572 RETURN NUMBER IS
573 -- return size of initial extent in bytes
574
575 CURSOR l_size_params_cur IS
576 select VP.value db_block_size
577 , VT7.type_size sb2
578 , VT6.type_size ub1
579 , VT5.type_size kcbh
580 , VT4.type_size ub4
581 , VT3.type_size ktbbh
582 , VT2.type_size ktbit
583 , VT1.type_size kdbh
584 from v$type_size VT7
585 , v$type_size VT6
586 , v$type_size VT5
587 , v$type_size VT4
588 , v$type_size VT3
589 , v$type_size VT2
590 , v$type_size VT1
591 , v$parameter VP
592 where upper(VT7.TYPE) = 'SB2'
593 and upper(VT6.TYPE) = 'UB1'
594 and upper(VT5.TYPE) = 'KCBH'
595 and upper(VT4.TYPE) = 'UB4'
596 and upper(VT3.TYPE) = 'KTBBH'
597 and upper(VT2.TYPE) = 'KTBIT'
598 and upper(VT1.TYPE) = 'KDBH'
599 and upper(VP.name) = 'DB_BLOCK_SIZE';
600 l_size_params_row l_size_params_cur%ROWTYPE;
601
602 CURSOR l_kdbt_cur IS
603 select VT8.type_size kdbt
604 from v$type_size VT8
605 where upper(VT8.TYPE) = 'KDBT'
606 union
607 select VT9.type_size
608 from v$type_size VT9
609 where upper(VT9.TYPE) = 'KCBH'
610 and not exists (select VT0.type_size kdbt
611 from v$type_size VT0
612 where upper(VT0.TYPE) = 'KDBT');
613 l_kdbt NUMBER;
614
615 CURSOR l_table_cols_size_cur(c_tablename user_tables.table_name%TYPE) IS
616 select sum(data_length + decode(floor(data_length/250),0,1,3))
617 from user_tab_columns
618 where table_name = c_tablename;
619 l_table_cols_size NUMBER;
620
621 l_db_free_space NUMBER; -- holds free space in DB block after header
622 l_rowsize NUMBER; -- holds the size of a row in bytes
623 l_rowspace NUMBER; -- holds the overall size of a row in bytes
624 l_rows_per_block NUMBER; -- holds the number of rows that can fit in a block
625 l_blocks_needed NUMBER; -- the number of blocks needed to store this table
626
627 BEGIN
628
629 -- get size parameters
630 OPEN l_size_params_cur;
631 FETCH l_size_params_cur
632 INTO l_size_params_row;
633 CLOSE l_size_params_cur;
634
635 OPEN l_kdbt_cur;
636 FETCH l_kdbt_cur
637 INTO l_kdbt;
638 CLOSE l_kdbt_cur;
639
640 OPEN l_table_cols_size_cur(p_tablename);
641 FETCH l_table_cols_size_cur
642 INTO l_table_cols_size;
643 CLOSE l_table_cols_size_cur;
644
645 -- figure out db free space
646 l_db_free_space := (l_size_params_row.db_block_size
647 - l_size_params_row.kcbh
648 - l_size_params_row.ub4
649 - l_size_params_row.ktbbh
650 - ((p_initrans - 1) * l_size_params_row.ktbit)
651 - l_size_params_row.kdbh);
652
653 -- figure out available db free space
654 l_db_free_space := CEIL(l_db_free_space * (1 - p_pctfree/100))
655 - l_kdbt;
656
657 -- figure out the size of a row in bytes
658 l_rowsize := (3 * l_size_params_row.ub1) + l_table_cols_size;
659
660 -- the space a row takes up
661 l_rowspace := (l_size_params_row.ub1 * 3)
662 + l_size_params_row.ub4
663 + l_size_params_row.sb2;
664
665 IF (l_rowsize > l_rowspace) THEN
666 l_rowspace := l_rowsize + l_size_params_row.sb2;
667 ELSE
668 l_rowspace := l_rowspace + l_size_params_row.sb2;
669 END IF;
670
671 -- the number of rows per block
672 l_rows_per_block := floor(l_db_free_space/l_rowspace);
673
674 -- the number of blocks needed for storage
675 l_blocks_needed := ceil(p_rowcount/l_rows_per_block);
676
677 RETURN l_blocks_needed * l_size_params_row.db_block_size;
678
679 EXCEPTION
680
681 WHEN OTHERS THEN
682 GMA_PURGE_UTILITIES.printlong(p_purge_id,
683 'Problem raised in GMA_PURGE_DDL.tab_size with '
684 || p_tablename);
685 GMA_PURGE_UTILITIES.printlong(p_purge_id,
686 'Unhandled EXCEPTION - ' || sqlerrm);
687 RAISE;
688
689 END;
690
691 /***********************************************************/
692
693 PROCEDURE coalescetablespace
694 (p_purge_id sy_purg_mst.purge_id%TYPE,
695 p_tablespace_name user_tablespaces.tablespace_name%TYPE,
696 p_debug_flag BOOLEAN) IS
697 l_countstatement user_source.text%TYPE;
698 l_alterstatement user_source.text%TYPE;
699
700 l_cursor INTEGER;
701 l_currval INTEGER;
702 l_lastval INTEGER;
703 l_return INTEGER;
704
705 BEGIN
706 /*
707 l_countstatement := 'SELECT COUNT(*) ' ||
708 'FROM DBA_FREE_SPACE ' ||
709 'WHERE TABLESPACE_NAME = ' ||
710 '''' || p_tablespace_name || '''';
711 l_alterstatement := 'ALTER TABLESPACE ' ||
712 p_tablespace_name ||
713 ' COALESCE';
714
715 l_cursor := DBMS_SQL.OPEN_CURSOR;
716
717 -- get count from dba_free_space
718 GMA_PURGE_UTILITIES.printdebug(p_purge_id,l_countstatement,p_debug_flag);
719 DBMS_SQL.PARSE(l_cursor,l_countstatement,DBMS_SQL.NATIVE);
720 DBMS_SQL.DEFINE_COLUMN(l_cursor,1,l_currval);
721 l_return := DBMS_SQL.EXECUTE_AND_FETCH(l_cursor);
722 DBMS_SQL.COLUMN_VALUE(l_cursor,1,l_currval);
723
724 -- keep coalescing until effectiveness ends
725 l_lastval := 0;
726 WHILE (l_currval <> l_lastval) LOOP
727 l_lastval := l_currval;
728 GMA_PURGE_UTILITIES.printdebug(p_purge_id,l_alterstatement,p_debug_flag);
729 DBMS_SQL.PARSE(l_cursor,l_alterstatement,DBMS_SQL.NATIVE);
730 GMA_PURGE_UTILITIES.printdebug(p_purge_id,l_countstatement,p_debug_flag);
731 DBMS_SQL.PARSE(l_cursor,l_countstatement,DBMS_SQL.NATIVE);
732 DBMS_SQL.DEFINE_COLUMN(l_cursor,1,l_currval);
733 l_return := DBMS_SQL.EXECUTE_AND_FETCH(l_cursor);
734 DBMS_SQL.COLUMN_VALUE(l_cursor,1,l_currval);
735 IF (l_currval <> l_lastval) THEN
736 GMA_PURGE_UTILITIES.printlong(p_purge_id,
737 'Tablespace ' ||
738 p_tablespace_name ||
739 ' coalesced - ' ||
740 GMA_PURGE_UTILITIES.chartime);
741 END IF;
742
743 END LOOP;
744
745 DBMS_SQL.CLOSE_CURSOR(l_cursor);
746 */
747
748 RETURN;
749
750 /*
751 EXCEPTION
752
753 WHEN OTHERS THEN
754 GMA_PURGE_UTILITIES.printlong(p_purge_id,
755 'Problem raised in GMA_PURGE_DDL.coalescetablespace ' ||
756 'with '
757 || p_tablespace_name);
758 GMA_PURGE_UTILITIES.printlong(p_purge_id,
759 'Unhandled EXCEPTION - ' || sqlerrm);
760 RAISE;
761 */
762
763 END coalescetablespace;
764
765 /***********************************************************/
766
767 PROCEDURE disableindexes(p_purge_id sy_purg_mst.purge_id%TYPE,
768 p_tablenames_tab g_tablename_tab_type,
769 p_tableactions_tab g_tableaction_tab_type,
770 p_tablecount INTEGER,
771 p_indexes_tab IN OUT NOCOPY g_statement_tab_type,
772 p_indexcount IN OUT NOCOPY INTEGER,
773 p_owner user_users.username%TYPE,
774 p_appl_short_name fnd_application.application_short_name%TYPE,
775 p_debug_flag BOOLEAN) IS
776 -- This cursor selects all of the information that we'll need
777 -- to recreate the indexes later on the named table
778 CURSOR l_idx_details_cur(c_table_name user_tables.table_name%TYPE) IS
779 select UIX.index_name index_name
780 , UIX.table_name indexed_table
781 , decode(UIX.uniqueness,
782 'NONUNIQUE',NULL,
783 'UNIQUE' ,' UNIQUE',
784 NULL) uniqueness
785 , UIX.tablespace_name tablespace_name
786 , UIX.ini_trans ini_trans
787 , UIX.max_trans max_trans
788 , decode(nvl(UIX.initial_extent,9999999999),
789 9999999999,NULL,
790 ' INITIAL '
791 || to_char(UIX.initial_extent))
792 initial_extent
793 , decode(nvl(UIX.next_extent,9999999999),
794 9999999999,NULL,
795 ' NEXT '
796 || to_char(UIX.next_extent))
797 next_extent
798 , decode(nvl(UIX.freelists,9999999999),
799 9999999999,NULL,
800 ' FREELISTS '
801 || to_char(UIX.freelists))
802 freelists
803 , decode(nvl(UIX.freelist_groups,9999999999),
804 9999999999,NULL,
805 ' FREELIST GROUPS '
806 || to_char(UIX.freelist_groups))
807 freelist_groups
808 , UIX.min_extents min_extents
809 , UIX.max_extents max_extents
810 , UIX.pct_increase pct_increase
811 , UIX.pct_free pct_free
812 , UIC.table_name indexed_column_table
813 , UIC.column_name column_name
814 from user_ind_columns UIC
815 , user_indexes UIX
816 where UIC.index_name = UIX.index_name
817 and UIX.table_name = c_table_name
818 order by UIX.index_name
819 , UIC.column_position;
820
821 -- The name of the current index
822 l_current_idx user_indexes.index_name%TYPE := NULL;
823
824 -- These variables hold text fragments during index create statement
825 -- construction
826 l_sqlstatement user_source.text%TYPE := NULL;
827 l_runstatement user_source.text%TYPE := NULL;
828 l_sqlfront user_source.text%TYPE := NULL;
829 l_sqlback user_source.text%TYPE := NULL;
830 l_column_list user_source.text%TYPE := NULL;
831
832 l_cursor INTEGER;
833
834 BEGIN
835
836 p_indexcount := 0;
837
838 l_cursor := DBMS_SQL.OPEN_CURSOR;
839
840 -- loop through the tables
841 FOR l_tablecounter IN 0 .. (p_tablecount - 1) LOOP
842 -- we only care about indexes for tables marked for delete
843 IF (p_tableactions_tab(l_tablecounter) = 'D') THEN
844 -- loop through the rows from idx_details
845 FOR l_idx_details_row IN
846 l_idx_details_cur(UPPER(p_tablenames_tab(l_tablecounter))) LOOP
847
848 -- check to see if we're still on the same index
849 IF ((l_current_idx <> l_idx_details_row.index_name) OR
850 (l_current_idx IS NULL)) THEN
851
852 -- If not, finish the last index create statement before
853 -- starting the next
854 IF (l_current_idx IS NOT NULL) THEN
855
856 -- save create statement in table
857 l_sqlstatement := l_sqlfront ||
858 l_column_list ||
859 l_sqlback;
860 p_indexes_tab(p_indexcount) := l_sqlstatement;
861 p_indexcount := p_indexcount + 1;
862 GMA_PURGE_UTILITIES.printdebug(p_purge_id,
863 'Statement saved - will run later. => ' ||'
864 '||
865 l_sqlstatement,
866 p_debug_flag);
867
868 -- drop the index
869 l_sqlstatement := 'DROP INDEX ' || l_current_idx;
870 GMA_PURGE_UTILITIES.printdebug(p_purge_id,
871 l_sqlstatement,
872 p_debug_flag);
873 IF (GMA_PURGE_ENGINE.use_ad_ddl = TRUE) THEN
874 AD_DDL.DO_DDL(p_owner,p_appl_short_name,AD_DDL.DROP_INDEX,
875 l_sqlstatement,l_current_idx);
876 ELSE
877 DBMS_SQL.PARSE(l_cursor,l_sqlstatement,DBMS_SQL.NATIVE);
878 END IF;
879
880 GMA_PURGE_UTILITIES.printlong(p_purge_id,
881 l_current_idx ||
882 ' index dropped.');
883 END IF;
884
885 l_current_idx := l_idx_details_row.index_name;
886
887 -- start new create statement
888 l_sqlfront := 'CREATE' ||
889 l_idx_details_row.uniqueness ||
890 ' INDEX ' ||
891 l_idx_details_row.index_name ||
892 ' ON ' ||
893 l_idx_details_row.indexed_table ||
894 ' (';
895
896 l_column_list := l_idx_details_row.column_name;
897
898 l_sqlback := ') TABLESPACE ' ||
899 l_idx_details_row.tablespace_name ||
900 ' INITRANS ' ||
901 to_char(l_idx_details_row.ini_trans) ||
902 ' MAXTRANS ' ||
903 to_char(l_idx_details_row.max_trans) ||
904 ' PCTFREE ' ||
905 to_char(l_idx_details_row.pct_free) ||
906 ' STORAGE (' ||
907 l_idx_details_row.initial_extent ||
908 l_idx_details_row.next_extent ||
909 ' MINEXTENTS ' ||
910 to_char(l_idx_details_row.min_extents) ||
911 ' MAXEXTENTS ' ||
912 to_char(l_idx_details_row.max_extents) ||
913 ' PCTINCREASE ' ||
914 to_char(l_idx_details_row.pct_increase) ||
915 l_idx_details_row.freelists ||
916 l_idx_details_row.freelist_groups ||
917 ' ) ';
918
919 ELSE -- write all subsequent columns for the same index
920 -- to the column list
921 l_column_list := l_column_list ||
922 ',' ||
923 l_idx_details_row.column_name;
924 END IF;
925
926 END LOOP;
927 END IF;
928 END LOOP;
929
930 -- finish last statement
931 IF (l_current_idx IS NOT NULL) THEN
932 -- save create statement in table
933 l_sqlstatement := l_sqlfront ||
934 l_column_list ||
935 l_sqlback;
936 p_indexes_tab(p_indexcount) := l_sqlstatement;
937 p_indexcount := p_indexcount + 1;
938 GMA_PURGE_UTILITIES.printdebug(p_purge_id,'Statement saved - will run later. => ' ||'
939 '||
940 l_sqlstatement,
941 p_debug_flag);
942
943 -- drop the index
944 l_runstatement := 'DROP INDEX ' || l_current_idx;
945 GMA_PURGE_UTILITIES.printdebug(p_purge_id,
946 l_runstatement,
947 p_debug_flag);
948 IF (GMA_PURGE_ENGINE.use_ad_ddl = TRUE) THEN
949 AD_DDL.DO_DDL(p_owner,p_appl_short_name,AD_DDL.DROP_INDEX,
950 l_sqlstatement,l_current_idx);
951 ELSE
952 DBMS_SQL.PARSE(l_cursor,l_runstatement,DBMS_SQL.NATIVE);
953 END IF;
954
955 GMA_PURGE_UTILITIES.printlong(p_purge_id,
956 l_current_idx ||
957 ' index dropped.');
958 GMA_PURGE_UTILITIES.printlong(p_purge_id,'The following statement can be ' ||
959 'used to recreate the index in ' ||
960 'case of severe archive failure.');
961 GMA_PURGE_UTILITIES.printlong(p_purge_id,l_sqlstatement);
962
963 END IF;
964
965 DBMS_SQL.CLOSE_CURSOR(l_cursor);
966
967 RETURN;
968
969 EXCEPTION
970
971 WHEN OTHERS THEN
972 GMA_PURGE_UTILITIES.printlong(p_purge_id,
973 'Problem raised in GMA_PURGE_DDL.disableindexes');
974 GMA_PURGE_UTILITIES.printlong(p_purge_id,
975 'Unhandled EXCEPTION - ' || sqlerrm);
976 RAISE;
977
978 END disableindexes;
979
980 /***********************************************************/
981
982 PROCEDURE enableindexes(p_purge_id sy_purg_mst.purge_id%TYPE,
983 p_indexes_tab g_statement_tab_type,
984 p_indexcount INTEGER,
985 p_idx_tablespace_tab g_tablespace_name_tab_type,
986 p_idx_tablespace_count INTEGER,
987 p_owner user_users.username%TYPE,
988 p_appl_short_name fnd_application.application_short_name%TYPE,
989 p_debug_flag BOOLEAN) IS
990
991 l_cursor INTEGER;
992 l_sqlstatement user_source.text%TYPE;
993 l_indexname user_source.text%TYPE;
994 l_errortext user_source.text%TYPE;
995 l_parse_success BOOLEAN;
996
997 BEGIN
998
999 l_cursor := DBMS_SQL.OPEN_CURSOR;
1000
1001 -- coalesce any index tablespaces
1002 IF (p_idx_tablespace_count > 0) THEN
1003 FOR l_idx_tablespace_count IN 0 .. (p_idx_tablespace_count - 1) LOOP
1004 GMA_PURGE_DDL.coalescetablespace(p_purge_id,
1005 p_idx_tablespace_tab(l_idx_tablespace_count),
1006 p_debug_flag);
1007 END LOOP;
1008 END IF;
1009
1010 -- return if there are no saved index statements
1011 IF (p_indexcount <= 0) THEN
1012 RETURN;
1013 END IF;
1014
1015 -- recreate all of the indexes
1016 FOR l_counter IN 0 .. (p_indexcount - 1) LOOP
1017 l_parse_success := TRUE;
1018 l_sqlstatement := p_indexes_tab(l_counter);
1019
1020 -- extract index name
1021 l_indexname := l_sqlstatement;
1022 l_indexname := REPLACE(l_indexname,'CREATE ');
1023 l_indexname := REPLACE(l_indexname,'UNIQUE ');
1024 l_indexname := REPLACE(l_indexname,'INDEX ');
1025 l_indexname := SUBSTR(l_indexname,1,(INSTR(l_indexname,' ') - 1));
1026
1027 GMA_PURGE_UTILITIES.printdebug(p_purge_id,
1028 l_sqlstatement,
1029 p_debug_flag);
1030
1031 -- try to recreate index
1032 BEGIN
1033 IF (GMA_PURGE_ENGINE.use_ad_ddl = TRUE) THEN
1034 AD_DDL.DO_DDL(p_owner,p_appl_short_name,AD_DDL.CREATE_INDEX,
1035 l_sqlstatement,l_indexname);
1036 ELSE
1037 DBMS_SQL.PARSE(l_cursor,l_sqlstatement,DBMS_SQL.NATIVE);
1038 END IF;
1039 EXCEPTION
1040 WHEN OTHERS THEN
1041 l_parse_success := FALSE;
1042 l_errortext := SQLERRM;
1043 END;
1044
1045 IF (l_parse_success = TRUE) THEN
1046 GMA_PURGE_UTILITIES.printlong(p_purge_id,
1047 l_indexname ||
1048 ' index recreated.');
1049 ELSE
1050 GMA_PURGE_UTILITIES.printlong(p_purge_id,
1051 l_indexname ||
1052 ' index not recreated - ' ||
1053 l_errortext);
1054 END IF;
1055
1056 END LOOP;
1057
1058 DBMS_SQL.CLOSE_CURSOR(l_cursor);
1059
1060 RETURN;
1061
1062 EXCEPTION
1063
1064 WHEN OTHERS THEN
1065 GMA_PURGE_UTILITIES.printlong(p_purge_id,
1066 'Problem raised in GMA_PURGE_DDL.enableindexes');
1067 GMA_PURGE_UTILITIES.printlong(p_purge_id,
1068 'Unhandled EXCEPTION - ' || sqlerrm);
1069 RAISE;
1070
1071 END enableindexes;
1072
1073 END GMA_PURGE_DDL;