Recreate TEMP Tablespace

Step 1: Create another temporary tablespace, TEMP2

create temporary tablespace TEMP2
tempfile 'd:\oracle\oradata\sid\TEMP02.DBF' size 100m
extent management local uniform size 100m;

alter database tempfile 'd:\oracle\oradata\sid\TEMP02.DBF' autoextend on next 100m maxsize unlimited;

Step 2: Set it as default temporary tablespace

alter database default temporary tablespace TEMP2;

Step 3: Make sure No sessions are using your Old Temp tablespace

Skip this step if you are recreating temp tablespace without any user accessing the db.

Find Session Number from V$SORT_USAGE:

SELECT USERNAME, SESSION_NUM, SESSION_ADDR FROM V$SORT_USAGE;

Find Session ID from V$SESSION:

If the resultset contains any tows then your next step will be to find the SID from the V$SESSION view. You can find session id by using SESSION_NUM or SESSION_ADDR from previous resultset.

SELECT SID, SERIAL#, STATUS FROM V$SESSION WHERE SERIAL#=SESSION_NUM;
OR
SELECT SID, SERIAL#, STATUS FROM V$SESSION WHERE SADDR=SESSION_ADDR;

Kill Session:

Now kill the session with IMMEDIATE.

ALTER SYSTEM KILL 'SID,SERIAL#' IMMEDIATE;

Step 4: Drop Old TEMP tablespace

drop tablespace TEMP including contents and datafiles;

Step 5: Recreate TEMP tablespace

create temporary tablespace TEMP
tempfile 'd:\oracle\oradata\sid\TEMP02.DBF' size 512m
extent management local uniform size 100m;

alter database  tempfile 'd:\oracle\oradata\sid\TEMP01.DBF' autoextend on next 100m maxsize unlimited;

Step 6: Set TEMP as the default Temporary Tablespace

alter database default temporary tablespace TEMP;

Step 7: Drop TEMP2

drop tablespace TEMP2 including contents and datafiles;