Change TEMP tablespace to Temporary instead of Permanent.
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: Change TEMP tablespace to Temporary instead of Permanent.

  1. #1
    Join Date
    Apr 2001
    Location
    London
    Posts
    725
    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

  2. #2
    Join Date
    Feb 2001
    Location
    Paris, France
    Posts
    809
    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;

  3. #3
    Join Date
    Apr 2001
    Location
    London
    Posts
    725
    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


  4. #4
    Join Date
    Feb 2001
    Location
    Paris, France
    Posts
    809
    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

  5. #5
    Join Date
    Apr 2001
    Location
    London
    Posts
    725

    Talking 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
  •  



Click Here to Expand Forum to Full Width