How to Create TEMPORARY tablespace and drop existing temporary tablespace in oracle 11g

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#,
a.username,a.osuser, a.status
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';

For example:

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”

  1. 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.

  2. 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.

  3. 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

Leave a Reply