André Spadini | MultiCloud | DBA

André Spadini | MultiCloud | DBA

Oracle: Tablespace Size

Oracle: Tablespace Size

Subscribe to my newsletter and never miss my upcoming articles

Segue abaixo query para analisar o uso de tablespace no oracle, já levando em consideração o taxa de autoextend.

select
                a.tablespace_name,
                round(SUM(a.bytes)/(1024*1024*1024)) CURRENT_GB,
                round(SUM(decode(b.maxextend, null, A.BYTES/(1024*1024*1024), 
                b.maxextend*8192/(1024*1024*1024)))) MAX_GB,
                (SUM(a.bytes)/(1024*1024*1024) - round(c.Free/1024/1024/1024)) USED_GB,
                round((SUM(decode(b.maxextend, null, A.BYTES/(1024*1024*1024), 
                b.maxextend*8192/(1024*1024*1024))) - (SUM(a.bytes)/(1024*1024*1024) - 
                round(c.Free/1024/1024/1024))),2) FREE_GB,
                round(100*(SUM(a.bytes)/(1024*1024*1024) - 
                round(c.Free/1024/1024/1024))/(SUM(decode(b.maxextend, null, A.BYTES/(1024*1024*1024), 
                b.maxextend*8192/(1024*1024*1024))))) USED_PCT
from
                dba_data_files a,
                sys.filext$ b,
                (SELECT
                               d.tablespace_name ,sum(nvl(c.bytes,0)) Free
                FROM
                               dba_tablespaces d,
                               DBA_FREE_SPACE c
                WHERE
                               d.tablespace_name = c.tablespace_name(+)
                               group by d.tablespace_name) c
WHERE
                a.file_id = b.file#(+)
                and a.tablespace_name = c.tablespace_name
GROUP BY a.tablespace_name, c.Free/1024
ORDER BY tablespace_name;

Saída do comando:

-- Saída
TABLESPACE_NAME                CURRENT_GB     MAX_GB    USED_GB    FREE_GB   USED_PCT
------------------------------ ---------- ---------- ---------- ---------- ----------
ACESDATA                                1         32 1.05175781      30.95          3
ACESINDX                                0         32 .016601563      31.98          0
ADMIN_IDX                               0         32 .009765625      31.99          0
ALMOXARIFADO_DAD                        0         32 .009765625      31.99          0
ALMOXARIFADO_IDX                        0         32 .009765625      31.99          0
ARMAZEM                                 0         32 .037109375      31.96          0
AUDITA                                 16         64 15.1318359      48.87         24
CEP_IDX                                 0         32 .205078125      31.79          1

Abs!

 
Share this