If you want to recreate your temp tablespace, then follow below steps. For changing the default tablespace also, below steps can be used.
Find the existing temp tablespace details
SQL> ; 1* select tablespace_name,file_name from dba_temp_files SQL> / TABLESPACE_NAME FILE_NAME ------------------------------ -------------------------------------------------------- TEMP /home/oracle/app/oracle/oradata/cdb1/orcl/orcl_temp01201 4-07-30_04-39-23-PM.dbf
Create another Temporary Tablespace TEMP1
CREATE TEMPORARY TABLESPACE TEMP1 TEMPFILE ‘/u01/app/oradata/DBACLASS/temp01′ SIZE 2G;
Move Default Database 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.status FROM v$session a,v$sort_usage b WHERE a.saddr = b.session_addr; ALTER SYSTEM KILL SESSION 'SID,SERIAL#' IMMEDIATE;
Drop the original temp tablespace.
Drop temp tablespace
DROP TABLESPACE temp INCLUDING CONTENTS AND DATAFILES;
If you want to change the name from TEMP1 to TEMP, then follow the same process as below.
Create TEMP tablespace
CREATE TEMPORARY TABLESPACE TEMP TEMPFILE /u01/app/temp/temp01′ SIZE 2000M;
Make TEMP as default tablespace
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp;
Drop temporary for tablespace temp1
DROP TABLESPACE temp1 INCLUDING CONTENTS AND DATAFILES;
Thanks for the article. One note, in the last step where you are dropping tablespace temp2, I think you meant temp1
Hi Jay,
Thanks for pointing the issue. It is TEMP1 only, I have corrected that.
DROP TABLESPACE temp INCLUDING CONTENTS AND DATAFILES;
Its running for last 3 hours, the size of temp files is of 31gb & 30 gb.
Is there any way to drop tables quickly.
Dear, Make sure you run below query to check if anyone using existing temp tablespace and kill them.
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;
Thanks for sharing. Thanks for sharing. It will help me to recreate temp tablespace.
Gracias por compartir !!
Hi Everyone,
Thank you for this useful tuto, i have only one thing to report i think you have missed the key word SESSION in the query : ALTER SYSTEM KILL ‘SID,SERIAL#’ IMMEDIATE; it should be like : ALTER SYSTEM KILL SESSION ‘SID,SERIAL#’ IMMEDIATE;
Thank yo
Thank you. Needed this info, made my day.