Oracle 11g New Feature : Advantage of Shrink Temp Tablespace and Tempfile

The temporary tablespace will increase in size depends upon the amount of sorting happens on the database and it occupies the most of the space in the disk. Sorting might be happening occasionally and for that purpose there is no need of keeping huge amount of temporary tablespace. As and when needed we can increase the temp tablespace size.


Until Oracle 11g there was no command to unallocated or reduce the temporary tablespace size. One workaround used was create a new temporary tablespace smaller in size and allocate this new tablespace to the users and drop the old temporary tablespace. The disadvantage of this method is no sorting operations are permitted during the tablespace being dropped.

In Oracle 11g there is one SQL command to shrink the temporary tablespace which is applicable only for locally managed temporary tablespace.  

Command Syntax:
ALTER TABLESPACE tablespace SHRINK SPACE | TEMPFILE tempfile_name [KEEP size];

Method 1: Shrink Space
select file_name, bytes/1024/1024/1024 from dba_temp_files
SQL>
SQL> /

FILE_NAME                                BYTES/1024/1024/1024
---------------------------------------- --------------------
/data/oracle/oradata/prod9/temp01.dbf                  3.671875

  1  select TABLESPACE_NAME , TABLESPACE_SIZE/1024/1024/1024 TOTAL, ALLOCATED_SPACE/1024/1024/1024 ALLOCATED, FREE_SPACE/1024/1024/1024 FREE
  2* FROM DBA_TEMP_FREE_SPACE
SQL> /

TABLESPACE_NAME                     TOTAL  ALLOCATED       FREE
------------------------------ ---------- ---------- ----------
TEMP                             3.671875   3.015625 3.67089844

SQL> ALTER TABLESPACE temp SHRINK SPACE KEEP 1028M; à Keep Keyword is optional

Tablespace altered.

How to Check TEMP tablespace usage? DBA_TEMP_FREE_SPACE

SQL> select TABLESPACE_NAME , TABLESPACE_SIZE/1024/1024/1024 TOTAL, ALLOCATED_SPACE/1024/1024/1024 ALLOCATED, FREE_SPACE/1024/1024/1024 FREE
  2  FROM DBA_TEMP_FREE_SPACE;

TABLESPACE_NAME                     TOTAL  ALLOCATED       FREE
------------------------------ ---------- ---------- ----------
TEMP                           1.00488281 .000976563 1.00390625

ls -ltr /data/oracle/oradata/prod9/temp01.dbf
-rw-r-----   1 oracle   dba      1078992896 Jul  7 22:16 /data/oracle/oradata/prod9/temp01.dbf

How to resize a temp file?

SQL> alter database tempfile  '/data/oracle/oradata/prod9/temp01.dbf' resize 3760M;

Database altered.

SQL> select TABLESPACE_NAME , TABLESPACE_SIZE/1024/1024/1024 TOTAL, ALLOCATED_SPACE/1024/1024/1024 ALLOCATED, FREE_SPACE/1024/1024/1024 FREE
  2  FROM DBA_TEMP_FREE_SPACE;

TABLESPACE_NAME                     TOTAL  ALLOCATED       FREE
------------------------------ ---------- ---------- ----------
TEMP                             3.671875 .000976563 3.67089844

Method 2: Shrink Tempfile

SQL> select TABLESPACE_NAME , TABLESPACE_SIZE/1024/1024/1024 TOTAL, ALLOCATED_SPACE/1024/1024/1024 ALLOCATED, FREE_SPACE/1024/1024/1024 FREE
  2  FROM DBA_TEMP_FREE_SPACE;

TABLESPACE_NAME                     TOTAL  ALLOCATED       FREE
------------------------------ ---------- ---------- ----------
TEMP                             3.671875 .000976563 3.67089844

SQL> ALTER TABLESPACE temp SHRINK TEMPFILE '/data/oracle/oradata/prod9/temp01.dbf' KEEP 1028M;

Tablespace altered.

SQL> select TABLESPACE_NAME , TABLESPACE_SIZE/1024/1024/1024 TOTAL, ALLOCATED_SPACE/1024/1024/1024 ALLOCATED, FREE_SPACE/1024/1024/1024 FREE
  2  FROM DBA_TEMP_FREE_SPACE;

TABLESPACE_NAME                     TOTAL  ALLOCATED       FREE
------------------------------ ---------- ---------- ----------
TEMP                           1.00487518 .000968933 1.00390625

SQL> !ls -ltr /data/oracle/oradata/prod9/temp01.dbf
-rw-r-----   1 oracle   dba      1078984704 Jul  7 22:31 /data/oracle/oradata/prod9/temp01.dbf
DBA Tips Data Pump Reference

0 comments:

Post a Comment

 

dba topics. Copyright 2011-16 All Rights Reserved | Site Map | Contact | Disclaimer | Google