Re: runaway temporary tablespace

The fact it's only 95% full is a problem. It ought to be 100% full, and being re-used. Is it really *temporary* tablespace, not permanent tablespace with a name of "temp"? Check dba_tablespaces to make sure.

Secondly, tablespaces only get bigger if you let them. Got autoextend on? Turn it off... your application will likely go beserk, of course, but at least your disk space won't be eaten away behind your back. Check dba_data_files for the autoextend attribute, and issue an 'alter database datafile 'C:\bing\bong\blah.dbf' autoextend off' if you find any with it set on.

Thirdly, how many Users have you got doing simultaneous sorts? If there's stacks of them, consider creating multiple small TEMPS on separate disks, and changing Users' temporary tablespace so different people point to different tablespaces for their sorts. If they're all bundling into the one TEMP tablespace, expect it to grow (autoextend permitting, which is a bad idea) to accomodate the strain.

Fourth, what's your sort_area_size in init.ora? If you increase it, more sorts will take place in memory, and not be swapped down to disk in the first place. My rule of thumb is that sort_area_size should be at least 1Mb, and if you don't reckon you can afford that because you've too many Users, the its time you considered multi-threaded server.

Some things to start with, anyway....
Regards
HJR WCK <abramswee_at_yahoo.com> wrote in message news:950bj8$801$1_at_dahlia.singnet.com.sg...
> 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 Mon Jan 29 2001 - 06:04:31 CST