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.