— consultar archivos del tablespace actual

select tablespace_name,file_name from dba_temp_files;

— qué sesión esta usando el tablespace temp, para matarla antes de borrarlo/

select 'ALTER SYSTEM KILL SESSION ''' || s.sid || ',' ||s.serial# || ''' immediate;',
tu.username,s.sid,s.serial#, s.status  from v$tempseg_usage tu, v$session s
where tu.session_addr=s.saddr;

— Crear un tablespace temporal para poder borrar el actual y ponerlo por defecto

CREATE TEMPORARY TABLESPACE TEMP2 TEMPFILE '+DATA' SIZE 20M;
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP2;

— Borrar el tablespace actual

DROP TABLESPACE temp INCLUDING CONTENTS AND DATAFILES;

— Recrear el tablespace

CREATE TEMPORARY TABLESPACE TEMP TEMPFILE '+data' SIZE 30G;
alter tablespace temp add TEMPFILE '+data' SIZE 10G;

— volver a poner el tablespace por defecto y borrar el que se habia creado

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP;

DROP TABLESPACE temp2 INCLUDING CONTENTS AND DATAFILES;

SELECT a.tablespace_name,SIZEMB,
USAGEMB, (SIZEMB – USAGEMB) FREEMB
FROM ( SELECT SUM (bytes) / 1024 / 1024 SIZEMB, b.tablespace_name
FROM dba_data_files a, dba_tablespaces b
WHERE a.tablespace_name = b.tablespace_name AND b.contents like ‘UNDO’
GROUP BY b.tablespace_name) a,
( SELECT c.tablespace_name, SUM (bytes) / 1024 / 1024 USAGEMB
FROM DBA_UNDO_EXTENTS c
WHERE status <> ‘EXPIRED’
GROUP BY c.tablespace_name) b
WHERE a.tablespace_name = b.tablespace_name;