I need to alter my temp tablespace so that it is temporary instead of permanent.
At the moment I cannot alter the tablespace to temporary becasue 11Mb is being used up.
I want to drop the objects currently inside temp tablepspace but don't know where to find them.
I queried dba_segments but it tells me that there is nothing in TEMP Tablespace. Is this becasue some PERMANENT Objetcs are already in there ?
Please tell me how to drop current objects in Temp Tablespace so that I can make the tablespace Temporary.
Also.... Does anybody know the reason or scenarios for defining the Temp tablespace as permanent. I know that a permanent tablespace leaves objects in there until they are dropped but I don't know why you would ever need to to define a temp tablespace as permanent, especially as it is only used to sort data when sort_area in PGA is full - I would expect it to always be dynamic dropping and creating objects whenever necessary.
you should always use TEMP tablespace as a temporary tablespace, so you are sure noone can create an object and thus decrease the available space for everybody.
I guess you have a SYS segment in your TEMP tablespace, which is a temporary segment.
you should do this (first 3 commands to clean, last to make temporary):
alter tablespace TEMP default storage (pctincrease 1);
alter tablespace TEMP default storage (pctincrease 0);
alter tablespace TEMP coalesce;
alter tablespace TEMP temporary;
well, Oracle 8 sometimes has a problem in cleaning TEMP tablespace. It's noraml behaviour would be to fill the TEMP tablespace, and then clean if space is needed. Nevertheless sometimes it doesn't, and the first 2 alter commands I gave you (changing pctincrease to 1 then to 0) is a good workaround for forcing SMON to look at the TEMP tablespace, thus releasing occupied segments.
You just coalesce then, and your TEMP tablespace is clean