Espacio ocupado/libre por TBL y cálculo p/umbral. | ExpoDBA

Espacio ocupado/libre por TBL y cálculo p/umbral.

Espacio ocupado/libre por TBL y cálculo p/umbral.

set cmdsep on
set cmdsep !
set concat on
set escape off
set feedback on
set feedback 1
set linesize 500
set long  32000
set longc 32000
set pagesize 1000
set recsep off
set serveroutput on size 1000000
set show off
set tab off
set verify off
set trimout on
set trimspool on
define M=1048576
col Ocupado format 999999.99
col Agregar format 999999.99
col Tamanio format 999999999
col file_name format a40
col Comando new_value Ejecutar

-- La formula para calcular el espacio ocupado es:
-- 	100-(EspacioLibre*100/EspacioTotal)
select 100-(
	(select sum(bytes) from dba_free_space where tablespace_name = upper('&&tbl'))*100/
	(select sum(bytes) from dba_data_files where tablespace_name = upper('&tbl'))
	) Ocupado
from dual;
-- La formula para calcular el espacio a agregar es:
-- 	(EspacioTotal*(100-PctLibre)-100*EspacioLibre)/PctLibre
select (
	(select sum(bytes) from dba_data_files where tablespace_name = upper('&tbl'))*(100-&&pct) -
	100*(select sum(bytes) from dba_free_space where tablespace_name = upper('&tbl'))
	)/&pct/&M Agregar
from dual;
select file_name, bytes/&M Tamanio from dba_data_files where tablespace_name = upper('&tbl');
select distinct 'df -k' ||
	max(decode(linea,1, ' ' || Filesystem,null)) ||
	max(decode(linea,2, ' ' || Filesystem,null)) ||
	max(decode(linea,3, ' ' || Filesystem,null)) ||
	max(decode(linea,4, ' ' || Filesystem,null)) ||
	max(decode(linea,5, ' ' || Filesystem,null)) ||
	max(decode(linea,6, ' ' || Filesystem,null)) ||
	max(decode(linea,7, ' ' || Filesystem,null)) ||
	max(decode(linea,8, ' ' || Filesystem,null)) ||
	max(decode(linea,9, ' ' || Filesystem,null)) ||
	max(decode(linea,10, ' ' || Filesystem,null)) ||
	max(decode(linea,11, ' ' || Filesystem,null)) ||
	max(decode(linea,12, ' ' || Filesystem,null)) Comando
from (
	select rownum linea, Filesystem
	from (
		select distinct substr(file_name,1,instr(file_name,'/',-1)-1) Filesystem
		from dba_data_files
		where tablespace_name like upper('&tbl')
	)
);
!&Ejecutar
undefine Ejecutar
undefine tbl
undefine pct