Can shrinking temporary TEMP tablespace in Oracle cause any harm

oracleoracle-11g-r2oracle-12ctablespaces

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

…

or

b) to shrink entire tablespace:

    ALTER TABLESPACE TEMP SHRINK SPACE KEEP 300MB;

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

   ALTER TABLESPACE TEMP SHRINK SPACE;

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.

Best Answer

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

Related Question