-
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?
-
err temporary segments are not freed because after the sorting operation they can be overwritten, it´s Oracle´s normal behavior. You dont need to be worried
FULL TABLE SCAN doesnt cause sorts so your temporary tablespace growth is not caused by that, SQL that cause sorting are group by, distinct, order by (and 4 or 5 more but I cant remembah but look for these 3)
-
in fact there is a strange behaviour of Oracle which does not release temp segments when needed and instead of that tells you that you cannot extend TEMP etc ... while it should just free space :/
well nevertheless, my post is to give you a workaround to free TEMP without shutting down your instance, just follow these 3 steps :
alter tablespace TEMP default storage (pctincrease 1);
alter tablespace TEMP default storage (pctincrease 0);
alter tablespace TEMP coalesce;
-
In a DW system, multiple temporary TSs on multiple disks under multiple disk controllers help in performance.
If you have free disk space, create one more temporary tablespace with pctincrease 0, initial and next of same size ( = multiples of sort_area_size) and assign this TS to specific users who query from a large table. To improve performance you can place the data file for the TEMP TS in a non-mirrored disk.
Or you can try locally managed temp tablespace with uniform extent of size 50M.
The last option is: Look into the SQL statement whether it uses proper indexes or not.
