/*
 * move temporary tablespace
 * 
 * nice reading on http://www.orafaq.com/node/2
 * 
 * Did you know 
 * TEMPFILEs are not recorded in the database's control file. 
 * This implies that one can just recreate them whenever you restore the database, 
 *  or after deleting them by accident.    
 */  

-- list of tempfiles
select * from v$tempfile;

-- wait for active users when leav temporary segments
select tablespace_name, CURRENT_USERS, TOTAL_BLOCKS, USED_BLOCKS, FREE_BLOCKS from v$sort_segment; 


-- move temporary tablespace to different partition
-- drop and create new ones
alter database tempfile '/ora/data/temp01.dbf' drop; 
alter tablespace temp add tempfile '/ora/data/temp01.dbf' size 1000M reuse; 
alter tablespace temp add tempfile '/ora/data/ORAWEB/temp01.dbf' size 500M reuse; 

-- where is default temporary tabelspace?
SELECT * FROM DATABASE_PROPERTIES where PROPERTY_NAME='DEFAULT_TEMP_TABLESPACE';