公司對產線DB采用了archive技術,將DB中長時間不使用的數據遷移到archiveDB中，以此來對產線DB實現“瘦身”目的。但是目前的archiveDB已經沒有空間支持，需要擴容或者是購買新的server.需要預測server的存儲空間。首先需要知道數據的日增長量：通過對表空間的使用情況獲得每日的數據庫增長量

	            http://fayaa.com/code/view/708/

	set linesize 1000 

set pagesize 100 

select t.*

  from (SELECT D.TABLESPACE_NAME,

               SPACE "SUM_SPACE(M)",

               BLOCKS SUM_BLOCKS,

               SPACE - NVL(FREE_SPACE, 0) "USED_SPACE(M)",

               ROUND((1 - NVL(FREE_SPACE, 0) / SPACE) * 100, 2) "USED_RATE(%)",

               FREE_SPACE "FREE_SPACE(M)"

          FROM (SELECT TABLESPACE_NAME,

                       ROUND(SUM(BYTES) / (1024 * 1024), 2) SPACE,

                       SUM(BLOCKS) BLOCKS

                  FROM DBA_DATA_FILES

                 GROUP BY TABLESPACE_NAME) D,

               (SELECT TABLESPACE_NAME,

                       ROUND(SUM(BYTES) / (1024 * 1024), 2) FREE_SPACE

                  FROM DBA_FREE_SPACE

                 GROUP BY TABLESPACE_NAME) F

         WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)

        UNION ALL

        SELECT D.TABLESPACE_NAME,

               SPACE "SUM_SPACE(M)",

               BLOCKS SUM_BLOCKS,

               USED_SPACE "USED_SPACE(M)",

               ROUND(NVL(USED_SPACE, 0) / SPACE * 100, 2) "USED_RATE(%)",

               SPACE - USED_SPACE "FREE_SPACE(M)"

          FROM (SELECT TABLESPACE_NAME,

                       ROUND(SUM(BYTES) / (1024 * 1024), 2) SPACE,

                       SUM(BLOCKS) BLOCKS

                  FROM DBA_TEMP_FILES

                 GROUP BY TABLESPACE_NAME) D,

               (SELECT TABLESPACE,

                       ROUND(SUM(BLOCKS * 8192) / (1024 * 1024), 2) USED_SPACE

                  FROM V$SORT_USAGE

                 GROUP BY TABLESPACE) F

         WHERE D.TABLESPACE_NAME = F.TABLESPACE(+)) t

order by "USED_RATE(%)" desc;

	  預測存儲量M=每日增長量×365×3+backup空間-文件歸檔釋放的空間(將保持超過三年的數據采用文件歸檔方式存儲)

ITPub博客

預測DBserver的存儲量

最新文章