----------------------------------
-- SCRIPT tsfreeale.sql
-- INICO DO SCRIPT
-- ALEXANDRE PIRES 14/05/2012
----------------------------------
rem
rem ARQUIVO
rem tsfreeale.sql
rem
rem FINALIDADE
rem Exibe informacoes sobre os espacos livres em tablespaces em 80 colunas.
rem
rem PARAMETROS
rem 1 = Parte do nome da tablespace
rem
rem MODIFICACOES
rem 2011-05-17 Pires criacao do script
rem
CLEAR COLUMNS
CLEAR BREAKS
CLEAR COMPUTES
SET PAGES 500
set feedback off
col TABLESPACE_NAME for a22
col ARQUIVOS for 99
col free_Gbytes for 999,999,999,999.99
col used_Gbytes for 999,999,999,999.99
col total_Gbytes for 999,999,999,999.99
col PCT for 999
break on MG skip 1 on report
compute sum of free_Gbytes on MG
compute sum of free_Gbytes on report
compute sum of used_Gbytes on MG
compute sum of used_Gbytes on report
compute sum of total_Gbytes on MG
compute sum of total_Gbytes on report
--break on report skip 1
select 'DATAFILE' as MG
, tbs.tablespace_name as tablespace_name
, arquivos
, tot_bytes/1024/1024/1024 as total_Gbytes
, (tot_bytes - nvl(livres,0))/1024/1024/1024 as used_Gbytes
, nvl(livres,0)/1024/1024/1024 as free_Gbytes
, round(100*nvl(livres,0)/tot_bytes) as pct
from
( select tablespace_name
, count(*) as arquivos
, sum(bytes) as tot_bytes
from dba_data_files
group by tablespace_name ) tot
, ( select tablespace_name
, count(*) as qtde
, sum(bytes) as livres
, min(bytes) as menor_free
, max(bytes) as maior_free
from dba_free_space
group by tablespace_name ) free
, dba_tablespaces tbs
where tot.tablespace_name = tbs.tablespace_name (+)
and tot.tablespace_name = free.tablespace_name (+)
--and 100*nvl(livres,0)/tot_bytes <5
--and tot.tablespace_name not in ('TEMP1', 'TEMP2','TEMP3')
union all
select 'TEMPFILE',tablespace_name, count(*) as arquivos, sum(bytes)/1024/1024/1024 total_Gbytes, sum(bytes)/1024/1024/1024 used_Gbytes,0 free_Gbytes,100.01 pct from dba_temp_files group by tablespace_name
union all
select 'STANDBYLOG', 'STANDBY_LOG'||GROUP#,1,(bytes)/1024/1024/1024 total_Gbytes,0 used_Gbytes, (bytes)/1024/1024/1024 free_Gbytes, 100.02 pct from V$STANDBY_LOG
union all
select 'LOGFILE', 'ONLINE_LOG'||GROUP#,1,(bytes)/1024/1024/1024 total_Gbytes,0 used_Gbytes, (bytes)/1024/1024/1024 free_Gbytes, 100.02 pct from v$log
order by pct,2
/
prompt
--clear breaks
--clear compute
set feedback 6
----------------------------------
-- FIM DO SCRIPT --
Anúncios
