Systems Engineering and RDBMS

Shrinking temporary tablespace in Oracle 11g

Posted by decipherinfosys on March 13, 2009

We have touched upon online segment shrinking in Oracle 10g. Oracle came up with a new feature of shrinking temporary tablespace using SHRINK SPACE or SHRINK TEMPFILE command in 11g to reclaim the space back from temporary tablespace. It is much easier solution rather than dropping and re-creating tablespace again with the smaller size.  Let us start with examining the current state of temporary tablespace.

SQL> select file_name,bytes,blocks from dba_temp_files;

FILE_NAME                                          BYTES     BLOCKS
——————————————— ———- ———-
+DB9_DATA01/orcl/tempfile/temp.264.680771683    28311552       3456

Now let us issue SHRINK SPACE command and re-examine the space occupied.

SQL> ALTER TABLESPACE TEMP SHRINK SPACE;

Tablespace altered.

SQL> select file_name,bytes,blocks from dba_temp_files;

FILE_NAME                                          BYTES     BLOCKS
——————————————— ———- ———-
+DB9_DATA01/orcl/tempfile/temp.264.680771683     1114112        136

We reclaim 26M back leaving only 1M for temporary tablespace. Now let us run following command to resize the temporary tablespace and increase its size.

SQL> ALTER DATABASE TEMPFILE ‘+DB9_DATA01/orcl/tempfile/temp.264.680771683’ RESIZE 200M;

Database altered.

SQL> select name,bytes,blocks from v$tempfile;

NAME                                               BYTES     BLOCKS
——————————————— ———- ———-
+DB9_DATA01/orcl/tempfile/temp.264.680771683   209715200      25600

Tablespace is now resized to 200M. This time we will shrink tablespace using shrink tempfile command. We can also retain the specific size of tablespace by using KEEP clause.

SQL> ALTER TABLESPACE TEMP SHRINK TEMPFILE ‘+DB9_DATA01/orcl/tempfile/temp.264.680771683’ KEEP 100M;

Tablespace altered.

Querying v$tempfile view again, we can see the effect of above command.

SQL> select name,bytes,blocks from v$tempfile;

NAME                                               BYTES     BLOCKS
——————————————— ———- ———-
+DB9_DATA01/orcl/tempfile/temp.264.680771683   104923136      12808

We can also use KEEP clause with SHRINK SPACE. IF temporary table contains more than one temp file and SHRINK SPACE command is issued to shrink temporary tablespace, Oracle may shrink one file completely and shrink other file for remaining amount rather than shrinking all the files equally.

This is a very useful feature especially when temporary tablespace has been increased to accommodate large jobs, which require sorting/grouping. We can shrink it back to its original size once that specific job is done.

We cannot perform any one of this command for dictionary managed or permanent tablespaces. We will run into the following error if we try to perform such an operation:

SQL> ALTER TABLESPACE USERS SHRINK SPACE;
ALTER TABLESPACE USERS SHRINK SPACE
*
ERROR at line 1:
ORA-12916: cannot shrink permanent or dictionary managed tablespace

Resources:

  • Book – Oracle 11g New features for Developers and DBAs by Sam Alapati, Charles Kim.
  • Oracle 11g SQL Reference Manual – here.

One Response to “Shrinking temporary tablespace in Oracle 11g”

  1. Anonymous said

    Thank you sir.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: