How to Shrink Temp Tablespace and Reclaim Unused Space in Oracle

Over a period of time temporary tablespace grow in size and we must resize tempfile to overcome space issue. In this article, we will discuss about temporary tablespace usage and shrinking tempfiles to reclaim unused space from the TEMP tablespace in Oracle.

Temporary tablespaces are used for database sorting and joining operations and for storing global temporary tables. It may grow in size over a period of time and thus either we need to recreate temporary tablespace or shrink it to release the unused space.

ORA-1652: unable to extend temp segment by 128 in tablespace TEMP

There is Temporary Tablespace Enhancements in Oracle 11g and a new view has been introduced called DBA_TEMP_FREE_SPACE that displays temporary tablespace usage like TABLESPACE_NAME, TABLESPACE_SIZE, ALLOCATED_SPACE and FREE_SPACE.

Query to Check TEMP Tablespace Usage in Oracle:

 SELECT   A.tablespace_name tablespace, D.mb_total,
         SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_used,
         D.mb_total - SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_free
FROM     v$sort_segment A,
         (
         SELECT   B.name, C.block_size, SUM (C.bytes) / 1024 / 1024 mb_total
         FROM     v$tablespace B, v$tempfile C
         WHERE    B.ts#= C.ts#
         GROUP BY B.name, C.block_size
         ) D
WHERE    A.tablespace_name = D.name
GROUP by A.tablespace_name, D.mb_total;


TABLESPACE     MB_TOTAL  MB_USED  MB_FREE
-------------- -------- -------- --------
TEMP              150.0      1.0    149.0

SQL> select TABLESPACE_NAME,TABLESPACE_SIZE/1024/1024 "TABLESPACE_SIZE", FREE_SPACE/1024/1024 "FREE_SPACE" from dba_temp_free_space;

TABLESPACE_NAME                TABLESPACE_SIZE FREE_SPACE
------------------------------ --------------- ----------
TEMP                                        50         49

Below are the queries to Shrink TEMP Tablespace in Oracle:

Shrink TEMP Tablespace using alter tablespace command
SQL> ALTER TABLESPACE temp SHRINK SPACE KEEP 50M;.


Shrink TEMPFILE using alter tablespace command
SQL> ALTER TABLESPACE temp SHRINK TEMPFILE '/u01/app/oracle/oradata/TEST11G/temp01.dbf' KEEP 40M;


Shrink TEMP Tablespace to the smallest possible size:
SQL> ALTER TABLESPACE temp SHRINK SPACE;

Below are the examples to shrink tempfile:

Before resizing temp files:
SQL> select FILE_NAME,BYTES/1024/1024/1024 from dba_temp_files;
FILE_NAME
BYTES/1024/1024/1024
/scratch/u01/app/oracle/OTMSTG/temp01.dbf
.001945496
/scratch/u01/app/oracle/OTMSTG/temp02.dbf
31.9814377

After resizing tempfiles:

SQL> ALTER TABLESPACE TEMP SHRINK TEMPFILE '/scratch/u01/app/oracle/OTMSTG/temp02.dbf' KEEP 1G;

SQL> col FILE_NAME for a45;
SQL> select FILE_NAME,BYTES/1024/1024/1024,MAXBYTES/1024/1024/1024 from dba_temp_files;
FILE_NAME BYTES/1024/1024/1024 MAXBYTES/1024/1024/1024

/scratch/u01/app/oracle/OTMSTG/temp01.dbf .001945496 31.9999847
/scratch/u01/app/oracle/OTMSTG/temp02.dbf 1.00096893 31.9999847