How to resize temporary tablespace

How to resize temporary tablespace

step.1

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;

step.2

Following query will create temp tablespace named: 'temp2' with 800 MB size along with auto-extend and maxsize unlimited.
sql> create temporary tablespace temp2 tempfile  '/u01/oradata/temp_02.dbf' size 800m autoextend on next 10m maxsize unlimited;

step.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 temp2;

step.4

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, serial#';
For example:
sql> alter system kill session '59,57391';

step.5

Now, we can drop old temporary tablespace without any trouble with following:
sql> drop tablespace temp2 including contents and datafiles;

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.