Je partage via ce post un script PL/SQL permettant la réorganisation de tables sous Oracle 10g & 11g, via la méthode ALTER TABLE MOVE.
Il est très utile sur des bases de données utilisant le partitionnement car il supporte la réorganisation de table partitionnées.
Les indexes de la table sont également reconstruits, faute de quoi l’ordre ALTER TABLE MOVE les laisse dans un état UNUSABLE.
Si les indexes sont partitionnés, le script supporte également le rebuild de ces derniers, partition par partition.
Noter que la méthode ALTER TABLE MOVE est à faire en dehors des plages d’ouvertures applicatives car elle rend les indexes unusable le temps de leur rebuild.
Le script se trouve en fin d’article.
Voyons un cas de test avec une table visant à stocker les collaborateurs d’une société avec une partition par site géographique.
CREATE TABLE EMP
(emp_id NUMBER(5),
emp_prenom VARCHAR2(30),
emp_nom VARCHAR2(30),
emp_site VARCHAR2(20),
CONSTRAINT PK_EMP PRIMARY KEY (emp_id))
PARTITION BY LIST(emp_site)
(
PARTITION EMP_PARIS VALUES('Paris'),
PARTITION EMP_NANTES VALUES ('Nantes'),
PARTITION EMP_LYON VALUES('Bordeaux'),
PARTITION EMP_LILLE VALUES('Brest'),
PARTITION EMP_AUTRES VALUES(DEFAULT)
);
Cette table stocke les entrées dans la partition correspondante à chaque ville sur la base du champ emp_site.
Si une requête sur la table utilise une clause « WHERE emp_site =
Créons un index partitionné LOCAL sur cette table afin d’avoir à la fois une table partitionnée et un index partitionné.
create index EMP_IDX_NOMPRENOM on EMP (emp_nom,emp_prenom) LOCAL;
Insérons une dizaine de lignes dans la table :
insert into EMP values (1,'Christophe','Carpentier','Paris'); insert into EMP values (2,'Charlotte','Morvan','Paris'); insert into EMP values (3,'Robert','Ledoux','Nantes'); insert into EMP values (4,'Nathalie','Marie','Nantes'); insert into EMP values (5,'Aurore','Pasquier','Bordeaux'); insert into EMP values (6,'Olivier','Jean','Bordeaux'); insert into EMP values (7,'René','Laroche','Brest'); insert into EMP values (8,'Paul','Fournier','Brest'); insert into EMP values (9,'Rene','Dupont','Brest'); insert into EMP values (10,'Bernard','Maurice','Brest');
Vérifions la présence des objets partitionnés :
set line 300 col table_name for a30; col index_name for a30; col partition_name for a30; select table_name,partition_name from user_tab_partitions; select index_name,partition_name from user_ind_partitions;
Voici l’appel du script pour la réorganisation de cette table.
Il prend en paramètre le schéma et le nom de la table visée :
[oracle@orapri scripts]$ ./reorg_table.sh TESTSCHEMA EMP [INFO] - Reorganisation de la table_partitionee EMP [INFO] ----> Reorganisation de la partition de table EMP_AUTRES [INFO] ----> Reorganisation de la partition de table EMP_LILLE [INFO] ----> Reorganisation de la partition de table EMP_LYON [INFO] ----> Reorganisation de la partition de table EMP_NANTES [INFO] ----> Reorganisation de la partition de table EMP_PARIS [INFO] - Rebuild de l'index PK_EMP [INFO] - Rebuild de l'index partitionne EMP_IDX_NOMPRENOM [INFO] ----> Rebuild de la partition d'index EMP_AUTRES [INFO] ----> Rebuild de la partition d'index EMP_LILLE [INFO] ----> Rebuild de la partition d'index EMP_LYON [INFO] ----> Rebuild de la partition d'index EMP_NANTES [INFO] ----> Rebuild de la partition d'index EMP_PARIS Elapsed: 00:00:00.48
Voici les sources du script :
#!/bin/ksh ################################################################### # Ce script reorganise la table en parametre et tous ses indexes # ################################################################### # + Supporte les tables partitionnees # # + Supporte les indexes partitionnes # ################################################################### #Rappel d'usage if [ $# -lt 2 ] then echo 'USAGE : ksh reorg_tables.ksh' exit 1 fi schema=$1 table=$2 log=reorg_tables.log sqlplus -s << ENDOFSQL / as sysdba -- Parametres SQLPLUS set lines 80; set feedback off; set serveroutput on; set timing on; spool ${log}; -- Debut du traitement dynamique DECLARE -- Curseur contenant l'ensemble des tables a reorganiser dans ce lot 1 CURSOR C_ALL_TABLES IS ( SELECT owner,table_name,partitioned FROM dba_tables WHERE owner = '${schema}' AND table_name = '${table}' ); BEGIN -- Parcours de toutes les tables FOR all_tab IN C_ALL_TABLES LOOP -- Si la table n'est pas partitionnee IF all_tab.partitioned = 'NO' THEN -- Ligne ajoutee au journal DBMS_OUTPUT.PUT_LINE('[INFO] - Reorganisation de la table '||all_tab.table_name); -- Execution de la reorganisation EXECUTE IMMEDIATE ('ALTER TABLE "'||all_tab.owner||'"."'||all_tab.table_name||'" MOVE PARALLEL 64'); -- Repositionnement du NOPARALLEL sur la table EXECUTE IMMEDIATE ('ALTER TABLE "'||all_tab.owner||'"."'||all_tab.table_name||'" NOPARALLEL'); -- Reorganisation du ou des index(es) de la table DECLARE -- Curseur contenant les indexes de la table CURSOR C_ALL_INDEXES IS ( SELECT owner,index_name,partitioned FROM dba_indexes WHERE owner = all_tab.owner AND index_type = 'NORMAL' AND table_name = all_tab.table_name ); BEGIN FOR all_idx IN C_ALL_INDEXES LOOP -- Si l'index n'est pas partitionne IF all_idx.partitioned = 'NO' THEN -- Ligne ajoutee au journal DBMS_OUTPUT.PUT_LINE('[INFO] - Rebuild de l''index '||all_idx.index_name); -- Execution du rebuild de l'index EXECUTE IMMEDIATE('ALTER INDEX "'||all_idx.owner||'"."'||all_idx.index_name||'" REBUILD PARALLEL 64'); -- Repositionnement du NOPARALLEL sur l'index EXECUTE IMMEDIATE('ALTER INDEX "'||all_idx.owner||'"."'||all_idx.index_name||'" NOPARALLEL'); -- Si l'index est partitionne ELSE DECLARE -- Curseur contenant les partitions de l'index CURSOR C_IDX_PARTITIONS IS ( SELECT partition_name FROM dba_ind_partitions WHERE index_name = all_idx.index_name); BEGIN DBMS_OUTPUT.PUT_LINE('[INFO] - Rebuild de l''index partitionne '||all_idx.index_name); -- Parcours des partitions d'indexes FOR idx_partitions IN C_IDX_PARTITIONS LOOP -- Ligne ajoutee au journal DBMS_OUTPUT.PUT_LINE('[INFO] ----> Rebuild de la partition d''index '||idx_partitions.partition_name); -- Execution du rebuild EXECUTE IMMEDIATE('ALTER INDEX "'||all_idx.owner||'"."'||all_idx.index_name||'" REBUILD PARTITION "'||idx_partitions.partition_name||'" PARALLEL 64'); END LOOP; -- Repositionnement du NOPARALLEL sur l'index EXECUTE IMMEDIATE('ALTER INDEX "'||all_idx.owner||'"."'||all_idx.index_name||'" NOPARALLEL'); END; END IF; END LOOP; END; -- Si la table est partitionee ELSE -- Reorganisation de la table partitionnee DECLARE -- Curseur contenant les partitions de la table CURSOR C_ALL_TABLE_PARTS IS ( SELECT partition_name FROM dba_tab_partitions WHERE table_name = all_tab.table_name AND table_owner = all_tab.owner ); BEGIN -- Ligne ajoutee au journal DBMS_OUTPUT.PUT_LINE('[INFO] - Reorganisation de la table_partitionee '||all_tab.table_name); -- Parcours des partitions de la table FOR all_tab_partition IN C_ALL_TABLE_PARTS LOOP -- Ligne ajoutee au journal DBMS_OUTPUT.PUT_LINE('[INFO] ----> Reorganisation de la partition de table '||all_tab_partition.partition_name); -- Execution de la reorg de la partition EXECUTE IMMEDIATE('ALTER TABLE "'||all_tab.owner||'"."'||all_tab.table_name||'" MOVE PARTITION "'||all_tab_partition.partition_name||'"'); END LOOP; -- Repositionnement du NOPARALLEL sur la table EXECUTE IMMEDIATE('ALTER TABLE "'||all_tab.owner||'"."'||all_tab.table_name||'" NOPARALLEL'); END; -- Reorganisation du ou des index(es) de la table DECLARE -- Curseur contenant les indexes de la table CURSOR C_ALL_INDEXES IS ( SELECT owner,index_name,partitioned FROM dba_indexes WHERE owner = all_tab.owner AND index_type = 'NORMAL' AND table_name = all_tab.table_name ); BEGIN FOR all_idx IN C_ALL_INDEXES LOOP -- Si l'index n'est pas partitionne IF all_idx.partitioned = 'NO' THEN -- Ligne ajoutee au journal DBMS_OUTPUT.PUT_LINE('[INFO] - Rebuild de l''index '||all_idx.index_name); -- Execution du rebuild de l'index EXECUTE IMMEDIATE('ALTER INDEX "'||all_idx.owner||'"."'||all_idx.index_name||'" REBUILD PARALLEL 64'); -- Repositionnement du NOPARALLEL sur l'index EXECUTE IMMEDIATE('ALTER INDEX "'||all_idx.owner||'"."'||all_idx.index_name||'" NOPARALLEL'); -- Si l'index est partitionne ELSE DECLARE -- Curseur contenant les partitions de l'index CURSOR C_IDX_PARTITIONS IS ( SELECT partition_name FROM dba_ind_partitions WHERE index_name = all_idx.index_name ); BEGIN DBMS_OUTPUT.PUT_LINE('[INFO] - Rebuild de l''index partitionne '||all_idx.index_name); -- Parcours des partitions d'indexes FOR idx_partitions IN C_IDX_PARTITIONS LOOP -- Ligne ajoutee au journal DBMS_OUTPUT.PUT_LINE('[INFO] ----> Rebuild de la partition d''index '||idx_partitions.partition_name); -- Execution du rebuild EXECUTE IMMEDIATE('ALTER INDEX "'||all_idx.owner||'"."'||all_idx.index_name||'" REBUILD PARTITION "'||idx_partitions.partition_name||'" PARALLEL 64'); END LOOP; -- Repositionnement du NOPARALLEL sur l'index EXECUTE IMMEDIATE('ALTER INDEX "'||all_idx.owner||'"."'||all_idx.index_name||'" NOPARALLEL'); END; END IF; END LOOP; END; END IF; END LOOP; END; / ENDOFSQL exit 0
