I have a massive TEMP tablespace that is 1TB large. It consists of 30datafiles, each one is like 31GB in size.

Entire TEMP tablespace has a size of 1TB, while used size is only ~600MB.

I want to shrink tablespace and I am dilema whether to use option

a) to shrink each datafile individually:

    ALTER TABLESPACE temp SHRINK TEMPFILE '/u02/oracle/data/lmtemp01.dbf' KEEP 10MB
    ALTER TABLESPACE temp SHRINK TEMPFILE '/u02/oracle/data/lmtemp02.dbf' KEEP 10MB
    ALTER TABLESPACE temp SHRINK TEMPFILE '/u02/oracle/data/lmtemp03.dbf' KEEP 10MB



b) to shrink entire tablespace:


c) shrink without KEEP (so it gets shrunk to minimum)


I am on Oracle Enterprise 12.2.

I am wondering:

  1. Which option is the 'best' ?
  2. Why wouldn't one just always use option C ?
  3. Why would one want to KEEP anything in TEMP temporary tablespace at all at shrinking (except that bare system required minimum)?

  4. Can any harm be done with shrinking TEMP tablespace? I will run it on Productional database (out of working hours tho when noone is connected to the DB) and I have to be sure.

I am running on Oracle Enterprise 12.2.

  • 2
    Temp spaces expand when the system needs space for interim work. Why do you think that the normal work load will not cause the temporary space to expand after shrinking again? – kevinskio Dec 9 '19 at 14:39
  • Because there is a possibility that the next work load will not cause as large tablespace expansion as it's current size. Also, my customer uses Jenkins automation tool for their work load - I was thinking about adding script for shrinking TEMP tablespace after their each last work load job. My customer has 20 databases and by shrinking TEMP tablespace at least for temporary (after each work load) I will save some storage, in theory of course : ) – korodani Dec 9 '19 at 16:24
  • Storage is (relatively) cheap compared to downtime. Until you know what work load is causing the growth and the likelihood of it's reoccurence you are taking on risk for no proven gain. Try dba_extents where tablespace_name = 'TEMP' to gather more information about the growth of the temp space – kevinskio Dec 9 '19 at 17:33
  • May I ask you what risk would I be taking by shrinking TEMP ? Isn't shrinking TEMP harmless if it's done out of the working hours (with no active sessions) ?Well If had ASH - active session history, It would be easy to find out what's filling TEMP, but I don't have ASH unfortunately. – korodani Dec 10 '19 at 14:15
  • 1
    My comment is based on working with production systems on an enterprise level. If the database is for development or not used for anything important then it might not apply. For any change to a production database, particularly one which could terminate an in process transaction, the first question is "Why?". The idea that shrinking the temp space will not have any side effects depends on a number of assumptions: assumes no work will be done while you are shrinking the temp files, assumes that downtime has no cost, assumes that the script will always run correctly. – kevinskio Dec 10 '19 at 16:54

all the three options would work, but would prefer to use first or the second one in production environment to avoid queries failing post activity due to not enough temp tablespace. Would recommend identifying the usage history pattern using queries similar to the one defined in this blog and then use that for your keep size. https://zukus.blogspot.com/2016/06/temp-tablespace-usage-history.html

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.