Drop and recreate Temporary tablespace
CREATE TEMPORARY TABLESPACE TEMP_01 TEMPFILE '/u02/oradata/DEV/temp_01.dbf' SIZE 500m autoextend on next 10m maxsize unlimited;
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP_01;
DROP TABLESPACE TEMP including contents and datafiles;
CREATE TEMPORARY TABLESPACE TEMP TEMPFILE '/u02/oradata/DEV/temp01.dbf' SIZE 500m autoextend on next 10m maxsize unlimited;
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP;
DROP TABLESPACE TEMP_01 including contents and datafiles;
Add extra temp files
================
alter tablespace temp add tempfile '/u02/oradata/DEV/temp02.dbf' size 500m reuse autoextend on next 10m maxsize unlimited;
alter tablespace temp add tempfile '/u02/oradata/DEV/temp03.dbf' size 500m reuse autoextend on next 10m maxsize unlimited;
check the undo size
==============
SELECT SUM(a.bytes)/(1024*1024*1024) as
UNDO_SIZE FROM v$datafile a,
v$tablespace b,
dba_tablespaces c
WHERE c.contents = 'UNDO' AND c.status = 'ONLINE'
AND b.name = c.tablespace_name AND a.ts# = b.ts#;
