Administration

Reclaim wasted temp space online

User Rating:  / 0
PoorBest 
Parent Category: Articles
Created on Thursday, 03 October 2013 14:32
Last Updated on Thursday, 23 March 2017 09:08
Published on Thursday, 03 October 2013 14:32
Written by Guy Lambregts
Hits: 6514

Reclaim wasted temp space online


Prior to 11G if the DBA would like to reclaim space from the temporary tablespace, he/she could run into the below situation


14:00:38 SILVER> alter database tempfile 1 resize 2G;
alter database tempfile 1 resize 2G
*
ERROR at line 1:
ORA-03297: file contains used data beyond requested RESIZE value

 

From 11G onwards


Similar to online data space compact and shrink


14:01:53 SILVER> select tablespace_name,tablespace_size/(1024*1024*1024) "tablespace_size",
14:02:09 2                              allocated_space/(1024*1024*1024) "allocated_space",
14:02:17 3                              free_space/(1024*1024*1024)      "free_space"
14:02:25 4       from dba_temp_free_space;

TABLESPACE_NAME                tablespace_size  allocated_space free_space
------------------------------ ---------------- --------------- ----------------
TEMP                           8                .03             7.966796875

 

This is how the DBA can shrink the temp space

 

14:05:27 SILVER> alter tablespace TEMP shrink space keep 2G;

Tablespace altered.

 

One can also do the operation at level of the tempfile


14:05:27 SILVER> alter tablespace TEMP shrink tempfile '/u01/oradata/SILVER/temp01.dbf' keep 2G;