How to Drop and Recreate TEMP Tablespace in Oracle

Below are the steps to drop and recreate temp tablespace in Oracle. You can also assign default temporary tablespace to the newly created tablespace.

Steps to Recreate TEMP Tablespace

STEP 1: Check the existing temp tablespace name, size and the file name.

 SQL>  set lines 200
SQL>  col TABLESPACE_NAME for a10
SQL>  col file_name for a60
SQL> select tablespace_name,file_name,bytes/1024/1024/1024 from dba_temp_files;

TABLESPACE FILE_NAME BYTES/1024/1024/1024
--------------------------------------------------------
TEMP /scratch/u01/app/oracle/oradata/tmp01/temp001.dbf 11

STEP 2: Create another new Temporary Tablespace TEMP1

SQL> create temporary tablespace temp1 tempfile '/scratch/u01/app/oracle/oradata/tmp01/temp01.dbf' size 10G;

If You are using ASM then use diskgroup name.
SQL> show parameter db_create_file_dest
SQL> create temporary tablespace temp1 tempfile '+DATA1' size 10G;

STEP 3: Make new tablespace to the default temp tablespace

SQL> alter database default temporary tablespace temp1;

STEP 4: Check any sessions using temp tablespace

SELECT b.tablespace,b.segfile#,b.segblk#,b.blocks,a.sid,a.serial#,
a.username,a.osuser, a.status
FROM v$session a,v$sort_usage b
WHERE a.saddr = b.session_addr;

STEP 5: Kill sessions using Temp Tablespace

SQL> alter system kill session 'sid,serial#' immediate;

STEP 6: Drop older Temp Tablespace

SQL> drop tablespace temp INCLUDING CONTENTS AND DATAFILES;
Tablespace dropped.

Done.

Note: Size of temp tablespace depends on your requirement. You can allocate space and create temp tablespace based on the usage. You can also extend, add datafiles into the temp tablespace.

Sometimes temp tablespace size may grow over a period of time due to which it consume lots of disk space so in this case either you can recreate new temp tablespace or shrink the temp tablespace.