Moving existing Oracle temp datafiles

SET PAUSE ON
SET PAUSE 'Press Return to Continue'
SET PAGESIZE 60
SET LINESIZE 300
 
COL TABLESPACE_SIZE FOR 999,999,999,999
COL ALLOCATED_SPACE FOR 999,999,999,999
COL FREE_SPACE FOR 999,999,999,999
 
SELECT *
FROM   dba_temp_free_space
/

CREATE TEMPORARY TABLESPACE TEMP_NEW TEMPFILE '/dbapps/oracle/oradata/DWHD/temp/temp02.dbf' SIZE 500m autoextend on next 10m maxsize 16G;
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. */
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;

alter system kill session '211,11509';

select * from v$datafile where TS#='45';

--Get tablespacename
select * from v$tablespace where TS#='45';

DROP TABLESPACE TEMP1 including contents and datafiles;