How to Drop and Recreate Temporary Tablespace in Oracle



1) Find the existing temp tablespace details :

 select tablespace_name,file_name from dba_temp_files;

2) Create another Temporary Tablespace TEMP1

CREATE TEMPORARY TABLESPACE TEMP1 TEMPFILE ‘/u01/app/oradata/temp01′ SIZE 5G;

3) Assign new tablespace as  Default  temp tablespace.

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP1;

 If any sessions are using temp space, then kill them.

SELECT b.tablespace,b.segfile#,b.segblk#,b.blocks,a.sid,a.serial#,a.username,a.osuser, a.statusFROM v$session a,v$sort_usage WHERE a.saddr = b.session_addr;
 
ALTER SYSTEM KILL SESSION 'SID,SERIAL#' IMMEDIATE;

4) Drop the original temp tablespace?

DROP TABLESPACE temp INCLUDING CONTENTS AND DATAFILES;







Please do like and subscribe to my youtube channel: https://www.youtube.com/@foalabs If you like this post please follow,share and comment