LLI Wiki — My Digital Knowledge Base

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

  1. 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;
    
  2. 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;
Mastodon LLIMastodon SocialFosstodon