En este documento podemos encontrar diversas consultas Para evaluar el estado del Tablespace TEMP, Si hace falta ampliarlo, su nos sobra…

SELECT tablespace_name,
	SUM(bytes)/1024/1024 MB, 
	SUM(blocks) BLOQUES
FROM DBA_TEMP_FILES 
GROUP BY tablespace_name

SELECT b.SID,
	b.saddr,
	a.username,
	osuser,
	program,
	TABLESPACE, 
	EXTENTS, 
	blocks 
FROM v$sort_usage a, 
	v$session b 
WHERE a.SESSION_ADDR=b.saddr

SELECT SYSDATE,
	V$SQL.SQL_TEXT,
	SUM(BLOCKS)*32 BYTES
FROM V$SORT_USAGE,
	V$SQL
WHERE V$SORT_USAGE.www.='TEMP'
AND V$SORT_USAGE.SQL_ID=V$SQL.SQL_ID
AND V$SORT_USAGE.USERNAME ='<Usuario>'
GROUP BY V$SQL.SQL_TEXT

SELECT V$SQL.SQL_TEXT
FROM V$SQL,
	V$SESSION
WHERE V$SQL.SQL_ID=V$SESSION.SQL_ID
	AND V$SESSION.USERNAME='<Usuario>'
	AND STATUS ='ACTIVE'

SELECT V$SORT_USAGE.USERNAME,
	SUM(V$SORT_USAGE.BLOCKS*32)/1024 Kb
FROM V$SORT_USAGE 
GROUP BY V$SORT_USAGE.USERNAME

SELECT tablespace_name, 
	used_extents,
	used_blocks , 
	free_extents, 
	free_blocks 
FROM v$sort_segment

 Cuando free_extents llega a 0 ---> ORA-01652 
 01652, 00000, "unable to extend temp segment by %s in tablespace %s"

• Cause: Failed to allocate an extent for temp segment in tablespace.
• Action: Use ALTER TABLESPACE ADD DATAFILE statement to add one or more

 En el caso de pérdida o corrupción de los datafiles que forman el 
 Tablespace Temp es necesario recrearlo. 
 Como una BBDD no puede dejar de tener un Tbs TEMP por defecto, 
 no basta con crear otro, hay que llevar a cabo el 
 siguiente procedimiento:

CREATE TEMPORARY TABLESPACE TEMP_BACKUP 
TEMPFILE '<Path>/Nombre_del_fichero' 
SIZE 64M REUSE AUTOEXTEND ON MAXSIZE  1024M 
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1024K;

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP_BACKUP;
 
SELECT * 
  FROM DATABASE_PROPERTIES 
 WHERE PROPERTY_NAME='DEFAULT_TEMP_TABLESPACE';

DROP TABLESPACE TEMP INCLUDING CONTENTS AND DATAFILES;

– Opcional (si gustas regresar al mismo temp anterior, pero recostruido)

CREATE TEMPORARY TABLESPACE TEMP TEMPFILE '<Path>/Nombre_del_fichero' 
SIZE 256M REUSE AUTOEXTEND ON MAXSIZE 256M EXTENT MANAGEMENT LOCAL 
  UNIFORM SIZE 1024K;
 
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP;
 
SELECT * FROM DATABASE_PROPERTIES 
WHERE PROPERTY_NAME='DEFAULT_TEMP_TABLESPACE';

DROP TABLESPACE TEMP_BACKUP INCLUDING CONTENTS AND DATAFILES;