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.
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?