Wednesday, November 9, 2011

Shrinking TEMP tablespace in Oracle 11g



Oracle 11g has a new feature of shrinking temporary tablespace using SHRINK SPACE or SHRINK TEMPFILE.
It is much easier and better way of reclaiming space rather than dropping and re-creating the tablespace with the smaller size.  


First see the current state of TEMP tablespace using the following SQL 


SQL> select file_name,bytes,blocks from dba_temp_files;



File_NAME                                                            BYTES                 BLOCK
/u02/oradata/kandwh2/TEMP3.dbf                      12883984384 1572752
/u02/oradata/kandwh2/TEMP4.dbf                      12883918848 1572744
/u03/oradata/kandwh2/datafiles/TEMP 5.dbf        2088960                     255

Now, shrink the space using the following SQL statement (we are using 'SHRINK SPACE'  statement)

SQL> ALTER TABLESPACE TEMP SHRINK SPACE;


Now see if we are able to reclaim some space


SQL> select file_name,bytes,blocks from dba_temp_files;


File_NAME                                                                           BYTES                 BLOCK

/u02/oradata/kandwh2/TEMP3.dbf                       1179648         144
/u02/oradata/kandwh2/TEMP4.dbf                       2079391744    253832
/u03/oradata/kandwh2/datafiles/TEMP 5.dbf         2088960         255

We cannot perform any one of this command for dictionary managed or permanent tablespaces.

No comments:

Post a Comment