Re: TEMP tablespace problem

Yes, but is there a way to dump all of the Temp data easily if it is not being used? Can an algorithm (daemon) be written to check for temp data that is no longer being used and then flush it? If so, how would you go about doing it? What are the commands to repair/compact the temp space? Thanks. Brendan

Matthias Gresz wrote:

> 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 :-)
Received on Wed Jan 28 1998 - 00:00:00 CST