PROCEDURE ORACLE DE DÉPLACEMENT D'INDEXES ET CONTRAINTES INVALIDES VERS DE NOUVEAUX TBS

---

Commentaire sur cette source (1)
Ajouter un commentaire et/ou une note

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.

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

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.

• REQUÊTE SQL POUR DÉTERMINER L'ESPACE OCCUPÉ DANS LES TABLESPACES SUR ORACLE
• PACKAGE ORACLE -- OUTILS APPLICATIFS

• SQL LECTURE IMPORT DE FICHIER TEXTE
• GÉNÉRER DES REQUETES INSERT À PARTIR DES DONNÉES EXISTANTES DANS UNE TABLE.
• SQL SERVER - PROCEDURE D'EDITION D'UN FICHIER PLAT (EXPORT CSV)
• PROCÉDURE INSERTION OU MISE À JOUR DONNÉES D'UN UTILISATEUR
• REQUETE SUPPRESSION DE DOUBLON

• REQUÊTE SQL POUR DÉTERMINER L'ESPACE OCCUPÉ DANS LES TABLESPACES SUR ORACLE
• AJOUT , SUPPRESSION INTERVALLE DE TEMPS EN PLSQL
• REQUETE SUPPRESSION DE DOUBLON SOUS ORACLE
• RECALCUL DES STATISTIQUES POUR AMÉLIORER LES PERFOMANCE D'UNE BASE SQLSERVER
• PACKAGE ORACLE -- OUTILS APPLICATIFS