Shrinking Oracle TEMP tablespace

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:

oracle_drop_temp_0

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

oracle_drop_temp_1

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

oracle_drop_temp_2

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.

One comment on “Shrinking Oracle TEMP tablespace

  1. Sammy

    Awesome man, this is detailed stuff! Thanks.

Leave a reply