Difference between revisions of "Tablespace Allocated and Used"

From NazimWIKI
Jump to navigation Jump to search
(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$...")
 
(No difference)

Latest revision as of 23:07, 8 November 2019

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;