本部落格寫於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.

祝好~

Oracle 查詢各個 “表空間/資料檔案” 的空間使用比情況

相關文章