Hello.
We have a database running with Oracle 10.2.0.4 on Solaris 5.10 [SUN SPARC v240] where the assigned TEMP tablespace size looks to be quite huge ( = 8GB!). There are not much SQL queries being run on the database, but what can I do/find/investigate to be sure of how much TEMP space is 'actually' required for the application to run OK?

Note. I have seen the previous DBA's have marked AUTOEXTEND = ON for the TEMP datafiles as well.

Related queries:
If I would like to know the max utilisation of TEMP tablespace and the specific query/user which is doing so, what can I do?

Also, can someone advice if keeping AUTOEXTEND = ON for a non-OTLP database like this one is a good idea?


Thanks
SB.