ORA-01652 : impossible d'étendre le segment temporaire dans le tablespace TEMP
Diagnostiquer et corriger l'erreur ORA-01652 : vérifier les fichiers temporaires, agrandir le tablespace TEMP et identifier les sessions qui consomment le tri.
L'erreur ORA-01652 indique qu'un segment temporaire (tri, jointure, agrégation) ne peut pas être étendu parce que le tablespace TEMP n'a plus d'espace libre et que ses fichiers temporaires ne peuvent pas grandir.
Vérifier la taille des blocs et les limites des fichiers
La taille des blocs de la base détermine la taille maximale d'un fichier de données :
select value from v$parameter where name = 'db_block_size';
| Block Sz | Max Datafile Sz (Go) | Max DB Sz (To) |
|---|---|---|
| 2 048 | 8 192 | 524 264 |
| 4 096 | 16 384 | 1 048 528 |
| 8 192 | 32 768 | 2 097 056 |
| 16 384 | 65 536 | 4 194 112 |
| 32 768 | 131 072 | 8 388 224 |
Lister les fichiers de données et leur taille maximale :
select bytes/1024/1024 as mb_size,
maxbytes/1024/1024 as maxsize_set,
x.*
from dba_data_files x
Activer l'extension automatique sans limite sur un fichier existant, ou ajouter un fichier de données :
alter database datafile 'path_to_your_file\that_file.DBF' autoextend on maxsize unlimited;
alter tablespace system add datafile 'path_to_your_datafiles_folder\name_of_df_you_want.dbf' size 10m autoextend on maxsize unlimited;
Diagnostic
-
Identifier les fichiers temporaires et leur tablespace :
select file_name, TABLESPACE_NAME from DBA_TEMP_FILES;Puis vérifier l'espace utilisé dans les segments de tri :
SELECT A.tablespace_name tablespace, D.mb_total, SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_used, D.mb_total - SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_free FROM v$sort_segment A, ( SELECT B.name, C.block_size, SUM (C.bytes) / 1024 / 1024 mb_total FROM v$tablespace B, v$tempfile C WHERE B.ts# = C.ts# GROUP BY B.name, C.block_size ) D WHERE A.tablespace_name = D.name GROUP BY A.tablespace_name, D.mb_total; -
Vérifier s'il reste de l'espace libre dans le tablespace temporaire.
Solutions temporaires
Agrandir le fichier temporaire existant :
ALTER DATABASE TEMPFILE '/u01/oradata/ORCL/temp01.dbf' RESIZE 3072M;
Ou ajouter un fichier temporaire au tablespace TEMP :
ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/oradata/ORCL/temp02.dbf' SIZE 1024M REUSE AUTOEXTEND ON NEXT 50M MAXSIZE 1024M;
Analyse de la cause
Identifier les sessions qui consomment le tablespace temporaire :
SELECT S.sid || ',' || S.serial# sid_serial, S.username, S.osuser, P.spid, S.module,
P.program, SUM (T.blocks) * TBS.block_size / 1024 / 1024 mb_used, T.tablespace,
COUNT(*) statements
FROM v$sort_usage T, v$session S, dba_tablespaces TBS, v$process P
WHERE T.session_addr = S.saddr
AND S.paddr = P.addr
AND T.tablespace = TBS.tablespace_name
GROUP BY S.sid, S.serial#, S.username, S.osuser, P.spid, S.module,
P.program, TBS.block_size, T.tablespace
ORDER BY mb_used;
Retrouver la requête SQL responsable de l'utilisation du tri :
SELECT S.sid || ',' || S.serial# sid_serial, S.username, Q.hash_value, Q.sql_text,
T.blocks * TBS.block_size / 1024 / 1024 mb_used, T.tablespace
FROM v$sort_usage T, v$session S, v$sqlarea Q, dba_tablespaces TBS
WHERE T.session_addr = S.saddr
AND T.sqladdr = Q.address
AND T.tablespace = TBS.tablespace_name
ORDER BY mb_used;