Tablespace Allocated and Used

From NazimWIKI
Revision as of 23:07, 8 November 2019 by Admin (talk | contribs) (Created page with "<blockquote><pre> SELECT A.RT , A.ALLOCATED , B.USED FROM (SELECT TO_CHAR(ROLLUP_TIMESTAMP,'YYYY-MM') RT , AVG(AVERAGE) ALLOCATED FROM MGMT$...")
(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)
Jump to navigation Jump to search
SELECT A.RT
     , A.ALLOCATED
     , B.USED
  FROM (SELECT TO_CHAR(ROLLUP_TIMESTAMP,'YYYY-MM') RT
             , AVG(AVERAGE) ALLOCATED
          FROM MGMT$METRIC_DAILY 
         WHERE TARGET_NAME LIKE '%&DB_NAME%'
           AND METRIC_NAME = 'tbspAllocation' 
           AND KEY_VALUE = '&TBSPC_NAME'
           AND METRIC_COLUMN = 'spaceAllocated' 
        GROUP BY TO_CHAR(ROLLUP_TIMESTAMP,'YYYY-MM') ) a
     , (SELECT TO_CHAR(ROLLUP_TIMESTAMP,'YYYY-MM') RT
             , AVG(AVERAGE) USED
          FROM MGMT$METRIC_DAILY 
         WHERE TARGET_NAME LIKE '%&&DB_NAME%' 
           AND METRIC_NAME = 'tbspAllocation' 
           AND KEY_VALUE = '&&TBSPC_NAME'
           AND METRIC_COLUMN = 'spaceUsed' 
        GROUP BY TO_CHAR(ROLLUP_TIMESTAMP,'YYYY-MM')) b
  WHERE A.RT = B.RT
ORDER BY A.RT DESC;