[Home] [Help]
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,
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,
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:
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
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;
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
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
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
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;
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'
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
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;
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:
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,