Re: TEMP tablespace grows all the time!

If I had to guess at the cause of the problem, it would be that you do not have uniform extent sizes, and hence you are fragmenting this tablespace into many small chunks. Each time it needs to grab a decent sized chunk it has to grow the tablespace. If this is the problem, then using a uniform extent size should cure this.

As to how to go about reducing it. That would probably depend on your circumstances. If you have only a few users, you could simply create a new Temporary tablespace, and assign this as their TEMP tablespace. I'm not sure when the assignment will happen. It probably won't affect connected session, and hence man not be until they next login. But at least in a few hours/days you should be able to drop (and if necessary) recreate your TEMP tablespace.

"SGA" <zeevikm_at_hotmail.com> wrote in message news:b0fb5246.0203310632.3ed23a3a_at_posting.google.com...
> Hello All.
>
> RDBMS: 8.1.7.3
> O/S : Sun Solaris 8
>
>
> Localy Managed Temporary tablespace (with unlimited autoextend enabled
> on datafiles) is 40G already - and keeps growing.
> Tablespace is locally managed, and the DB is 24x7 environment,
> so shutdown is not an option to reduce the tablespace size.
>
> I know such a problem exists since early 8i versions (at least 8.1.6 and
up),
> but nobody seems to know the answer ...
>
> Please Advice.
> Regards.
Received on Sun Mar 31 2002 - 13:08:33 CST