The temporary tablespace on one of our databases keeps growing. I drop and recreate the tablespace to get it to a smaller size and then it grows very large. Is there anything that I can run/look at to see what is causing this explosive growth? (from 55 meg at creation to 1.8 gig now - two weeks later). I understand that sorts and queries with distinct, etc use the temp space, but I would like more exacting detail.
TEMP will use what TEMP will use. In other words, if the load on your database is such that TEMP keeps growing to 2G, then you probably need 2G of TEMP space. TEMP will be re-used when necessary, but you may actually be slowing things down by constantly resizing TEMP.