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;
Can you please explain what the first three statements did.
I know that pctincrease is the percentage to incease the next extent size with.
I also know coalesce defragments and groups data segemnts together.
I don't understand how it cleared up all of my temp tablespace though.
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
Thanks for your help Pipo !!
Click Here to Expand Forum to Full Width