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.