This content has been marked as final. Show 1 reply
I think using Google would have given some answers:
The fact that oracle complains about being unable to add TEMP segments often makes the DBA start searching into extending the TEMP tablespace.
But it is smart to read until the end of the line. The problem then gets more clear.
It is obvious to the statement that there is a lack of TEMP segments in the DATA tablespace. So not in your TEMP tablespace.
This is also confirmed by the fact that your problem got solved by adding space to the DATA tablespace.
Maybe you should consider to start using autoextend features.
Regarding the second question:
The database of the dropped temp tablespace might be still in use while e.g. SMON is cleaning up segments.
After a while - but for sure if you bounce the db - the space will be released
My Blog: http://managingoracle.blogspot.com