Procedure oracle de déplacement d'indexes et contraintes invalides vers de nouveaux tbs

Commenter

Contenu du snippet

Voici un petit bout de code que j'ai écrit dans le cadre de mon travail.
La problématique était qu'un certain nombre d'indexes liés à des contraintes d'intégrité n'étaient pas créés sur le bon TableSpace (TBS).
Il est à noter que les TBS de données de ma base Oracle sont tous només TAB_%, et les TBS d'index de ma base sont només IDX_%.
Ma solution a donc été de créer ce code qui réalise les opérations suivantes:
  • Déplacement des indexes positionnés sur les mauvais TBS vers les nouveaux TBS.
  • Drop et re-création des contraintes liées à ces indexes.
  • Création sur les bons TBS des indexes liés à des contraintes invalidées.
  • Re-validation des contraintes invalidées.


Note: attention à l'espace disque nécessaire pour la création et/ou re-création des indexes qui peut excéder l'espace disque disponible.

Source / Exemple : 


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;
/

Conclusion :


Ce code n'est sans doute pas exceptionnel mais il a fait l'affaire pour mes besoins spécifiques et j'espère qu'il pourra en aider d'autres.

A voir également

S'inscrire maintenant

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.

Du même auteur (sgoriaud)