Consulta Tamanho Tablespace


clear columns
column tablespace format a30
column total_mb format 999,999,999.99
column used_mb format 999,999,999,999.99
column free_mb format 999,999,999.99
column pct_used format 999.99
column graph format a25 heading "GRAPH (X=5%)"
column status format a10
compute sum of total_mb on report
compute sum of used_mb on report
compute sum of free_mb on report
set lines 200 pages 100
select  total.ts tablespace,
        DECODE(total.mb,null,'OFFLINE',dbat.status) status,
        total.mb total_mb,
        NVL(total.mb - free.mb,total.mb) used_mb,
        NVL(free.mb,0) free_mb,
        DECODE(total.mb,NULL,0,NVL(ROUND((total.mb - free.mb)/(total.mb)*100,2),100)) pct_used,
        CASE WHEN (total.mb IS NULL) THEN '['||RPAD(LPAD('OFFLINE',13,'-'),20,'-')||']'
        ELSE '['|| DECODE(free.mb,
                             null,'XXXXXXXXXXXXXXXXXXXX',
                             NVL(RPAD(LPAD('X',trunc((100-ROUND( (free.mb)/(total.mb) * 100, 2))/5),'X'),20,'-'),
                '--------------------'))||']'
         END as GRAPH
from
        dba_tablespaces dbat
        inner join (select tablespace_name ts, sum(bytes)/1024/1024 mb from dba_data_files group by tablespace_name) total on total.ts=dbat.tablespace_name
        left join  (select tablespace_name ts, sum(bytes)/1024/1024 mb from dba_free_space group by tablespace_name) free on total.ts=free.ts
UNION ALL
select s.tablespace_name, 'TEMP', 
        f.total_bytes/1024/1024 total_mb,
        (s.tot_used_blocks*t.block_size)/1024/1024 used_mb,
        (s.free_blk*t.block_size)/1024/1024 free_mb,
        ROUND((s.tot_used_blocks/f.total_blocks)*100,2) pct_used,
        '['||DECODE((s.free_blk*t.block_size),0,'XXXXXXXXXXXXXXXXXXXX',
              NVL(RPAD(LPAD('X',(TRUNC(ROUND(((s.tot_used_blocks*t.block_size)/((s.tot_used_blocks+s.free_blk)*t.block_size))*100,2)/5)),'X'),20,'-'),
                '--------------------'))||']'
from
            (select tablespace_name, sum(used_blocks) tot_used_blocks, sum(FREE_BLOCKS) free_blk from v$sort_segment group by tablespace_name ) s
 inner join (select tablespace_name, sum(blocks) total_blocks, sum(bytes) total_bytes from dba_temp_files group by tablespace_name) f on  f.tablespace_name = s.tablespace_name
 inner join (select tablespace_name, block_size from dba_tablespaces where contents='TEMPORARY') t on  t.tablespace_name = s.tablespace_name
order by 6
/