Re: Temporary tablespace fills up

The temp (if tablespace is TEMPORARY) space is not released until the database is restarted (even if sorts have completed), when SMON will reclaim the space. To overcome this, manually wakeup the SMON. You can try this from server manager (I think Johnathan Lewis has it on his web site):

alter tablespace temp default storage (pctincrease 0);

oradebug wakeup pid;

where pid is the process id of SMON (look at the startup messages in your alert log to find out the actual number)

Now look at the sum of free space from dba_free_space for your temp tablespace. Voila. I have used this without any problems and it always works for me.

Murali Sreedhar

"traceable1" <traceable1_at_hotmail.com> wrote in message news:8551d8c9.0107170705.d3b727b_at_posting.google.com...
> We were having problems in 8.1.7 (or any 8i) with the TEMPORARY
> tablespace filling up. It didn't matter how big it was - the query
> would run until the TEMP space filled up. v$sort_segment would report
> that the space was free, but it would not allocate it. The process
> would run okay after restarting the database - I think the SGA needed
> to clear out.
> Anyway, the problem was solved by getting rid of the Parallel Query.
> At the beginning of the query, I set each table involved:
> ALTER TABLE [table_x] PARALLEL (DEGREE 1 INSTANCES 1);
Received on Tue Jul 17 2001 - 17:26:59 CDT