SQL语言: 统计数据库每天的数据增长量
SQL语言: 统计数据库每天的数据增长量需求：系统有时要统计数据库总共多少空间、剩余多少空间、使用空间、空间使用率、空间空闲率
实现：
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(+)) torder by "USED_RATE(%)" desc;
运行效果：
http://dl.iteye.com/upload/attachment/371883/994428b1-4b95-37f2-a24d-f3e9ca44725c.jpg
测试实例：Oracle测试成功，MySQL测试不通过，DB2未测
页:
[1]