temp tablespace in 10g

I have problem with tablespace temp that increases and never decreases. In test environments I created it with AUTOEXTEND ON and run out of disk space few times (temp tablespace get 68 GB large mainly when MV are created! ! !). In test environments i used DROP and CREATE to resize it. This method cannot be used in production environment (cannot drop default temp tablespace)..

In Oracle docs the only thing I found regarding this issue is in Database Concepts 10g Release 2 (10.2)

http://download-uk.oracle.com/docs/cd/B19306_01/server.102/b14220/logical.htm#sthref399

"2 Data Blocks, Extents, and Segments
Extents in Temporary Segments

Multiple sorts, however, can use sort segments in temporary tablespaces designated exclusively for sorts. These sort segments are allocated only once for the instance, and they are not returned after the sort, but remain available for other multiple sorts."

There's no tuning in queries that require many GBs of temp segments. There are very very simple GROUP BY or MINUS queries

Any solution to let Oracle free temp segments to avoid "ORA-01652 unable to extend temp segment.." in production environment ? Received on Mon Sep 11 2006 - 11:01:14 CDT