RE: Temp Tablespace

Add a new tablespace temp2 size it as you want it, switch over all you users to it, and then drop the original one. If you are version 10g you can also rename temp2 to temp if you felt so inclined.  

Ken Naim  

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Badauy, Gustavo (Badauy)
Sent: Wednesday, April 05, 2006 3:16 PM
To: oracle-l_at_freelists.org
Subject: Temp Tablespace  

Guys,  

I have a database that has 16GB of allocated data on the temp database even though there is no one accessing it. I had shutdown/started it. How can I reclaim this space? When I try alter database tempfile '.' RESIZE 15000M, it says that there is data beyond that point.  

Thanks  

SELECT s.sid "SID",s.username "User",s.program "Program", u.tablespace "Tablespace",
u.contents "Contents", u.extents "Extents", u.blocks*8/1024 "Used Space in MB", q.sql_text "SQL TEXT",
a.object "Object", k.bytes/1024/1024 "Temp File Size" FROM v$session s, v$sort_usage u, v$access a, dba_temp_files k, v$sql q WHERE s.saddr=u.session_addr

and s.sql_address=q.address
and s.sid=a.sid
and u.tablespace=k.tablespace_name;
 
 

--
http://www.freelists.org/webpage/oracle-l