Re: runaway temporary tablespace

From: Mark Bole <makbo_at_ecis.com>
Date: Sun, 28 Jan 2001 10:07:56 -0800
Message-ID: <3A745FFC.7E5F7992@ecis.com>

I have seen similar behavior when poorly formed SQL queries create "Cartesian product" result sets due to a missing "join" condition, and then the programmer works around it by doing a "SELECT DISTINCT...". This will cause all the rows to be sorted and merged in the TEMP tablespace.

Check "EXPLAIN PLAN" output to see how many rows are actually being processed at each step of the query execution...

WCK wrote:
>
> my oracle 8.0.6 is on NT 4.0 with SP6. My new database starts with a 100mb
> temporary tablespace and a 3gb data tablespace but now the temporary
> tablespace has increased to 2.5gb in size. any idea what cause it to expand
> so drastically and how can i go about to maintain it? it shows to have over
> 95% in use.
> Thanks.
> WCK
Received on Sun Jan 28 2001 - 12:07:56 CST