TEMPORARY tablespace problem -- newbie problems

Hi. I am new to Oracle and am having a problem with the Temporary tablespace. It seems that Oracle is never reusing any of the space it allocates for a process. The reason I say this is that we keep running out of space on the tablespace (if we allow unlimited extents, it fills the disk). Some of the queries we run involve long, complicated joins on millions of records, which should (and does) allocate a large temporary space.

Anyway, the users keep getting locked up due to the lack of temporary space. Is there some setting I have to use to tell oracle to free up the space when it finishes the query? I have tried using the "alter tablespace temporary coalesce" command and it does not seem to help.

My assumption was that the temporary tablespace was, in fact, temporary and that when a query or process completed it would release any objects that Oracle created, thereby freeing the space for use by another process. Is this wrong?

Any help would be appreciated, as I am being hammered by a large client on this.

Thanks.

Rob Received on Tue Jun 13 2000 - 00:00:00 CDT