-
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
Thanks for your help Pipo !!
Much Appreciated..
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|