Home > Database > Mysql Tutorial > body text

Statistics on the daily data growth of the database

大家讲道理
Release: 2016-11-12 10:40:34
Original
5098 people have browsed it

SQL语言: 统计数据库每天的数据增长量
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;
Copy after login

source:php.cn
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Popular Tutorials
More>
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template
About us Disclaimer Sitemap
php.cn:Public welfare online PHP training,Help PHP learners grow quickly!