Tuesday 10 July 2007

Temp tablespace usage Oracle 9i/10g

Below is the query that I would use to find out usage of temp tablespace:

select
df.TABLESPACE_NAME,
df.df_size/1048576 AS TOTAL,
nvl(us.free_sum, 0)/1024/1024 AS USED,
(df.df_size/1048576 - (nvl(us.free_sum, 0)/1024/1024)) AS FREE,
df.FILE_COUNT
from
( select TABLESPACE_NAME,
sum(bytes) as df_size,
count(TABLESPACE_NAME) AS FILE_COUNT
from dba_temp_files
group by TABLESPACE_NAME ) df,

( select TABLESPACE,
sum(BLOCKS)*(select value from v$parameter where upper(name) like '%DB_BLOCK_SIZE%') free_sum
from v$sort_usage group by TABLESPACE ) us

where df.TABLESPACE_NAME = us.TABLESPACE(+)
order by TABLESPACE_NAME;


Sample output:

TABLESPACE_NAME TOTAL USED FREE FILE_COUNT
------------------------------ ---------- ---------- ---------- ----------
TEMP 1024 0 1024 1

No comments: