A temporary tablespace is responsible for various database sorting operations. A temporary tablespace is part of the physical database files, which the Oracle control file will expect to exist under normal operations. Because the temporary tablespace doesn’t have any permanent objects stored within it, there is no change in the system change number (SCN) from the checkpoint process in the control file or file header. The database will continue to function normally, with the exception of creating temporary segments, which occurs when creating indexes or performing certain select statements. Because the temporary tablespace only has temporary data stored inside, this tablespace can be re-created and reassigned in the event of data file loss.
In this post, we will see how to create a new TEMP tablespace and drop the existing temporary tablespace.
While performing this activity, the existing temporary tablespace may have some existing live sessions. Due to this, oracle may not let us drop the existing temporary tablespace. Thus, first, we need to kill existing sessions before dropping temporary tablespace.
1. Following query will give you the tablespace name and datafile name along with the path of that data file.
SQL> select FILE_NAME,TABLESPACE_NAME from dba_temp_files;
2. 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;
3. 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;
4. Retrieve ‘SID_NUMBER’ and ‘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;
5. 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';
6. 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 the same step mentioned above to recreate the temp tablespace with old name.