topic Re: temp tablespace in Operating System - HP-UX

temp tablespace

Brian_274 — Tue, 30 Mar 2004 14:10:18 GMT

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.

Re: temp tablespace

Nicolas Dumeige — Tue, 30 Mar 2004 15:50:08 GMT

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

Re: temp tablespace

Brian_274 — Tue, 30 Mar 2004 15:56:00 GMT

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.

Re: temp tablespace

Nicolas Dumeige — Tue, 30 Mar 2004 17:15:58 GMT

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 ...)

Re: temp tablespace

Printaporn_1 — Wed, 31 Mar 2004 01:09:53 GMT

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

HTH.

Re: temp tablespace

twang — Wed, 31 Mar 2004 03:22:57 GMT

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

Re: temp tablespace

Brian_274 — Wed, 31 Mar 2004 09:11:28 GMT

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