Query para obtener fragmentacion Tablespace

[Wilson Barraza]

Por datafile

select
total.tablespace_name tsname,
total.FILE_NAME file_name,
count(free.bytes) nfrags,
nvl(max(free.bytes)/1024,0) mxfrag,
total.bytes/1024 totsiz,
nvl(sum(free.bytes)/1024,0) avasiz,
round(1-nvl(sum(free.bytes),0)/total.bytes)*100 pctusd
from dba_data_files total, dba_free_space free
where total.tablespace_name = free.tablespace_name(+)
and total.file_id=free.file_id(+)
group by total.tablespace_name,total.FILE_NAME,total.bytes
/

Por Tablespace

select tablespace_name,
max(bytes/1024/1024) "ext+mas grande MB",
min(bytes/1024/1024) "ext+mas chico MB",
to_char(sum(bytes/1024/1024),'999,999,999,999,999') "total libre MB",
to_char(count(*),'999,999') pedazos
from dba_free_space
group by tablespace_name
order by 1