Change TEMP tablespace to Temporary instead of Permanent.

Hi Friends,

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.

Thanks

Suresh

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;

Hi Pipo,

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.

Please explain.

Many Thanks
Suresh

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 !!
Much Appreciated..