Le serveur de bases de données Oracle utilise des tablespaces au format .DBF, par exemple USERS01.DBF pour le tablespace USERS. Le fichier d’échange temporaire TEMP dispose également de son propre fichier système : TEMP01.DBF. Le tablespace temporaire est utilisé pour stocker les tris qui ne peuvent pas être exécutés en mémoire (RAM).
Ce tutoriel a été réalisé sur un serveur Oracle 12c mais les autres éditions sont également compatibles (11g, 10g, 9i). Le système d’exploitation exemple est Windows Server mais ces requêtes seront les mêmes sur un serveur Linux / UNIX. Exemple ci-dessous avec un fichier TEMP01.DBF qui pèse 32 / 33 Go.
Erreur liée au tablespace temporaire Oracle trop plein
Deux exemples d’erreur qui peuvent s’afficher lorsque le fichier du tablespace TEMP est rempli à 100%.
Lors d’une requête trop importante :
ORA-01652: impossible d’étendre le segment temporaire de 128 dans le tablespace TEMP
00000 – « unable to extend temp segment by %s in tablespace %s »
*Cause: Failed to allocate an extent of the required number of blocks for a temporary segment in the tablespace indicated.
*Action: Use ALTER TABLESPACE ADD DATAFILE statement to add one or more files to the tablespace indicated.
Ou lors d’un import de base de données :
ORA-39171: Le travail se heurte à une attente avec possibilité de reprise.
Resumable error: ORA-01652: impossible d’étendre le segment temporaire de 128 dans le tablespace TEMP
Resumable stmt: BEGIN SYS.KUPW$WORKER.MAIN(‘SYS_IMPORT_FULL_01’, ‘NOM’, 0); END;
Resumable stmt status: SUSPENDED
Dans quels cas suivre ce tutoriel ?
Le fichier lié au tablespace Temp peut être configuré en Autoextend pour qu’il puisse s’agrandir au fur et à mesure des besoins. Il n’est pas normal que le fichier pèse plusieurs dizaines de gigaoctets mais ceci peut arriver selon le type d’utilisation du serveur Oracle (test chez un éditeur de logiciel, de trop nombreux kill de sessions, des requêtes gigantesques…). On pourrait simplement vider ou réduire ce tablespace mais ce n’est pas toujours possible. Si on ne peut pas agrandir le fichier TEMP01.DBF ou qu’on ne peut pas créer un TEMP02.DBF, il reste la solution de supprimer le fichier et de le créer à vide pour que le SGBD Oracle puisse à nouveau l’utiliser correctement. Cette procédure peut impliquer un arrêt temporaire du serveur de base de données.
Ce n’est évidemment pas une solution pérenne que de supprimer et recréer le tablespace temporaire car la même situation pourra se reproduire dans quelques temps. Ce tutoriel peut servir aux serveurs de test mais également dépanner une situation bloquante sur un serveur Oracle de production. L’idéal étant ensuite de chercher à comprendre ce qui consomme du temp et pourquoi pas créer différents fichiers temporaires pour identifier les consommations excessives.
Effacer et recréer le tablespace temporaire Oracle
1. Ouvrir une console SQLPlus en compte équivalent ‘sys’ ou un Invite de commandes et exécuter « sqlplus / as sysdba » pour être directement connecté avec un utilisateur ‘sys’. L’utilisation du cmd apporte le support du copier / coller pour les commandes qui seront à taper ensuite.
2. Supprimer le fichier du tablespace temporaire Oracle :
ALTER DATABASE TEMPFILE '[chemin du fichier]\TEMP01.DBF' DROP INCLUDING DATAFILES;
Par exemple : ALTER DATABASE TEMPFILE ‘D:\oracle\oradata\orcl\TEMP01.DBF’ DROP INCLUDING DATAFILES;
3. Le fichier TEMP01.DBF a été supprimé et le disque dur du serveur a récupéré la taille complète du fichier en question.
4. Créer un nouveau fichier TEMP01.DBF et l’attribuer au tempfile Oracle. Ici, on fixe sa taille initiale à 500Mo en autorisant l’extension automatique. Sur Windows, ce fichier DBF va grossir jusqu’à la taille de 32Go ou 33Go.
ALTER TABLESPACE TEMP ADD TEMPFILE '[chemin du fichier]\TEMP01.DBF' SIZE 500M REUSE AUTOEXTEND ON;
Par exemple : ALTER TABLESPACE TEMP ADD TEMPFILE ‘D:\oracle\oradata\orcl\TEMP01.DBF’ SIZE 500M REUSE AUTOEXTEND ON;
Erreur ORA-25152: TEMPFILE ne peut pas être supprimé maintenant
S’il y a un message d’erreur du type « ERREUR à la ligne 1 : ORA-25152: TEMPFILE ne peut pas être supprimé maintenant » : il faudra arrêter et relancer le service OracleServiceORCL (ou autre nom d’instance).
L’arrêter ne servirait à rien puisqu’il faut que le service Oracle soit démarré pour se connecter en SQL*Plus.
