統計資料庫每天的資料增長量

統計資料庫每天的資料增長量

---

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;


SQL> select t.*
  2    from (SELECT D.TABLESPACE_NAME,
  3                 SPACE "SUM_SPACE(M)",
  4                 BLOCKS SUM_BLOCKS,
  5                 SPACE - NVL(FREE_SPACE, 0) "USED_SPACE(M)",
  6                 ROUND((1 - NVL(FREE_SPACE, 0) / SPACE) * 100, 2) "USED_RATE(%)",
  7                 FREE_SPACE "FREE_SPACE(M)"
  8            FROM (SELECT TABLESPACE_NAME,
  9                         ROUND(SUM(BYTES) / (1024 * 1024), 2) SPACE,
 10                         SUM(BLOCKS) BLOCKS
 11                    FROM DBA_DATA_FILES
 12                   GROUP BY TABLESPACE_NAME) D,
 13                 (SELECT TABLESPACE_NAME,
 14                         ROUND(SUM(BYTES) / (1024 * 1024), 2) FREE_SPACE
 15                    FROM DBA_FREE_SPACE
 16                   GROUP BY TABLESPACE_NAME) F
 17           WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)
 18          UNION ALL
 19          SELECT D.TABLESPACE_NAME,
 20                 SPACE "SUM_SPACE(M)",
 21                 BLOCKS SUM_BLOCKS,
 22                 USED_SPACE "USED_SPACE(M)",
 23                 ROUND(NVL(USED_SPACE, 0) / SPACE * 100, 2) "USED_RATE(%)",
 24                 SPACE - USED_SPACE "FREE_SPACE(M)"
 25            FROM (SELECT TABLESPACE_NAME,
 26                         ROUND(SUM(BYTES) / (1024 * 1024), 2) SPACE,
 27                         SUM(BLOCKS) BLOCKS
 28                    FROM DBA_TEMP_FILES
 29                   GROUP BY TABLESPACE_NAME) D,
 30                 (SELECT TABLESPACE,
 31                         ROUND(SUM(BLOCKS * 8192) / (1024 * 1024), 2) USED_SPACE
 32                    FROM V$SORT_USAGE
 33                   GROUP BY TABLESPACE) F
 34           WHERE D.TABLESPACE_NAME = F.TABLESPACE(+)) t
 35  order by "USED_RATE(%)" desc;

TABLESPACE_NAME                SUM_SPACE(M) SUM_BLOCKS USED_SPACE(M) USED_RATE(%) FREE_SPACE(M)
------------------------------ ------------ ---------- ------------- ------------ -------------
SYSTEM                                  700      89600        506.94        72.42        193.06
SYSAUX                                  600      76800        264.69        44.12        335.31
USERS                                     5        640          1.25           25          3.75
TEMP                                     31       3968             3         9.68            28
UNDOTBS1                                215      27520         12.81         5.96        202.19
DBADATATBS                             2048     262144          1.12          .05       2046.88

6 rows selected.

SQL>