VERIFICAR Y SELECCIONAR UN TABLESPACE CON TAMAÑO SUPERIOR AL 90% Y GENERAR SCRIPT PARA AMPLIAR

--SELECCIONAR UN TABLESPACE CON TAMAÑO SUPERIOR AL 90% Y GENERAR SCRIPT PARA AMPLIAR


set lines 150
col a.tablespace_name format a25
col Mbusado format a10
col Sentencia format a90
col Mbusado format 999,999,999

select a.tablespace_name,((a.bytes/1024/1024) - (b.bytes/1024/1024)) Mbusado,round(((a.bytes-b.bytes)/a.bytes)*100,2) pct_usado,
'alter database datafile '''||c.file_name||''' resize '||round(((c.bytes+0.16*a.bytes - 1.16*b.bytes)/1024/1024),0)||'M;' Sentencia
from  (select tablespace_name
  , sum(bytes) bytes
from dba_data_files
  group by tablespace_name
) a
,(select tablespace_name
  , sum(bytes) bytes
  , max(bytes)/1024/1024 largest
from dba_free_space
  group by tablespace_name
) b, (select a.tablespace_name,a.file_name, a.bytes
 from (select tablespace_name, file_name, bytes from dba_Data_files) a,
(select tablespace_name, min(bytes) min from dba_data_files
  group by tablespace_name) b
where a.bytes=b.min
and a.tablespace_name=b.tablespace_name
order by 1) c
where a.tablespace_name = b.tablespace_name
and c.tablespace_name = b.tablespace_name
and ((a.bytes-b.bytes)/a.bytes)*100 > 90
--and (c.bytes)/1024/1024 < 20000
order by a.Tablespace_name asc