Com o comando abaixo, podemos listar todas as tablespaces de banco do dados obtendo as seguintes informações:
Nome da tablespace.......................: TABLESPACE_NAME
Tamanho total da tablespace:.............: TOTAL_MB
Quantidade em MB utilizada...............: USADO_MB
Quantidade em MB livre para utilização...: LIVRE_MB
Porcentagem de espaço livre na tablespace: PCT_LIVRE
Porcentagem de espaço usado na tablespace: PCT_USADO
set lines 200 pages 1000
COL TABLESPACE_NAME FORMAT A30
COL TOTAL_MB format 999,999,999
COL USADO_MB format 999,999,999
COL LIVRE_MB format 999,999,999
COL PCT_LIVRE format 999,999,999.99
COL PCT_USADO format 999,999,999.99
compute sum of TOTAL_MB on report
compute sum of USADO_MB on report
compute sum of LIVRE_MB on report
compute avg of PCT_LIVRE on report
compute avg of PCT_USADO on report
select a.tablespace_name, a.total TOTAL_MB, a.total - b.free USADO_MB,
b.free LIVRE_MB, (b.free * 100) / a.total PCT_LIVRE,
(100 - ((b.free * 100) / a.total)) PCT_USADO
from (select sum(bytes)/1024/1024 total, tablespace_name from dba_data_files group by tablespace_name
union
select sum(bytes)/1024/1024 total, tablespace_name from dba_temp_files group by tablespace_name) a,
(select sum(bytes)/1024/1024 free, tablespace_name from dba_free_space group by tablespace_name) b
where b.tablespace_name(+) = a.tablespace_name
order by 6 desc;
A partir do comando acima, podemos diversificar as consultas, como no exemplo abaixo onde podemos listar apenas as tablespaces com menos de 10% de espaço livre para utilização.
set lines 200 pages 1000
COL TABLESPACE_NAME FORMAT A30
COL TOTAL_MB format 999,999,999
COL USADO_MB format 999,999,999
COL LIVRE_MB format 999,999,999
COL PCT_LIVRE format 999,999,999.99
COL PCT_USADO format 999,999,999.99
compute sum of TOTAL_MB on report
compute sum of USADO_MB on report
compute sum of LIVRE_MB on report
compute avg of PCT_LIVRE on report
compute avg of PCT_USADO on report
select a.tablespace_name, a.total TOTAL_MB, a.total - b.free USADO_MB,
b.free LIVRE_MB, (b.free * 100) / a.total PCT_LIVRE,
(100 - ((b.free * 100) / a.total)) PCT_USADO
from (select sum(bytes)/1024/1024 total, tablespace_name from dba_data_files group by tablespace_name
union
select sum(bytes)/1024/1024 total, tablespace_name from dba_temp_files group by tablespace_name) a,
(select sum(bytes)/1024/1024 free, tablespace_name from dba_free_space group by tablespace_name) b
where b.tablespace_name(+) = a.tablespace_name
and (trunc((b.free * 100) / a.total)) <= 10
order by 6 desc;