L'autoextend est une merveilleuse invention qui a permis à de nombreux DBA de retrouver un cycle de sommeil quasi humain. Plus besoin d'être réveillé en pleine nuit si un des datafile venait à se remplir : l'autoextend reporte le problème au niveau des tablespaces, leur permettant d'attendre la saturation disques, voire volumes ou LUNs, si l'on veut utiliser un vocable plus moderne.

Nous voici donc avec des tablespaces devenus monstrueux… avec tout ce que cela a d'impact en cas de sauvegardes physiques, de temps de restauration, de maintenance, etc. Comment donc les réduire, sachant que la base de données, par nature, est peu prêteuse ? Ce qu'elle prend, elle ne le rend que rarement.

Nous nous baserons sur les hypothèses suivantes, afin d'éviter l'utilisation massive de variables dans les bribes de code SQL ci-dessous:

• nous avons un tablespace nommé TBS_DATA de plusieurs centaines de Go que nous souhaitons réduire;
• ce tablespace est utilisé par des objets se situant dans un schéma nommé SCH;
• le tablespace TBS_MAINT est un tablespace de maintenance, un espace de stockage temporaire.

• Une base comprend plusieurs tablespaces.
• Un tablespace est composé d'un ou plusieurs fichiers (datafiles).
• Un tablespace comprend plusieurs objets (couche logique) ou segments (couche physique).
• Un segment est composé de plusieurs extents, qui sont en fait des groupes de blocs contigus.

Dans ces vues système, passablement de colonnes sont dupliquées, ceci afin d'éviter des jointures multiples. On peut donc simplifier le modèle, pour le sujet qui nous occupe, et nous limiter aux vues dba_segments et dba_extents. Nous utiliserons aussi parfois d'autres vues anecdotiques.

Ajout, suppression, ajout, modifications, suppression, ajout… une table n'en finit pas de bouger. Oracle opère des suppressions logiques pour ne pas avoir à déplacer ses blocs et pointeurs trop fréquemment.

Dans un premier temps, il suffit de lui demander de libérer l'espace qui n'est plus alloué grâce à l'ordre ALTER TABLE … DEALLOCATE UNUSED.

La requête suivante génère les ordres à exécuter pour notre schéma donné

select 'ALTER TABLE '||owner||'.'||table_name||' DEALLOCATE UNUSED;' 
from dba_tables 
where owner ='SCH'
and temporary='N'

Si l'on souhaite réduire la taille d'un tablespace, on ne peut le faire qu'en réduisant la taille d'un ou plusieurs de ses fichiers sous-jacents (datafiles).

Si l'on souhaite réduire la taille d'un fichier, on ne peut que l'étêter de la partie non allouée qu'il pourrait avoir.

Ce point de troncature s'appelle, dans le jargon DBA, le high water mark, ou point de flottaison du datafile. Il correspond à l'adresse la plus haute du premier bloc d'un extent alloué à un objet dans un datafile particulier.

Certains programmes tels que Toad, permettent d'obtenir cette information aisément et sans besoin de compétences techniques spécifiques.

Parfois, le gain est risible… parfois, malgré cette information, le resize ne passe pas est vous affuble d'une superbe ORA-03297

Reste donc à trouver quel(s) objet(s) se situe(nt) dans la partie haute du fichier afin de les déplacer en priorité vers une zone plus basse.

La requête suivante nous fournit cette information :

select  F.FILE_NAME, e.owner, e.segment_name, e.block_id
from dba_extents e 
inner join dba_data_files f on E.FILE_ID=F.FILE_ID 
where  e.tablespace_name='TBS_DATA' and e.segment_type='TABLE' 
order by e.block_id desc

Mais comment donc déplacer des objets d'un tablespace à un autre?

Pour les tables simples, non partitionnées, la requête est de type

ALTER TABLE SCH.MaTable MOVE tablespace  TBS_MAINT ;

Pour les LOB (Large Object Binary), soit les colonnes ayant un type LOB, les blocs peuvent être dissociés de ceux de leur table, puisqu'il s'agit en fait d'un chaînage de blocs.

ALTER TABLE SCH.MaTable MOVE LOB (ColLOB) STORE AS (tablespace  TBS_MAINT) ;

À relever que si une table possède plusieurs colonnes de ce type, il peut être opportun de faire la modification en une fois :

ALTER TABLE SCH.MaTable MOVE LOB (ColLob1, ColLob2) STORE AS (tablespace  TBS_MAINT) ;

Pour les indexes simples, la requête est de type

ALTER INDEX SCH.MonIndex REBUILD tablespace  TBS_MAINT ;

Les requêtes suivantes sont des exemples vous permettant de générer les ordres SQL de déplacement pour vos divers objets:

Tables

select 'ALTER TABLE '||owner||'.'|| table_name
       || ' MOVE tablespace  TBS_MAINT;'
    from dba_tables
    where tablespace_name <> 'TBS_MAINT'  
    and owner ='SCH';

Colonnes de type LOB

select 'ALTER TABLE '||L.owner||'.'|| L.table_name
       || ' MOVE LOB('|| L.column_name ||') STORE AS (TABLESPACE TBS_MAINT) ;'
    from dba_indexes O inner join DBA_LOBS L ON O.INDEX_NAME=L.INDEX_NAME AND O.OWNER=L.OWNER
    where L.owner = 'SCH'
    and O.index_type = 'LOB'
    and O.tablespace_name <> 'TBS_MAINT' ;

Index

select 'ALTER INDEX '||owner||'.'||index_name||' REBUILD TABLESPACE TBS_MAINT;' 
from dba_indexes  
where tablespace_name <> 'TBS_MAINT' 
and owner ='SCH' 
and index_type like '%NORMAL' ;

En couplant les requêtes vues ci-dessus, nous pouvons donc générer le déplacement des tables dans l'ordre de leur localisation dans le datafile… et les déplacer sur un tablespace de maintenance.

select 'ALTER TABLE '||e.owner||'.'|| e.segment_name ||
         ' MOVE TABLESPACE TBS_MAINT ;  -- '||T.NUM_ROWS||' tuples, '|| T.blocks||' blocs' 
from dba_extents e  inner join dba_tables T on e.segment_name=t.table_name
where  e.tablespace_name='TBS_DATA' 
group by e.owner, e.segment_name , t.num_rows, T.BLOCKS
order by max(block_id) desc

Cette requête a l'avantage de vous donner, comme information en commentaire, le nombre de lignes et de blocs pour chaque table. Ces informations vous permettront de décider si cette maintenance peut être faite en journée ou non.

L'ordre ALTER TABLE a comme effet induit d'invalider tous les index relatifs aux tables déplacées. Veillez donc à revalider vos index aussitôt l'ordre de maintenance terminé. Dans le cas où votre paramètre SKIP_UNUSABLE_INDEXES serait à FALSE, cela peut avoir des conséquences bloquantes sur votre exploitation… dans le cas contraire, les conséquences ne seront sensibles qu'au niveau des performances.

select 'alter index '||owner||'.'||index_name ||' rebuild ;' 
from dba_indexes 
where status <> 'VALID'
and PARTITIONED='FALSE' ;

Un merci tout particulier à Claude Leloup pour sa relecture attentive.