Tuesday, September 21, 2010

Check Free Space In Tablespaces

The following SQL helps to check the tablespace sizes and the free space available. We can even see the largest continuous space available.


select *
from (
select tsp.tablespace_name
, round(df.sum_bytes/1024/1024) total_mb
, round(df.sum_bytes/1024/1024 - nvl(fs.sum_free,0)/1024/1024) sum_used_mb
, round(nvl(fs.sum_free,0)/1024/1024) sum_free_mb
, round(df.sum_bytes/1024/1024/1024) total_gb
, round(df.sum_bytes/1024/1024/1024 - nvl(fs.sum_free,0)/1024/1024/1024) sum_used_gb
, round(nvl(fs.sum_free,0)/1024/1024/1024) sum_free_gb
, round(nvl(fs.sum_free/df.sum_bytes *100,0)) pct_free
, substr(tsp.status,1,2)||substr(contents,1,1) status
, tsp.extent_management
, tsp.allocation_type
, case
when 'YES' in
(select autoextensible
from dba_data_files df
where df.tablespace_name = tsp.tablespace_name
)
then 'Y'
else 'N'
end as auto
, tsp.logging
, round(nvl(fs.max_bytes,0)/1024/1024) max_free_mb
, nvl(fs.cnt_free,0) free_count
from dba_tablespaces tsp
, (
select tablespace_name
, sum(bytes) sum_free
, max(bytes) max_bytes
, count(*) cnt_free
from dba_free_space
group by tablespace_name
) fs
, (
select tablespace_name
, sum(bytes) sum_bytes
from dba_data_files
group by tablespace_name
) df
where tsp.tablespace_name = fs.tablespace_name(+)
and tsp.tablespace_name = df.tablespace_name
union all
select tsp.tablespace_name
, round(df.sum_bytes/1024/1024) total_mb
, round(df.sum_bytes/1024/1024 - (df.sum_bytes-nvl(fs.sum_free,0))/1024/1024) sum_used_mb
, round((df.sum_bytes-nvl(fs.sum_free,0))/1024/1024) sum_free_mb
, round(df.sum_bytes/1024/1024/1024) total_gb
, round(df.sum_bytes/1024/1024/1024 - (df.sum_bytes-nvl(fs.sum_free,0))/1024/1024/1024) sum_used_gb
, round((df.sum_bytes-nvl(fs.sum_free,0))/1024/1024/1024) sum_free_gb
, round(((df.sum_bytes-nvl(fs.sum_free,0))/df.sum_bytes*100)) pct_free
, substr(tsp.status,1,2)||substr(contents,1,1) status
, tsp.extent_management
, tsp.allocation_type
, case
when 'YES' in
(select autoextensible
from dba_temp_files tf
where tf.tablespace_name = tsp.tablespace_name
)
then 'Y'
else 'N'
end as auto
, tsp.logging
, null max_free_mb
, null count
from dba_tablespaces tsp
, (
select tablespace_name
, sum(bytes_cached) sum_free
from v$temp_extent_pool
group by tablespace_name
) fs
, (
select tablespace_name
, sum(bytes) sum_bytes
from dba_temp_files
group by tablespace_name
) df
where tsp.tablespace_name = fs.tablespace_name(+)
and tsp.tablespace_name = df.tablespace_name
)
order by tablespace_name
;

No comments: