Re: Temp Tablespace Full

"aime badrane" <badrane.aime_at_neuf.fr> wrote in message news:dpib7o$3jn$1_at_aphrodite.grec.isp.9tel.net...
> (bonjour Michael, excuse my english)
> Normally, the tablespace temp must be empty, after a select,...however i
> have the save problem and i did:
> alter tablespace temp offline;
> alter tablespace temp online;
> to clean it;
>

No. Space is not deallocated but can be re-used by other sessions. If you want to know what really is used up by sessions, and you use tempfiles (not temporary datafiles) do:

set pagesize 0
column temptotal noprint new_value temptotal select sum((BYTES_USED + BYTES_FREE)/1024/1024) temptotal from v$temp_space_header;

column tempused noprint new_value tempused select nvl(sum(blocks * value /1024/1024),0) tempused from v$tempseg_usage, v$parameter
where name='db_block_size';

set pagesize 60 heading on

column tot format 99999.99 heading "Total space for Temp segments (MB)" column used format 99999.99 heading "Used space (MB)" column avail format 99999.99 heading "Space available (MB)" select &&temptotal+0 tot, &&tempused+0 used, &&temptotal+0 - &&tempused+0 avail
from dual;

If you don't have tempfiles it will return zeroes

Syltrem

> Aimé
>
> "Michael42" <melliott42_at_yahoo.com> a écrit dans le message de news:
> 1135527630.056281.102700_at_g49g2000cwa.googlegroups.com...
>> Hello,
>>
>> In Oracle 9i I ran an operation that gathered statistics. It ran for
>> several hours and I ended up having to break out of it. The result is
>> that my TEMP tablespace is full (OEM shows it as full).
>> Other than bouncing the database how can I have the TEMP tablespace
>> clean itself up?
>>
>> Thanks,
>>
>> Michael42
>>
>
>
Received on Fri Jan 06 2006 - 13:27:48 CST