---------------------------------- -- 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