Re: reg. problem with Temp Tablespace

On Dec 29, 1:13 am, calms <calmsqur..._at_gmail.com> wrote:
> Hi,
>
> Currently im using 17gb for temp tablespace.
> Im received a error stating, "cannot allocate temp segment for temp
> tablespace".
> I increased the temp tablespace with 8gb of space. but again im
> getting the same error.
>
> Can somebody plz suggest, what should be done, or how to determine the
> size of temp tablespace.
>
> fyi, mine is oracle datawarehouse with 1.5 TB of data.
>
> thks

You may want to put together some monitoring and alerting to start warning you at some level whenever a query exceeds usage of a fixed amount of temp tablespace. ( Say 50 meg varies according to your environment ). Later versions of oracle can do this alerting automatically.

Most queries that start using more than hundred meg of temp tablespace are runaway's ( often cartesian joins ) that can be killed. Varies based usage and especially in a data warehouse environment.

I believe that I posted a long time ago in cdos a query and set of procedures that can be used in this regard. Received on Sat Dec 29 2007 - 17:51:40 CST