Announcement

Collapse

http://progeeking.com

See more
See less

Tablespace used space

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Tablespace used space

    Code:
     
     
    select 
    a.TABLESPACE_NAME,
    a.total,nvl(b.used,0) USED,
    nvl((b.used/a.total)*100,0) PCT_USED
    from
    (select TABLESPACE_NAME,
    sum(bytes)/(1024*1024) total
    from sys.dba_data_files
    group by TABLESPACE_NAME) a,
    (select TABLESPACE_NAME,bytes/(1024*1024) used 
    from sys.SM$TS_USED) b
    where a.TABLESPACE_NAME=b.TABLESPACE_NAME(+);
    Code:
     
    Tablespace               TOTAL       USED   PCT_USED
    ------------------- ---------- ---------- ----------
    SYSAUX                806.8125   731.0625 90.6112015
    UNDOTBS1                   195     6.6875 3.42948718
    USERS                        5       .375        7.5
    SYSTEM                     700   695.9375 99.4196429
    UNDOTBS2                   175     8.3125       4.75
    SQL>

  • #2
    This is one of the simplest query for the same that I came across and we use it for monitoring as well:
    SELECT TABLESPACE_NAME,SUM(BYTES)/1024/1024/1024"FREE SPACE(GB)"FROM DBA_FREE_SPACE GROUPBY TABLESPACE_NAME;

    Comment

    Working...
    X