0/5 (1 avis)
Snippet vu 10 762 fois - Téléchargée 32 fois
CREATE OR REPLACE PROCEDURE EDF_MOVE_IDX_CNS_FROM_TBS IS TYPE t_FK IS RECORD ( CNS_NAME VARCHAR2(120), ORIG_TABLE VARCHAR2(120), ORIG_COLS VARCHAR2(4000), FORE_TABLE VARCHAR2(120), FORE_COLS VARCHAR2(4000)); TYPE t_tab_FK IS TABLE OF t_FK INDEX BY BINARY_INTEGER; i_table_name VARCHAR2(120); i_index_name VARCHAR2(120); i_tablespace_name VARCHAR2(120); i_column_name VARCHAR2(120); i_position INTEGER; i_columns_list VARCHAR2(4000); i_test_idx_size INTEGER; i_test_tbs_size INTEGER; i_test_idx_exist INTEGER; i_fk_name VARCHAR2(120); i_fk_table_name VARCHAR2(120); i_fk_column_name VARCHAR2(120); i_fk_columns_list VARCHAR2(4000); i_tab_FK t_tab_FK; i INTEGER; i_nb_fk INTEGER; i_FK t_FK; i_dis_cns_name VARCHAR2(120); i_dis_table_name VARCHAR2(120); i_dis_tbs_name VARCHAR2(120); i_dis_column_name VARCHAR2(120); i_dis_columns_list VARCHAR2(4000); i_dis_pctfree INTEGER; i_dis_initrans INTEGER; i_dis_maxtrans INTEGER; i_dis_initial INTEGER; i_dis_next INTEGER; i_dis_minextents INTEGER; i_dis_maxextents INTEGER; i_dis_pctincrease INTEGER; i_dis_freelist_group INTEGER; i_dis_cns_chk_name VARCHAR2(120); i_dis_table_chk_name VARCHAR2(120); -- Contraintes existantes (Enabled) positionnées sur le mauvais TBS CURSOR cur_idx_tbs_tab IS SELECT TABLE_NAME, INDEX_NAME, 'IDX' || SUBSTR(TABLESPACE_NAME, 4) FROM USER_INDEXES WHERE TABLESPACE_NAME LIKE 'TAB%' AND INDEX_NAME IN ( SELECT CONSTRAINT_NAME FROM USER_CONSTRAINTS WHERE CONSTRAINT_TYPE = 'P'); CURSOR cur_cns_cols IS SELECT COLUMN_NAME, POSITION FROM USER_CONS_COLUMNS WHERE CONSTRAINT_NAME = i_index_name ORDER BY POSITION; CURSOR cur_fk_related IS SELECT CONSTRAINT_NAME, TABLE_NAME FROM USER_CONSTRAINTS WHERE R_CONSTRAINT_NAME = i_index_name; CURSOR cur_fk_cols IS SELECT COLUMN_NAME, POSITION FROM USER_CONS_COLUMNS WHERE CONSTRAINT_NAME = i_fk_name ORDER BY POSITION; -- Contraintes existantes (Disabled) positionnées sur le mauvais TBS CURSOR cur_dis_cns IS SELECT CONSTRAINT_NAME, TABLE_NAME FROM USER_CONSTRAINTS WHERE STATUS = 'DISABLED' AND CONSTRAINT_TYPE = 'P'; CURSOR cur_dis_cns_fk IS SELECT CONSTRAINT_NAME, TABLE_NAME FROM USER_CONSTRAINTS WHERE STATUS = 'DISABLED' AND CONSTRAINT_TYPE = 'R'; CURSOR cur_dis_cols IS SELECT COLUMN_NAME, POSITION FROM USER_CONS_COLUMNS WHERE CONSTRAINT_NAME = i_dis_cns_name ORDER BY POSITION; -- Indexes existants positionnés sur le mauvais TBS CURSOR cur_idx_tbs IS SELECT INDEX_NAME, 'IDX' || SUBSTR(TABLESPACE_NAME, 4) FROM USER_INDEXES WHERE TABLESPACE_NAME LIKE 'TAB%'; -- Contraintes de checking existantes disabled CURSOR cur_dis_cns_chk IS SELECT CONSTRAINT_NAME, TABLE_NAME FROM USER_CONSTRAINTS WHERE STATUS = 'DISABLED' AND CONSTRAINT_TYPE = 'C'; BEGIN -- ********************************************************************************** -- -- Prise en compte des contraintes (Enabled) positionnées sur le mauvais TBS -- -- ********************************************************************************** -- IF cur_idx_tbs_tab%ISOPEN THEN CLOSE cur_idx_tbs_tab; END IF; OPEN cur_idx_tbs_tab; LOOP -- Définition des contraintes ayant un index sur un tablespace de données FETCH cur_idx_tbs_tab INTO i_table_name, i_index_name, i_tablespace_name; EXIT WHEN cur_idx_tbs_tab%NOTFOUND; IF cur_cns_cols%ISOPEN THEN CLOSE cur_cns_cols; END IF; i_columns_list := NULL; OPEN cur_cns_cols; LOOP -- Définition des champs pris en compte par la contrainte FETCH cur_cns_cols INTO i_column_name, i_position; EXIT WHEN cur_cns_cols%NOTFOUND; IF i_position = 1 THEN i_columns_list := i_column_name; ELSE i_columns_list := i_columns_list || ', ' || i_column_name; END IF; END LOOP; CLOSE cur_cns_cols; IF cur_fk_related%ISOPEN THEN CLOSE cur_fk_related; END IF; OPEN cur_fk_related; i := 1; LOOP -- Définition des contraintes de clé étrangères référençant une contrainte ayant un index sur un tablespace de données FETCH cur_fk_related INTO i_fk_name, i_fk_table_name; EXIT WHEN cur_fk_related%NOTFOUND; IF cur_fk_cols%ISOPEN THEN CLOSE cur_fk_cols; END IF; OPEN cur_fk_cols; LOOP -- Définition des champs pris en compte par la contrainte de clé étrangère FETCH cur_fk_cols INTO i_fk_column_name, i_position; EXIT WHEN cur_fk_cols%NOTFOUND; IF i_position = 1 THEN i_fk_columns_list := i_fk_column_name; ELSE i_fk_columns_list := i_fk_columns_list || ', ' || i_fk_column_name; END IF; END LOOP; CLOSE cur_fk_cols; SELECT i_fk_name, i_table_name, i_columns_list, i_fk_table_name, i_fk_columns_list INTO i_tab_FK(i) FROM DUAL; i := i + 1; END LOOP; i_nb_fk := i; CLOSE cur_fk_related; SELECT INITIAL_EXTENT INTO i_test_idx_size FROM USER_INDEXES WHERE INDEX_NAME = i_index_name; SELECT SUM(BYTES) INTO i_test_tbs_size FROM USER_FREE_SPACE WHERE TABLESPACE_NAME = i_tablespace_name; IF ( i_test_tbs_size - i_test_idx_size > 0 ) THEN LOOP EXIT WHEN i <= 1; i_FK := i_tab_FK(i - 1); EXECUTE IMMEDIATE 'ALTER TABLE ' || i_FK.FORE_TABLE || ' DROP CONSTRAINT ' || i_FK.CNS_NAME; i := i - 1; END LOOP; EXECUTE IMMEDIATE 'ALTER INDEX ' || i_index_name || ' REBUILD TABLESPACE ' || i_tablespace_name; SELECT COUNT(*) INTO i_test_idx_exist FROM USER_INDEXES WHERE INDEX_NAME = i_index_name; IF i_test_idx_exist > 0 THEN EXECUTE IMMEDIATE 'ALTER TABLE ' || i_table_name || ' DROP CONSTRAINT ' || i_index_name; EXECUTE IMMEDIATE 'ALTER TABLE ' || i_table_name || ' ADD ( ' || 'CONSTRAINT ' || i_index_name || ' PRIMARY KEY (' || i_columns_list || ') USING INDEX ' || 'TABLESPACE ' || i_tablespace_name || ' )'; IF i_nb_fk > 1 THEN LOOP EXIT WHEN i > ( i_nb_fk - 1 ); i_FK := i_tab_FK(i); EXECUTE IMMEDIATE 'ALTER TABLE ' || i_FK.FORE_TABLE || ' ADD ( ' || 'CONSTRAINT ' || i_FK.CNS_NAME || ' FOREIGN KEY ( ' || i_FK.FORE_COLS || ' ) REFERENCES ' || i_FK.ORIG_TABLE || ' ( ' || i_FK.ORIG_COLS || ' ))'; i := i + 1; END LOOP; END IF; END IF; END IF; END LOOP; CLOSE cur_idx_tbs_tab; -- ********************************************************************************** -- -- Fin de prise en compte des contraintes (Enabled) positionnées sur le mauvais TBS -- -- ********************************************************************************** -- -- ********************************************************************************** -- -- Prise en compte des contraintes (Disabled) positionnées sur le mauvais TBS -- -- ********************************************************************************** -- IF cur_dis_cns%ISOPEN THEN CLOSE cur_dis_cns; END IF; OPEN cur_dis_cns; LOOP FETCH cur_dis_cns INTO i_dis_cns_name, i_dis_table_name; EXIT WHEN cur_dis_cns%NOTFOUND; SELECT 'IDX' || SUBSTR(TABLESPACE_NAME, 4) INTO i_dis_tbs_name FROM USER_TABLES WHERE TABLE_NAME = i_dis_table_name; SELECT INITIAL_EXTENT, NEXT_EXTENT, MIN_EXTENTS, MAX_EXTENTS, PCT_INCREASE INTO i_dis_initial, i_dis_next, i_dis_minextents, i_dis_maxextents, i_dis_pctincrease FROM USER_TABLESPACES WHERE TABLESPACE_NAME = i_dis_tbs_name; IF cur_dis_cols%ISOPEN THEN CLOSE cur_dis_cols; END IF; i_dis_columns_list := NULL; OPEN cur_dis_cols; LOOP -- Définition des champs pris en compte par la contrainte FETCH cur_dis_cols INTO i_dis_column_name, i_position; EXIT WHEN cur_dis_cols%NOTFOUND; IF i_position = 1 THEN i_dis_columns_list := i_dis_column_name; ELSE i_dis_columns_list := i_dis_columns_list || ', ' || i_dis_column_name; END IF; END LOOP; CLOSE cur_dis_cols; SELECT SUM(BYTES) INTO i_test_tbs_size FROM USER_FREE_SPACE WHERE TABLESPACE_NAME = i_dis_tbs_name; IF ( i_test_tbs_size - i_dis_initial > 0 ) THEN EXECUTE IMMEDIATE 'CREATE UNIQUE INDEX ' || i_dis_cns_name || ' ON ' || i_dis_table_name || ' ( ' || i_dis_columns_list || ' ) LOGGING TABLESPACE ' || i_dis_tbs_name || ' PCTFREE ' || i_dis_pctfree || ' INITRANS 2 MAXTRANS 255 STORAGE ( ' || ' INITIAL ' || i_dis_initial || ' NEXT ' || i_dis_next || ' MINEXTENTS ' || i_dis_minextents || ' MAXEXTENTS ' || i_dis_maxextents || ' PCTINCREASE ' || i_dis_pctincrease || ' FREELISTS 1 FREELIST GROUPS 1 BUFFER POOL DEFAULT ) ' || ' NOPARALLEL'; EXECUTE IMMEDIATE 'ALTER TABLE ' || i_table_name || ' ADD ( ' || 'CONSTRAINT ' || i_dis_cns_name || ' PRIMARY KEY (' || i_dis_columns_list || ') USING INDEX ' || 'TABLESPACE ' || i_dis_tbs_name || ' )'; END IF; END LOOP; CLOSE cur_dis_cns; IF cur_dis_cns_fk%ISOPEN THEN CLOSE cur_dis_cns_fk; END IF; OPEN cur_dis_cns_fk; LOOP FETCH cur_dis_cns_fk INTO i_dis_cns_name, i_dis_table_name; EXIT WHEN cur_dis_cns_fk%NOTFOUND; SELECT COUNT(*) INTO i_test_idx_exist FROM USER_INDEXES WHERE INDEX_NAME = i_dis_cns_name; IF i_test_idx_exist > 0 THEN EXECUTE IMMEDIATE 'ALTER TABLE ' || i_dis_table_name || ' ENABLE NOVALIDATE CONSTRAINT ' || i_dis_cns_name; END IF; END LOOP; CLOSE cur_dis_cns_fk; -- ********************************************************************************** -- -- Fin de prise en compte des contraintes (Disabled) positionnées sur le mauvais TBS -- -- ********************************************************************************** -- -- ********************************************************************************** -- -- Prise en compte des indexes positionnées sur le mauvais TBS -- -- ********************************************************************************** -- IF cur_idx_tbs%ISOPEN THEN CLOSE cur_idx_tbs; END IF; OPEN cur_idx_tbs; LOOP FETCH cur_idx_tbs INTO i_index_name, i_tablespace_name; EXIT WHEN cur_idx_tbs%NOTFOUND; SELECT SUM(BYTES) INTO i_test_tbs_size FROM USER_FREE_SPACE WHERE TABLESPACE_NAME = i_tablespace_name; SELECT INITIAL_EXTENT INTO i_test_idx_size FROM USER_INDEXES WHERE INDEX_NAME = i_index_name; IF ( i_test_tbs_size - i_test_idx_size > 0 ) THEN EXECUTE IMMEDIATE 'ALTER INDEX ' || i_index_name || ' REBUILD TABLESPACE ' || i_tablespace_name; END IF; END LOOP; CLOSE cur_idx_tbs; -- ********************************************************************************** -- -- Fin de prise en compte des indexes positionnées sur le mauvais TBS -- -- ********************************************************************************** -- -- ********************************************************************************** -- -- Prise en compte des contraintes de checking non validées -- -- ********************************************************************************** -- IF cur_dis_cns_chk%ISOPEN THEN CLOSE cur_dis_cns_chk; END IF; OPEN cur_dis_cns_chk; LOOP FETCH cur_dis_cns_chk INTO i_dis_cns_chk_name, i_dis_table_chk_name; EXIT WHEN cur_dis_cns_chk%NOTFOUND; EXECUTE IMMEDIATE 'ALTER TABLE ' || i_dis_table_chk_name || ' ENABLE NOVALIDATE CONSTRAINT ' || i_dis_cns_chk_name; END LOOP; CLOSE cur_dis_cns_chk; -- ********************************************************************************** -- -- Fin de prise en compte des contraintes de checking non validées -- -- ********************************************************************************** -- END; /
19 mars 2008 à 09:20
je pense qu'il faudrait tester cette procédure avec des LOBS:
ceux ci posent problème.
Vous n'êtes pas encore membre ?
inscrivez-vous, c'est gratuit et ça prend moins d'une minute !
Les membres obtiennent plus de réponses que les utilisateurs anonymes.
Le fait d'être membre vous permet d'avoir un suivi détaillé de vos demandes et codes sources.
Le fait d'être membre vous permet d'avoir des options supplémentaires.