Tablespace Allocated and Used

From NazimWIKI
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;