Wednesday, September 28, 2011

Temporary Tablespace Usage

Oracle uses Temp tablespace for operations like sorting. A database can have multiple Temp tablespaces but only one of them can be assigned as the default tablespace.

So before getting into Temp tablespace monitoring stuff, remember you wont get that information from DBA_FREE_SPACE view. Instead use V$TEMP_SPACE_HEADER as shown below


SQL> select * from (select a.tablespace_name, 
      sum(a.bytes/1024/1024) allocated_mb 
      from dba_temp_files a 
      where a.tablespace_name = upper('&&temp_tsname') group by a.tablespace_name) x, 
      (select sum(b.bytes_used/1024/1024) used_mb, 
      sum(b.bytes_free/1024/1024) free_mb 
      from v$temp_space_header b 
      where b.tablespace_name=upper('&&temp_tsname') group by b.tablespace_name); 

Enter value for temp_tsname: TEMP 



TABLESPACE_NAME                ALLOCATED_MB    USED_MB    FREE_MB
------------------------------------------------------------------------------------------------------------
TEMP                                                      44367                 10717            33650


No comments:

Post a Comment