temp tablespace

[Brian_274]

Hello all,
I'm trying to determine what is using temp tablespace. It currently is filled up with no active sessions. The tablespace is in 8i and dictionary managed. Is there anyway I can determine what is using the temp tablespace? I looked in dba_segments and it appears that SYS owns an segment called '4.54754' that exists in the temp tablespace. This doesn't really help me determine what this object is. I don't want to bounce the server to clean it up.

thanks.

[Nicolas Dumeige]

Brian

Unless Oracle give you a 'Unable to extend ... in tablespace TEMP', you shouldn't worry about sys segments in the TEMP tablespace ; Oracle deal with thoses.
And even in this case, you would do sql tuning or increase TEMP size but never issue some drop sys.segement.

A trick that worded on 8.1.7.0.4, just for the peace of mind is to modify the storage paramater twice (given that pctincrease value is 0) :
alter tablespace TEMP default storage (pctincrease 1);
alter tablespace TEMP default storage (pctincrease 0);

But that's just a game with no gain.

To be sure, look on http://metalink.oracle.com

[Brian_274]

thanks actually I was getting unable to extend. The session was terminated and I wanted to try to free up the temp space. I know oracle has some background process that is supposed to clean it up. Or when the next process comes to use it should free it up, but it wasn't. I do realize I need to increase the size of the temp tablespace and the developer needs to tune his code and add more indexes. I was just hoping to free up space without having to bounce the DB.

[Nicolas Dumeige]

Actually, building an index use TEMP space as it involves sorts ;-D
You may want to check the sort on memory/disk ratio and tune SORT_AREA_SIZE.
If you want to check the SQL statement that produce the TEMP exhaustion use the set autotrace on or explain plan for ... to see some valuable info (block access, execution plan ...)

[Printaporn_1 · Accepted solution]

Hi,
For temp storage segment it will not clear out even no session use it until restart instance.

HTH.

[twang]

Hi,
You may use this sql to check who is currently using temp space:
select
b.tablespace
,b.segfile#
,b.segblk#
,round(((b.blocks*p.value)/1024/1024),2) size_mb
,a.sid
,a.serial#
,a.username
,a.osuser
,a.program
,a.status
from v$session a
,v$sort_usage b
,v$process c
,v$parameter p
where p.name='db_block_size'
and a.saddr = b.session_addr
and a.paddr=c.addr
order by
b.tablespace
,b.segfile#
,b.segblk#
,b.blocks

regards
twang

[Brian_274]

Thanks twang that's what I needed. Darn I was hoping there was a way to flush the temp tablespace.