Today in this article I will show you how to re-create TEMP tablespace which is being used/hold by the MMON background process.
I’ve been requested to had a look at a database which was storing the tablespaces/datafiles in the file system and the TEMP tablespace was consuming about 280GB. This file system reached the threshold of 87% (used) and the tablespace was only using about 62GB, against the 280GB allocated to it.
So, in order to reclaim 218GB which is not used, I created a new temporary tablespace (TEMP2) and assigned it to the database as a default temporary tablespace:
create temporary tablespace TEMP2 tempfile '/oradata/mydbname/tempfile/temp2_01.dbf' size 5G autoextend on;
alter database default temporary tablespace TEMP2;
Before drop the old tablespace TEMP, we must check if still any active process/session using this temporary tablespace:
SELECT DISTINCT SU.TABLESPACE ,SU.BLOCKS ,S.SID ,S.SERIAL# ,S.USERNAME ,S.OSUSER ,S.STATUS FROM V$SESSION S, V$SORT_USAGE SU WHERE S.SADDR = SU.SESSION_ADDR AND SU.TABLESPACE = 'TEMP' AND S.STATUS = 'ACTIVE';
After some time (2 days), most of the sessions started to use the new temporary tablespace TEMP2, however still a background process (MMON) active using the TEMP tablespace:
In order to release the MMON resources from this tablespace without downtime/bounce/restart the database instance:
alter system enable restricted session;
alter system disable restricted session;
After enable/disable the restricted session the MMON process instantly switched to the new temporary tablespace TEMP2, releasing the resources from TEMP.
Now, we can proceed to drop the temporary tablespace TEMP and re-create it with a reduced size:
drop tablespace TEMP including contents and datafiles;
create temporary tablespace TEMP tempfile '/oradata/mydbname/tempfile/temp01.dbf' size 5G autoextend on;
alter database default temporary tablespace TEMP;
I validated that the TEMP2 was not being used and then I dropped it:
drop tablespace TEMP2 including contents and datafiles;
With this action, I managed to reclaim at the first moment about 218GB.
I came up with this solution while searching at MOS (My Oracle Support), for a solution to restart the MMON process without downtime/restart/bounce the database instance.
The document which helped me to find this solution is the one below:
AWR Snapshots Are Not Being Created Because MMON Is Not Being Respawned (Doc ID 2023652.1)
I hope this helps anyone who find this blog post.