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:
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:
- Which option is the 'best' ?
- Why wouldn't one just always use option C ?
Why would one want to KEEP anything in TEMP temporary tablespace at all at shrinking (except that bare system required minimum)?
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.