Temporary tablespace concerns...

Hi All,
I was wondering why my temporary tablespace is growing so fast. Is there anything I can do about it? I think our application is opening a lot of cursors and that's causing the problem. Is there a way to free up the space on the temp tablespace? At the moment the size has grown to about 300 Megs.

Thanks,
Dee.

well turn the autoextend off then

cursor does not cause temp tablespace to grow, it's your SQL statements that cause them with ORDER BY, GROUP BY or MERGE JOINs

Thanks Pando, but what will be the impact of turning off autoextend? Will it affect performance in anyway?

Dee.

If you turn autoextend off, the users who are using all your TEMP space will start complaining because their queries fail.

but then again considering 300mb for a temp tablespace is not much anyways

and you have not said what kind of database is it, oltp or dwh

That's what I thought. I can't have our developers breath down my neck. By the way, I am not running an OLTP, it more or less a DWH. I am not used to seeing that much growth on the TEMP tablespace, but they are developing a GUI tool that I think does a lot of order by and sorting. Is there anything I should do or should be aware of at this point?

Thanks,
Dee

i think u can consider creating some indexes based on the explain plan of a particular query .

if it's DWH then 300MB is nothing

in our DWH we have like 15GB temp tablespaces

Thanks guys, now I can sleep in peace.

Dee.