Thursday, January 1, 2009

How to check free space in tablespaces

Query shows tablespace usage and lists tablespace names including the database name:
select d.status,
db.name dbname,
d.tablespace_name tsname,
d.extent_management,
d.allocation_type,
to_char(nvl(d.min_extlen / 1024, 0),
'99G999G990D90', 'NLS_NUMERIC_CHARACTERS = '',.'' ')
"ALLOC_SIZE (K)",
d.contents "Type",
case
when(d.contents = 'TEMPORARY') then
to_char(nvl(a.bytes / 1024 / 1024, 0),
'99G999G990D90', 'NLS_NUMERIC_CHARACTERS = '',.'' ')
else
to_char(nvl(t.bytes / 1024 / 1024, 0),
'99G999G990D90', 'NLS_NUMERIC_CHARACTERS = '',.'' ')
end as "Size (M)",
to_char(nvl((a.bytes - nvl(f.bytes, 0)) / 1024 / 1024, 0),
'99G999G990D90', 'NLS_NUMERIC_CHARACTERS = '',.'' ')
"Used (M)",
to_char(nvl((a.bytes - nvl(f.bytes, 0)) / a.bytes * 100, 0),
'990D90', 'NLS_NUMERIC_CHARACTERS = '',.'' ')
"Used (%)"
from sys.dba_tablespaces d,
(select tablespace_name,
sum(bytes) bytes
from dba_data_files
group by tablespace_name)
a,
(select tablespace_name,
sum(bytes) bytes
from dba_temp_files
group by tablespace_name)
t,
(select tablespace_name,
sum(bytes) bytes
from dba_free_space
group by tablespace_name)
f,
v$database db
where d.tablespace_name = a.tablespace_name(+)
and d.tablespace_name = f.tablespace_name(+)
and d.tablespace_name = t.tablespace_name(+)
order by 10 desc;

No comments:

Post a Comment