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?
Printable View
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?
sure, assuming your are using a temporary tablespace and not a permanent tablespace that is being used for temporary objects.
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.
Thank you marist89 for the quick response... i'll try this now :)