Resolviendo el error ORA-1652: unable to extend temp segment by 128 in tablespace TEMP
El error ORA-1652 es uno de los incidentes más temidos por los DBAs. La reacción más común suele ser añadir más archivos de datos (Tempfiles), pero la experiencia demuestra que el aumento en el consumo de espacio temporal es debido a un proceso o consulta ineficiente.
Cuando el error ocurre, el espacio en TEMP se libera automáticamente al fallar la sesión, borrando los registros en las vistas de tiempo real. Para investigar, debemos consultar el historial de sesiones activas (ASH) para encontrar el pico máximo de consumo de cada consulta.
SELECT
sql_id,
user_id,
program,
round(MAX(temp_space_allocated)/1024/1024/1024,2) AS max_temp_gb,
MIN(sample_time) AS inicio,
MAX(sample_time) AS fin
FROM v$active_session_history
WHERE temp_space_allocated > 0
AND sql_id IS NOT NULL
--Aqui colocamos la fecha y hora relacionada al error ORA-1652
AND sample_time BETWEEN TO_TIMESTAMP('08/04/2026 08:00', 'DD/MM/YYYY HH24:MI')
AND TO_TIMESTAMP('08/04/2026 08:30', 'DD/MM/YYYY HH24:MI')
GROUP BY sql_id, user_id, program
ORDER BY max_temp_gb DESC;
En nuestro caso, encontramos que el
SQL_ID 85v0ju71vfphg alcanzó un pico de 370 GB. Al revisar el reporte AWR (Automatic Workload Repository), la sección "Top SQL with Top Events" reveló lo siguiente:
- Evento: direct path write temp
- Wait Class: User I/O
- Top Row Source: HASH JOIN / HASH GROUP BY
DBMS_XPLAN.DISPLAY_AWR, detectamos dos problemas críticos:MERGE JOIN CARTESIAN: El motor intentó realizar un producto cartesiano (unir cada fila con todas las filas de otra tabla) debido a una condición de unión mal definida con una tabla. Esto multiplicó exponencialmente el set de datos intermedio.
SORT UNIQUE: El uso de
UNION(en lugar deUNION ALL) obligó a un ordenamiento masivo para eliminar duplicados de un set de datos que ya estaba inflado por el cartesiano.
Para solucionar esto, el primer paso es auditar la lógica de las uniones (JOINs): asegurar que todas las tablas tengan su predicado de unión correspondiente en el WHERE o en la cláusula ON.
Si la lógica es correcta pero el optimizador sigue eligiendo un cartesiano por un error de estimación en las estadísticas, podemos intervenir usando el hint /*+ NO_CARTESIAN */. Asimismo, evaluamos cambiar UNION por UNION ALL si sabemos que los resultados de ambas consultas son disjuntos o si los duplicados son aceptables.
