Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Temp Tablespace

Temp Tablespace

From: Badauy, Gustavo (Badauy) <badauy_at_avaya.com>
Date: Wed, 5 Apr 2006 17:16:10 -0300
Message-ID: <446ADA05B0EF8E44BF02F7C7AD5235E8010F0272@bz0814avexu1.global.avaya.com>


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
Received on Wed Apr 05 2006 - 15:16:10 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US