There might be times that you need to shrink the TEMP tablespace because you are running out of disk space. Now you can check and query to see what’s the name of the tablespace(s) and their names so you can shrink them. Run the following to see the list of all the temp tablespces:
SELECT tablespace_name, file_name, bytes FROM dba_temp_files WHERE tablespace_name like 'TEMP%';
And you’ll get something like this:
The first question you have to ask is why the temporary tablespace is so large. It may be due to a large query that you just ran with a sort that was a mistake or it may be due to some other exceptional circumstance. If that is the case then all you need to do to clean up is to shrink the temporary tablespace. So first try running the following line:
ALTER DATABASE TEMPFILE '/FULL_PATH_HERE/temp01.dbf' RESIZE 256M;
If it works then you are done, but chances are that you will get the following error:
ERROR at line 1: ORA-03297: file contains used data beyond requested RESIZE value
This error may have occurred since Oracle will only shrink the file if the temporary tablespace is at the head of the file and if it is smaller than the size you specify. If the temporary tablespace you want to shrink is your default temporary tablespace, you will have to first create a new temporary tablespace, set it as the default temporary tablespace then drop your old default temporary tablespace and recreate it. Afterwords drop the second temporary table created. So let’s do this:
Create the new tablespace:
CREATE TEMPORARY TABLESPACE TEMP2 TEMPFILE '/FULL_PATH_HERE/temp02.dbs' SIZE 5M REUSE AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M; Tablespace created.
Set the new tablespace as the default so you can drop the original one:
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP2; Database altered.
Now, try dropping the first tablespace. Yes I said “Try” because it might hang and I’ll tell you why later:
DROP TABLESPACE TEMP INCLUDING CONTENTS AND DATAFILES;
If the above statement hangs for more than a couple of seconds, then there might be an OS process that has locked the TEMP tablespace and isn’t allowing Oracle to drop it. So, let’s find out if that’s true and kill the process if possible:
SELECT username ,session_num ,session_addr ,tablespace FROM v$sort_usage WHERE tablespace='TEMP'; --put the tablespace's name here
So now that we know there is process locking the tablespace, run the following line to generate the kill statement to kill the annoying session. You need to get the saddr from the last step:
SELECT 'ALTER SYSTEM KILL SESSION ''' || sid ||','|| serial# || ''';' FROM v$session WHERE saddr='C00000009E6F6118'; --This is the session_addr from last step
Copy the above line and run it and you should be able to ALTER SYSTEM which kills the process, now we can continue dropping the tablespace. Try again:
DROP TABLESPACE TEMP INCLUDING CONTENTS AND DATAFILES; Tablespace dropped.
Now, you can recreate the original tablespace:
CREATE TEMPORARY TABLESPACE TEMP TEMPFILE '/FULL_PATH_HERE/temp01.dbs' SIZE 5M REUSE AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;
Mark it as default, and then drop the second tablespace:
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP; Database altered. DROP TABLESPACE TEMP2 INCLUDING CONTENTS AND DATAFILES; Tablespace dropped.
It should have worked this time… Now you have recreated your TEMP tablespace and saved some diskspace.