ITPub博客

首页 > 数据库 > Oracle > Oracle 查询各个 “表空间/数据文件” 的空间使用比情况

Oracle 查询各个 “表空间/数据文件” 的空间使用比情况

Oracle 作者:kingsql 时间:2014-09-09 21:24:11 0 删除 编辑
本博客写于2014-08-28,后续完善的博客为:Oracle 查询各表空间使用情况--完善篇

注释:
  绿色部分SQL:库中各个 '数据文件' 的空间使用比情况;
  蓝色部分SQL:库中各个 '表空间'   的空间使用比情况的2中方案;
  用到视图对应意义 最下文有官方解释.
  

SQL:
--查询 表空间 空间使用比情况 

SELECT C.TABLESPACE_NAME,
       SUM(DATAFILE_TOTAL_GB) TABLESPACE_TOTAL_GB,
       SUM(DATAFILE_FREE_GB) TABLESPACE_FREE_GB,
       SUM(DATAFILE_USER_GB) TABLESPACE_USER_GB,
       ROUND(SUM ("DATAFILE_USER%") / COUNT( 1), 2 ) "TABLESPACE_USER%"
        /*表空间使用比
        解释:按照TABLESPACE分组 ‘SUM使用比’/'COUNT(1)'数据文件个数才是表空间使用比率
        方案2) ROUND(SUM(USER_GB) / SUM(TOTAL_GB) * 100, 2)...因 子查询USER_GB有用到ROUND四舍五入函数...没上一种方式那么准..*/
  FROM ( SELECT DD.FILE_NAME, ---数据文件名
              DD.TABLESPACE_NAME, ---表空间
               ROUND(SUM (DD.DATAFILE_TOTAL_GB), 2) DATAFILE_TOTAL_GB,
               ROUND(SUM (DF.DATAFILE_FREE_GB), 2) DATAFILE_FREE_GB,
               ROUND(SUM (DD.DATAFILE_TOTAL_GB - DF.DATAFILE_FREE_GB), 2) DATAFILE_USER_GB,
               --DATAFILE_USER_GB=DATAFILE_TOTAL_GB-DATAFILE_FREE_GB ..
               --没直接使用 DBA_DATA_FILES.USER_BYTES字段的原因:
               --该字段记录 数据文件 使用到 的大小(包括TRUNCATE/DROP..释放的空间)  
               ROUND((SUM (DD.DATAFILE_TOTAL_GB - DF.DATAFILE_FREE_GB) /
                     SUM(DD.DATAFILE_TOTAL_GB) * 100 ),
                     2) "DATAFILE_USER%" --USER%=DATAFILE_USER_GB/DATAFILE_TOTAL_GB
          FROM (SELECT FILE_ID,
                       TABLESPACE_NAME,
                       FILE_NAME,
                       SUM(BYTES / 1024 / 1024 / 1024) DATAFILE_TOTAL_GB
                  FROM DBA_DATA_FILES DD
                 GROUP BY FILE_ID, FILE_NAME, TABLESPACE_NAME) DD
          JOIN (SELECT SUM(D.BYTES / 1024 / 1024 / 1024) DATAFILE_FREE_GB,
                      --该处用ROUND'四舍五入'函数,而DATAFILE_TOTAL_GB没用ROUND,可能导致外层结果集出现负'-'
                      --为了得到的USER% 更准确 子查询不用ROUND '四舍五入'函数
                      FILE_ID,
                      D.TABLESPACE_NAME
                 FROM DBA_FREE_SPACE D
                GROUP BY FILE_ID, D.TABLESPACE_NAME) DF
            ON DD.FILE_ID = DF.FILE_ID
           AND DD.TABLESPACE_NAME = DF.TABLESPACE_NAME
         GROUP BY DD.FILE_NAME, DD.TABLESPACE_NAME
        UNION ALL (SELECT F.FILE_NAME,
                         TF.TABLESPACE_NAME,
                         ROUND(TF.TABLESPACE_SIZE / 1024 / 1024 / 1024, 2),
                         ROUND(TF.FREE_SPACE / 1024 / 1024 / 1024, 2 ),
                         ROUND((TF.TABLESPACE_SIZE - TF.FREE_SPACE) / 1024 / 1024 / 1024,
                               2),
                         ROUND((TF.TABLESPACE_SIZE - TF.FREE_SPACE) /
                               TF.TABLESPACE_SIZE * 100,
                               2)
                    FROM DBA_TEMP_FREE_SPACE TF --临时表空间 的空间分配情况
                    JOIN DBA_TEMP_FILES F   --临时表空间文件属性
                      ON F.TABLESPACE_NAME = TF.TABLESPACE_NAME)) C
 GROUP BY C.TABLESPACE_NAME
 ORDER BY TABLESPACE_NAME DESC ;


SQL:
--查询 表空间 空间使用比情况 的第二种方案
--上一方案有相关解释..在此不再介绍

SELECT DD.TABLESPACE_NAME,
       ROUND (DD.TOTAL_GB, 2 ),
       ROUND (DF.FREE_GB, 2 ),
       ROUND ((DD.TOTAL_GB - DF.FREE_GB), 2 ) USER_GB,
       ROUND ((DD.TOTAL_GB - DF.FREE_GB) / DD.TOTAL_GB * 1002 ) "USER%"
  FROM ( SELECT TABLESPACE_NAME, SUM (BYTES / 1024 / 1024 1024 ) TOTAL_GB
          FROM DBA_DATA_FILES DD
         GROUP BY TABLESPACE_NAME) DD
  JOIN ( SELECT A.TABLESPACE_NAME, SUM (A.BYTES / 1024 / 1024 1024 ) FREE_GB
          FROM DBA_FREE_SPACE A
         GROUP BY A.TABLESPACE_NAME) DF
    ON DD.TABLESPACE_NAME = DF.TABLESPACE_NAME
UNION ALL ( SELECT TABLESPACE_NAME,
                  ROUND (TF.TABLESPACE_SIZE / 1024 1024 / 1024 2 ),
                  ROUND (TF.FREE_SPACE / 1024 1024 / 1024 2 ),
                  ROUND ((TF.TABLESPACE_SIZE - TF.FREE_SPACE) / 1024 / 1024 1024 ,
                        2 ),
                  ROUND ((TF.TABLESPACE_SIZE - TF.FREE_SPACE) /
                        TF.TABLESPACE_SIZE * 100 ,
                        2 )
             FROM DBA_TEMP_FREE_SPACE TF);

视图的解释:
DBA_DATA_FILES = SHOWS FILES( DATA FILES) BELONGING TO TABLESPACES.
DBA_FREE_SPACE = INFORMATION ABOUT FREE EXTENTS WITHIN ALL TABLESPACES.
DBA_TEMP_FREE_SPACE = DISPLAYS THE TOTAL ALLOCATED AND FREE SPACE IN EACH TEMPORARY TABLESPACE.
DBA_TEMP_FILES = SHOWS FILES(TEMP FILES) BELONGING TO TEMPORARY TABLESPACES. FILES) BELONGING TO TEMPORARY TABLESPACES.

 


祝好~


来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/28389881/viewspace-1266643/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
OCM

注册时间:2013-02-28

  • 博文量
    225
  • 访问量
    800258