Re-Create 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;

or

SQL> CREATE TEMPORARY TABLESPACE TEMP_NEW;

Tablespace created.

SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP_NEW;

Database altered.

SQL> DROP TABLESPACE TEMP including contents and datafiles;  --DROP OLD ONE (TEMP)

Tablespace dropped.

SQL> CREATE TEMPORARY TABLESPACE TEMP;                       --RECREATE as TEMP

Tablespace created.

SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP;

Database altered.

SQL> DROP TABLESPACE TEMP_NEW including contents and datafiles;

Tablespace dropped.

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';

or

SQL> alter system kill session 'SID_NUMBER, SERIAL#NUMBER,@INST_ID';




For example:

SQL> alter system kill session '59,57391';

or

SQL> alter system kill session '59,57391,@1';

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.