LLI Wiki — My Digital Knowledge Base

Oracle : réduire les fichiers temporaires à leur taille utile

Calculer le high water mark des tempfiles Oracle et générer les ordres ALTER DATABASE TEMPFILE ... RESIZE pour récupérer l'espace inutilisé.

Les fichiers temporaires Oracle (tempfiles) ne reviennent pas à leur taille utile après un pic d'utilisation. Les requêtes ci-dessous comparent la taille actuelle de chaque fichier à sa taille minimale utile, appelée high water mark (HWM), puis génèrent les ordres de redimensionnement. La méthode est reprise de pavandba.com.

Récupérer la taille de bloc

Les requêtes utilisent la taille de bloc de la base, stockée dans la variable de substitution blksize.

column value new_val blksize
select value from v$parameter where name = 'db_block_size'
/

Afficher la taille actuelle et la taille minimale

select file_name,
       ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) smallest,
       ceil( blocks*&&blksize/1024/1024) currsize,
       ceil( blocks*&&blksize/1024/1024) - ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) savings
from dba_temp_files a,
     ( select file_id, max(block_id+blocks-1) hwm
         from dba_extents
        group by file_id ) b
where a.file_id = b.file_id(+)
/
Colonne Signification
smallest Taille minimale du fichier en Mo, d'après le HWM
currsize Taille actuelle du fichier en Mo
savings Espace récupérable en Mo

Générer les ordres de redimensionnement

Cette requête ne retient que les fichiers dont savings est supérieur à 0.

column cmd format a75 word_wrapped

select
   'alter database tempfile "' || file_name || '" resize ' || ceil( (nvl(hwm,1)*&&blksize)/1024/1024 )  || 'm;' cmd
from dba_temp_files a,
     ( select file_id, max(block_id+blocks-1) hwm
         from dba_extents
        group by file_id ) b
where a.file_id = b.file_id(+)
  and ceil( blocks*&&blksize/1024/1024) - ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) > 0
/

Exemple d'ordre généré :

alter database tempfile '/path/to/temp01.dbf' resize 30844m;

Relire les ordres générés avant de les exécuter.

Mastodon LLIMastodon SocialFosstodon