本博客写于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 * 100, 2 ) "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博客

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

最新文章