Shrink TEMP requires getting MMON to release active TEMP segment
Is there a standard procedure for temporarily turning off AWR collection so I can get MMON to release his TEMP segment so that I can successfully SHRINK TEMP TABLESPACE?
Recently, I had a scenario on Oracle 12.2.0.1 where a bigfile TEMP tablespace has been grown to 300GB, and the underlying SQL "Cartesian Product" tuning issue has been resolved and we are ready to shrink it back.
First we can check and verify little to nothing is using TEMP currently, just Oracle’s internal monitoring processes DBSNMP and MMON.
So we try shrink but it fails, we are stuck at 300GB…
SQL> alter tablespace TEMP shrink tempfile '/oradata01/SAMP/temp01.dbf' keep 500M;