DROP TEMPORARY TABLESPACE Hangs because of MMOM
Oracle RDBMS 12.1.0.2.4
I create a new temporary tablespace, and make it as the new default.
All users now have this new temporary tablespace as their default.
There is an entry in v$sort_usage that points to a NULL user.
SELECT s.username, sid, serial#, contents, sql_address , extents,last_call_et
FROM v$session s, v$tempseg_usage t
WHERE s.saddr = t.session_addr;
Returns the sid, serial# for mmon.
It takes a bounce of the database for this to let go.
Is there another way to make mmon let go of whatever it's holding onto?
I can replicate this is our staging environment, so I'm going to let it think about it for awhile and see if something changes. MMON manages AWR and such things, so maybe after it takes a new snapshot it will release.