In this article, I will explain you the step by step guide to create new TEMP tablespace and drop existing temporary tablespace.
While doing this activity, existing temporary tablespace may have existing live sessions, due to same oracle won’t let us to drop existing temporary tablespace. Resulting, we need to kill existing session before dropping temporary tablespace.
Following query will give you tablespace name and datafile name along with path of that data file.
SQL> select FILE_NAME,TABLESPACE_NAME from dba_temp_files;
Following query will create temp tablespace named: ‘TEMP_NEW’ with 500 MB size along with auto-extend and maxsize unlimited.
SQL> CREATE TEMPORARY TABLESPACE TEMP_NEW TEMPFILE '/DATA/database/ifsprod/temp_01.dbf' SIZE 500m autoextend on next 10m maxsize unlimited;
Following query will help you to alter database for default temporary tablespace. ( i.e. Newly created temp tablespce: ‘TEMP_NEW’ )
SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP_NEW;
Retrieve ‘SID_NUMBER’ & ‘SERIAL#NUMBER’ of existing live session’s who are using old temporary tablespace ( i.e. TEMP ) and kill them.
SQL> SELECT b.tablespace,b.segfile#,b.segblk#,b.blocks,a.sid,a.serial#,
FROM v$session a,v$sort_usage b
WHERE a.saddr = b.session_addr;
Provide above inputs to following query, and kill session’s.
SQL> alter system kill session 'SID_NUMBER, SERIAL#NUMBER';
SQL> alter system kill session '59,57391';
Now, we can drop old temporary tablespace without any trouble with following:
SQL> DROP TABLESPACE old_temp_tablespace including contents and datafiles;
Contents and datafiles are deleted successfully.
If you wish to continue with old temporary tablespace name, i.e. ‘TEMP’ then follow same step mentioned above to recreate temp tablespace with old name.
Stay tune. 🙂
17 thoughts on “How to Create TEMPORARY tablespace and drop existing temporary tablespace in oracle 11g”
Thanks, it was straight forward and helpful.
Thank you Anand for Writing.
Stay tune. 🙂
Thanks for writing it simply and “to the point”.
Thank you Meddy for writing. Stay Tune. 🙂
temporary is misspelt in title.
Thanks Duncan for writing, changes done.
I appreciate your comment.
Thank you! Great instructions.
Thank you Jignesh!
Welcome Vikram. Stay Tune. 🙂
Thank you that was helpful, I tried shrinking it never worked even after bouncing the database. I will rename the existing tablesapce to temp_old and create the new tablespace temp, that way you can keep the existing name.
Thank you, this was helpful 🙂
Awesome thank you
You are most welcome.
Stay Tune. 🙂
It was very helpful.
It would be good if you can explain the steps required to perform the same activity ( change default temporary tablespace) in active (READ ONLY) standby database .
thank sin advance.
Follow same steps
Is there any danger of corrupting data or stopping important queries when killing the live sessions on the old temp? or will they just try to start again but using the new temp tablespace this time?
Thanks in advance
Thanks a lot 🙂 🙂 🙂