Urgent : TEMP tablespace
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: Urgent : TEMP tablespace

  1. #1
    Join Date
    Nov 2000
    Posts
    416

    Question

    Our TEMP tablespace in production database is %99.8 full. I did tablespace coalesce but it didn't free up anything. Why ?? Do I need to add a datafile?


  2. #2
    Join Date
    Feb 2001
    Location
    Paris, France
    Posts
    809
    try (assuming default pctincreaseis of 0) :
    alter tablespace temp default storage (pctincrease 1);
    then
    alter tablespace temp default storage (pctincrease 0);
    and
    alter tablespace temp coalesce;

    it should free your space ...

  3. #3
    Join Date
    Nov 2000
    Posts
    416
    Yes you are right Pipo, It worked. Thansk. But why it is like that ??


  4. #4
    Join Date
    Feb 2001
    Location
    Paris, France
    Posts
    809
    well that is a good question ... SMON [should] clean the TEMP tablespace whenever needed, but sometimes it doesn't ...
    note that the behaviour of the temporary tablespace has changed in Oracle 8 : Oracle now creates a segment, which keeps on growing until it totally fills the tablespace, and then needed space should be reused, nevertheless I have been working on a lot of HP boxes, and some of these had the problem you have encoutered ... what I suggested is just a workaround

  5. #5
    Join Date
    Apr 2000
    Location
    Edison, NJ
    Posts
    759
    Interesting solution. Another way is to bounce the db.

  6. #6
    Join Date
    Apr 2000
    Location
    Malmoe, Sweden
    Posts
    23
    Well actually it depends on if the temp tbs is created as temporary or permanent. If temporary it never deallocates temp segments(unless you restart the database), if permanent SMON cleans temp segments. Temporary is to prefer because the database doesn´t have the overhead of deallocating extents all the time. The worst situation i have seen is a 30 GB temp tbs created as permanent and with the next_extent size set to default 30(?) Kb. It took oracle 4 days to deallocate the extents and under this time no temporay segments could be allocated EVEN when we created a new temp tbs!!!

    /Tuve

  7. #7
    Join Date
    Apr 2000
    Location
    Edison, NJ
    Posts
    759
    I've had a a temp ts as permanent (not temporary) and I still had to bounce the db to clean out the temp ts. How strange.

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