, ,

Free space in tablespace oracle

пятница, 3 февраля 2012 г. 0 коммент.

This small reports the tablespace name, alloc size, free space, used space, extend size, total size in all the tablespaces in Oracle database.

select a.tablespace_name ,
round(a.bytes_alloc / 1024 / 1024, 2) m_alloc,
round(nvl(b.bytes_free, 0) / 1024 / 1024, 2) m_free,
round((a.bytes_alloc - nvl(b.bytes_free, 0)) / 1024 / 1024, 2) m_used,
round((maxbytes - a.bytes_alloc)/1024/1024, 2) m_extend,
round(maxbytes/1024/1024,2) Max
from ( select f.tablespace_name,
sum(f.bytes) as bytes_alloc,
sum(decode(f.autoextensible, 'YES',greatest(f.maxbytes, f.bytes),'NO', f.bytes)) as maxbytes
from dba_data_files f
group by tablespace_name
union all
select f.tablespace_name,
sum(f.bytes) as bytes_alloc,
sum(decode(f.autoextensible, 'YES',greatest(f.maxbytes, f.bytes),'NO', f.bytes)) as maxbytes
from dba_temp_files f
group by tablespace_name
) a,
( select f.tablespace_name,
sum(f.bytes) bytes_free
from dba_free_space f
group by tablespace_name) b
where a.tablespace_name = b.tablespace_name (+);
Читать полностью