Database Administration (MOSC)

MOSC Banner

Shrink TEMP requires getting MMON to release active TEMP segment

edited May 27, 2020 5:11AM in Database Administration (MOSC) 4 commentsAnswered ✓

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;

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center