RE: Temp Tablespace

From: Baumgartel, Paul <paul.baumgartel_at_credit-suisse.com>
Date: Wed, 5 Apr 2006 16:22:34 -0400
Message-ID: <D97D1FAE0521BD44820B920EDAB3BBAC0BF93F99@ENYC11P32005.corpny.csfb.com>

Once temporary segments are allocated, they stay allocated. The only way to reclaim the space is to create a new temp tablespace, make it the default, then drop the old one.  

Paul Baumgartel
paul.baumgartel_at_credit-suisse.com
212.538.1143

-----Original Message-----
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 4: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;    

---

Please access the attached hyperlink for an important electronic communications disclaimer:

http://www.credit-suisse.com/legal/en/disclaimer_email_ib.html

---

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

Received on Wed Apr 05 2006 - 15:22:34 CDT