Re: TEMP tablespace problem

Hi,

in many cases running out of well estimated temp-space is dued to an error in the SQL-statement. If your user's queries involve many tables and he fogets to join them, the result set of this query will be the cartessian product of the queried tables. The cartession product of table a with 10,000 rows and table b with 20,000 rows will return 200,000,000 rows!

RobW95 wrote:
>
> We have been experiencing some problems with our Temp tablespace. It is
> actually happening in two of our instances and it is basically the same
> problem. Everything is find for extended periods of time and then suddenly we
> notice that the temp tablespace is maxed out. It seems like they are big enough
> (100M in one, and 300M in the other). My guess is that the culprit is MS
> ACCESS, which our users utilize to query against the databases. One time when
> the 300M temp tablespace was maxed out I saw in V$session one woman who was
> running 10 Access queries at one time. She would submit one, it would run for
> about 15 minutes, she would get impatient when it didn't return results, and
> then just close Access. My guess is that this does not terminate Oracles
> response to the query request. She did this ten times and suddenly the temp
> tablespace is maxed out.
>
> Has anybody else experienced this problem? The only solution that we have found
> so far is to take the tablespace offline/online. Thanks. Rob
 

-- 
Regards

Matthias Gresz    :-)