I have a problem with my TEMP tablespace which is constantly filling up. This can happen as early as 30minutes after starting the database.
TEMP is the default temporary tablespace for all users and is 1.5gb in size.
I have checked that no users have been allocated TEMP as their default tablespace acidentally.
I am pretty sure the problem is being caused by certain reports which are run by some users. Some of the reports need to access some very large tables (up to 12 million rows in one case).
My questions are these.
1. Could the problem be badly written sql used by these reports (full table scans etc.)?
2. When these reports finish, the space in TEMP is not released. The only way that I can clear it is by shutting down and re-starting the database, and then taking about 20calls complaining that the system is down!. Is there any way to force the release of space on completion of said reports?