Reclaim space from TEMP Tablespace

In this post we'll see how to reclaim unused space from TEMP tablespace of Oracle database.

My Environment:
DB : Oracle 12c (12.1.0.2.0)

First check the tablesapce usage of TEMP.

Here the above SS shows free space is 0. This should not be the case. So we need to check the space used by TEMP segments from different views.

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;

Select TABLESPACE_NAME,TABLESPACE_SIZE/1024/1024 "TABLESPACE_SIZE",  FREE_SPACE/1024/1024 "FREE_SPACE" from dba_temp_free_space;

Select TABLESPACE_NAME,TABLESPACE_SIZE/1024/1024 "TABLESPACE_SIZE", ALLOCATED_SPACE/1024/1024 "ALLOCATED_SPACE", FREE_SPACE/1024/1024 "FREE_SPACE"  from dba_temp_free_space;

So now it's clear there is some free space available with the TEMP tablespace.

Now we'll try to reclaim the unused space from the temp tablespace by using shrink method.

In the above SS, I tried to shrink the space of TEMP1 tablespace upto 500M, so it errors out since it uses more data than the instructed size.

This time I tried to give the actual size and the least possible size of the tablespace. Now it worked well on the temp tablespace TEMP2.

Now we can compare the sizes of the temp tablespaces TEMP1 and TEMP2 before and after the shrink operation, to understand the difference in sizes of the tablespaces.

In tablespace TEMP1 there is no difference in size but on TEMP2 the  shrink operation has shown its effect and resized the table to 1GB from 110GB.

Using this technique we can reclaim the unused space from TEMP tablesapces.

Note: Shrink operation will be time consuming if the tablespace is large tablespace. Check the size of the tablespace and then perform the shrink operation.


                         ****************************நன்றி****************************