I can see that of all the 1200 gigs allocated to this tablespace, 0% are free. But even after logging out of that session and relogging, the TEMP tablespace remains at the same capacity. So, do i now need to bounce the database so as to free it?Temporary tablespace extents do not get deleted when they are no longer in use, they get re-used. You see the tablespace as 100% full, but as long as the sort usage shows that nothing is being used then the allocated extents will be available.
Also, when i check the v$sort_segment and v$sort_usage views, i find both of them empty. Then why is not the space getting deallocated. Am in following the wrong trail? Please advice.
CrazyAnie wrote:You said before that the temp tablespace was 1200 gb. Which is it?
The DB is Oracle 10g R2 and the OS is Solaris 10. I tried increasing the TEMP tablespace from 1 gig to 1.5 gigs. But still I am getting the same error. The TEMP tablespace again increases to 100%.
CrazyAnie wrote:So when does this error occur if the query completes?
The query is a simple one and does not take more than a few secs to retrieve the results in Oracle.
But my concern area is that if the V$TEMPSEG_USAGE is empty, why is the TEMP tablespace at 100% capacity.What query or tool tells you that it is at 100% capacity?