DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: Can I decrease the size of the TEMP tablespace online?

  1. #1
    Join Date
    Jun 2005
    Posts
    17

    Can I decrease the size of the TEMP tablespace online?

    Is it ok to decrease the size of TEMP tablespace online? i have 1 datafile in the TEMP tablespace and the size is 34GB. I want to resize it to lower size lets say 5GB or 10GB, can i do this online without shutting down the DB?

  2. #2
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    sure, assuming your are using a temporary tablespace and not a permanent tablespace that is being used for temporary objects.
    Jeff Hunter

  3. #3
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    wait. that little tom kyte devil on my shoulder tells me you need more of an answer than just "yes"...

    First, if this expansion is due to normal activity, then you can shrink the tablespace but it will grow right back to it's current size.

    If this is due to abnormal activity, then you can assign all the users that use that TS to a new temp ts, drop the tempfile with "alter database tempfile 'xyz.dbf' drop;", rm it from the OS, recreate it the size you want with "alter tablespace temp add tempfile 'xyz.dbf' size 1m autoextend on next 1m maxsize 4096M;", and re-assign the users back.
    Jeff Hunter

  4. #4
    Join Date
    Jun 2005
    Posts
    17
    Thank you marist89 for the quick response... i'll try this now

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