DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Thread: ORA-1652: unable to extend temp segment by 1280 in tablespace TEMP

  1. #1
    Join Date
    Jun 2001
    Posts
    243
    Hi, I'm getting unable to extent tablespace error. My temp size is 1200M and it's full. What should I do before increase the Temp tablespace size?...can I coalese temp tablespace?...is increasing the Temp tablespace right way to solve this problem?
    Thanks

  2. #2
    Join Date
    Feb 2001
    Location
    Paris, France
    Posts
    809
    do :
    alter tablespace TEMP default storage (pctincrease 1);
    alter tablespace TEMP default storage (pctincrease 0);
    alter tablespace TEMP coalesce;

    this will free unused space

  3. #3
    Join Date
    Jun 2001
    Posts
    243
    why can't I just do 'alter tablespace TEMP coalesce' ?..do I have to do the first two statements that you suggest pipo?

  4. #4
    Join Date
    Feb 2001
    Location
    Paris, France
    Posts
    809
    because temporary segments are not freed, but just marked as unused when they are not used (if you coalesce, you won't change much things), so by changing pctincrease, you force SMON to scan the whole tablespace, and unused segments are then freed, then you change back to pctincrease=0, and you can coalesce since you now have plenty of free segments everywhere

  5. #5
    Join Date
    Jun 2001
    Posts
    243
    is this applies to other tablespaces as well other than TEMP tablespaces?

  6. #6
    Join Date
    Feb 2001
    Location
    Paris, France
    Posts
    809
    nope, free segments in other tablespaces really are freed. this behaviour is due to the fact that Oracle manages temp segments, and uses unused segments when needed, while in other tablespaces segment management is quite basic : if you drop an object, all of its extents are freed, if not then extents are used
    so you can directly coalesce a standard tablespace, no need to use this SMON workaround

  7. #7
    Join Date
    Jun 2001
    Posts
    243
    Thank you pipo....
    I tried the first statemnet and I got the following error...

    SQL> alter tablespace TEMP default storage (pctincrease 1);
    alter tablespace TEMP default storage (pctincrease 1)
    *
    ERROR at line 1:
    ORA-03217: invalid option for alter of TEMPORARY TABLESPACE

    is this syntax right?....now what it I do wrong here?...

  8. #8
    Join Date
    Feb 2001
    Location
    Paris, France
    Posts
    809
    is your tablespace locally managed ???

    select extent_management
    from dba_tablespaces
    where tablespace_name = 'TEMP'


  9. #9
    Join Date
    Jun 2001
    Posts
    243
    Yes, my TEMP tablespace is locally managed.....

  10. #10
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    you have to increase datafile size, you cannot coalesce LMT tablespace (there is no point anyway)

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