---------------------------------- -- SCRIPT tsnext.sql -- INICO DO SCRIPT -- ALEXANDRE PIRES 14/05/2012 ---------------------------------- set feedback off column arquivos format 99g999 column kbytes_next format 9g999g999 column kbytes_free format 999g999g999 heading 'TOTAL_FREE|KBYTES' column kbytes_min_free format 99g999g999 heading 'MIN_FREE|KBYTES' column kbytes_max_free format 99g999g999 heading 'MAX_FREE|KBYTES' column kbytes_max_increment format 99g999g999 heading 'MAX_INCREMENT|KBYTES' break on lmt on assm on report select --+ no_merge decode (seg.extent_management, 'LOCAL', 'SIM', 'NAO') as LMT , decode (seg.segment_space_management, 'AUTO', 'SIM', 'NAO') as ASSM , seg.tablespace_name , tbs.arquivos , decode(tbs.max_increment, 0, to_number(null) , tbs.max_increment/1024) as kbytes_max_increment , seg.next_extent/1024 as kbytes_next , seg.objetos , tbs.bytes_free/1024 as kbytes_free , min_free/1024 as kbytes_min_free , max_free/1024 as kbytes_max_free from --- Contagem de objetos por NEXT por tablespace ( select s.tablespace_name , t.extent_management , t.segment_space_management , s.next_extent , count(*) as objetos from dba_segments s, dba_tablespaces t where t.tablespace_name = s.tablespace_name(+) group by s.tablespace_name , t.extent_management , t.segment_space_management , s.next_extent ) seg --- Informacoes de espaco livre, maior incremento, quant. arquivos por tablespace , ( select --+ no_merge d.tablespace_name , d.max_increment , d.arquivos , sum(f.bytes) as bytes_free , min(f.bytes) as min_free , max(f.bytes) as max_free from dba_free_space f , ( select tablespace_name , nvl(max(increment_by*block_size), 0) as max_increment , count(*) as arquivos from dba_data_files, v$datafile where file# = file_id group by tablespace_name ) d where d.tablespace_name = f.tablespace_name(+) group by d.tablespace_name , d.max_increment , d.arquivos ) tbs where tbs.tablespace_name = seg.tablespace_name order by lmt, assm, seg.tablespace_name / prompt clear breaks set feedback 6 ---------------------------------- -- FIM DO SCRIPT --
Anúncios