Dba rebuild indexes

Fiche express
Type	Script SQL*Plus générateur de script
Domaine	Administration (index, réorganisation de stockage)
Voir aussi	Dba tablespace mapper · Dba segment summary · Dba tablespace to owner

Dba rebuild indexes interroge le dictionnaire de données pour produire — et non exécuter directement — un second script SQL contenant l'ensemble des commandes ALTER INDEX ... REBUILD nécessaires pour reconstruire tous les index d'un tablespace donné. Le script généré reprend les paramètres de stockage d'origine de chaque index (extents initiaux/suivants, min/max extents, pctincrease).

Utile après un import massif, une purge importante ou pour lutter contre la fragmentation d'index, sans avoir à ressaisir manuellement chaque commande.

Script

-- Génère un script de reconstruction des index d'un tablespace
-- (ALTER INDEX ... REBUILD ONLINE), avec les paramètres de stockage
-- d'origine de chaque index.

SET TERMOUT OFF
COLUMN current_instance NEW_VALUE current_instance NOPRINT
SELECT rpad(instance_name, 17) current_instance FROM v$instance;
SET TERMOUT ON

PROMPT
PROMPT +------------------------------------------------------------------------+
PROMPT | Report   : Rebuild Index Build Script                                  |
PROMPT | Instance : &current_instance                                          |
PROMPT +------------------------------------------------------------------------+

PROMPT
ACCEPT TS_NAME CHAR PROMPT 'Enter the index tablespace name : '

SET ECHO      OFF
SET FEEDBACK  OFF
SET HEADING   OFF
SET LINESIZE  180
SET PAGESIZE  0
SET TRIMSPOOL ON
SET VERIFY    OFF

CLEAR COLUMNS
CLEAR BREAKS
CLEAR COMPUTES

SPOOL rebuild_&TS_NAME._indexes.sql

SELECT 'REM FILE : rebuild_&TS_NAME._indexes.sql' FROM dual;
SELECT ' '                                          FROM dual;
SELECT 'REM ***** ALTER INDEX REBUILD commands for tablespace: &TS_NAME' FROM dual;
SELECT ' '                                          FROM dual;

SELECT
    'REM +------------------------------------------------------------------------+' || chr(10) ||
    'REM | INDEX NAME : ' || owner || '.' || segment_name
         || lpad('|', 58 - (length(owner) + length(segment_name))) || chr(10) ||
    'REM | BYTES      : ' || bytes   || lpad('|', 59 - length(bytes))   || chr(10) ||
    'REM | EXTENTS    : ' || extents || lpad('|', 59 - length(extents)) || chr(10) ||
    'REM +------------------------------------------------------------------------+' || chr(10) ||
    'ALTER INDEX ' || owner || '.' || segment_name || chr(10) ||
    '    REBUILD ONLINE'                            || chr(10) ||
    '    TABLESPACE ' || tablespace_name            || chr(10) ||
    '    STORAGE ('                                 || chr(10) ||
    '        INITIAL     ' || initial_extent        || chr(10) ||
    '        NEXT        ' || next_extent           || chr(10) ||
    '        MINEXTENTS  ' || min_extents           || chr(10) ||
    '        MAXEXTENTS  ' || max_extents           || chr(10) ||
    '        PCTINCREASE ' || pct_increase          || chr(10) ||
    ');' || chr(10) || chr(10)
FROM   dba_segments
WHERE  segment_type  = 'INDEX'
  AND  owner NOT IN ('SYS', 'SYSTEM')
  AND  tablespace_name = UPPER('&TS_NAME')
ORDER BY owner, bytes DESC
/

SPOOL OFF

SET TERMOUT ON
PROMPT
PROMPT Done... Built the script rebuild_&TS_NAME._indexes.sql
PROMPT

Utilisation

• Le script demande le nom du tablespace, puis génère rebuild_<tablespace>_indexes.sql dans le répertoire courant.
• Il suffit ensuite d'exécuter (@rebuild_<tablespace>_indexes.sql) le script produit, après relecture, pour lancer les reconstructions.
• REBUILD ONLINE minimise le verrouillage mais reste soumis aux mêmes contraintes que d'habitude (espace disque double le temps de la reconstruction, non disponible pour certains types d'index particuliers selon la version d'Oracle).

Limites

• Les index appartenant à SYS et SYSTEM sont volontairement exclus (dictionnaire de données).
• Les partitions d'index (segment_type = 'INDEX PARTITION') ne sont pas reprises : la syntaxe de reconstruction d'une partition (ALTER INDEX ... REBUILD PARTITION ...) diffère de celle d'un index non partitionné.
• Sur des tablespaces gérés localement (LMT), les paramètres de stockage (INITIAL, NEXT, PCTINCREASE...) sont en grande partie ignorés par Oracle ; ils restent utiles pour des tablespaces en gestion par dictionnaire (DMT), plus rares aujourd'hui.

Voir aussi

• Dba tablespace mapper — cartographie de l'occupation d'un tablespace, utile pour repérer la fragmentation avant reconstruction
• Dba segment summary — vue d'ensemble des segments par propriétaire et par type
• Dba tablespace to owner — répartition d'un tablespace entre propriétaires