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:
Post a Comment