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