Oracle 表空間監控腳本編寫

原創布衣 2022-12-04

3239

背景

??Oracle 表空間的數據文件有自增屬性（autoextensible = ‘YES’）及非自增屬性（autoextensible = ‘NO’），這個自增屬性并不是無限擴展，其遵循如下原理:
??由于Oracle的Rowid使用22位來代表數據塊號，因此Oracle表空間數據文件每個數據文件最多只能包含2^22個數據塊。
也因此數據庫表空間的數據文件不是無限增長的，例如：在數據塊為8k的情況下，單個數據文件的最大容量為8K*2^22 = 32G
同理：

數據塊為2K，數據文件最大約8G
數據塊為32K，數據文件最大約16*8G

??故若達到這個最大容量之后，則即便是設置了自增長，也不是無限自增長，此時則需要為這個表空間添加數據文件。

遇到的問題

??我們的生產環境以前按著統一標準，系統表空間（SYSTEM、SYSAUX、USERS、UNDOTBS）均添加的為自增屬性的數據文件，而業務表空間添加的均為非自增屬性的數據文件，這樣方便管理。因為系統表空間數據庫基本夠用，所以在寫表空間監控的時候就把自增文件的表空間就剔除出去了，只監控非自增屬性的業務表空間。
??后來隨著人員的流動及每個人DBA的習慣不同，這個標準慢慢的就淡化了（根本原因為管理不到位）。導致新來的DBA為了省事，表空間后面新加的數據文件都成了自增的文件。最終一個業務表空間的數據文件組成：自增+非自增，致使以前的表空間監控腳本無法真實的反應現在的空間使用情況。
??再又遇到一次Oracle SYSAUX表空間異常爆滿—ORA-1653事故，表空間監控一條報警也沒有報，幸虧加了【ORA-】日志報警及時發現了問題。
??于是只能把數據庫表空間監控做優化了。

思路測試：

非自增數據文件MAXBYTES_MB 為0

SQL> set line 800 pagesize 900
SQL> select tablespace_name,BYTES/1024/1024 BYTES_MB,MAXBYTES/1024/1024 MAXBYTES_MB,autoextensible from dba_data_files;
TABLESPACE_NAME                  BYTES_MB MAXBYTES_MB AUT
------------------------------ ---------- ----------- ---
USERS                             4608.75  32767.9844 YES
UNDOTBS1                             4765  32767.9844 YES
SYSAUX                               1100  32767.9844 YES
SYSTEM                               2330  32767.9844 YES
RPT_DAT                             30720           0 NO
TWO_DAT                              1024       30720 YES
TWO_DAT                              1024           0 NO

計算所有表空間的總大?。?br /> 根據上面的查詢結果得出的計算公式：自增匯總 MAXBYTES 的值，非自增匯總：BYTES 的值，計算出所有表空間可使用的總大小。

SQL>select TABLESPACE_NAME,
       ROUND(sum(case
                   when autoextensible = 'NO' then
                    BYTES
                   when autoextensible = 'YES' then
                    MAXBYTES
                 end) / 1024 / 1024,
             2) TOTAL_MB
  from dba_data_files
 group by TABLESPACE_NAME;
TABLESPACE_NAME                  TOTAL_MB
------------------------------ ----------
UNDOTBS1                         32767.98
SYSAUX                           32767.98
USERS                            32767.98
SYSTEM                           32767.98
RPT_DAT                             30720
TWO_DAT                             31744

計算空閑空間大小
1、通過dba_free_space視圖計算出空閑空間大小

SQL> SELECT dfs.TABLESPACE_NAME, SUM(dfs.bytes / 1024 / 1024) FREE_MB_1
  FROM dba_free_space dfs
 GROUP BY dfs.TABLESPACE_NAME;
TABLESPACE_NAME                   FREE_MB_1
------------------------------ ----------
SYSAUX                                 69
UNDOTBS1                         4695.875
USERS                           3674.3125
SYSTEM                          1602.4375
RPT_DAT                          10210.25
TWO_DAT                              2046

2、dba_data_file視圖里的BYTES值包含dba_free_space視圖的bytes值的空間。

SQL> select TABLESPACE_NAME,
       ROUND(sum(case
                   when MAXBYTES - BYTES > 0 then
                    MAXBYTES - BYTES
                   when MAXBYTES - BYTES < 0 then
                    0
                 end) / 1024 / 1024,
             2) Free_MB_2
  from dba_data_files
 group by TABLESPACE_NAME;
TABLESPACE_NAME                   FREE_MB_2
------------------------------ ----------
UNDOTBS1                         28002.98
SYSAUX                           31667.98
USERS                            28159.23
SYSTEM                           30437.98
RPT_DAT                                 0
TWO_DAT                             29696

最后計算公式：

select a.TABLESPACE_NAME,
       ROUND((1 - (a.Free_MB_1+ b.FREE_MB_2) / a.total_mb) * 100, 2) Used_Prc
  from (select TABLESPACE_NAME,
               ROUND(sum(case
                           when autoextensible = 'NO' then
                            BYTES
                           when autoextensible = 'YES' then
                            MAXBYTES
                         end) / 1024 / 1024,
                     2) TOTAL_MB,
               ROUND(sum(case
                           when MAXBYTES - BYTES > 0 then
                            MAXBYTES - BYTES
                           when MAXBYTES - BYTES < 0 then
                            0
                         end) / 1024 / 1024,
                     2) Free_MB_1
          from dba_data_files
         group by TABLESPACE_NAME) a
 inner join (SELECT dfs.TABLESPACE_NAME,
                    SUM(dfs.bytes / 1024 / 1024) FREE_MB_2
               FROM dba_free_space dfs
              GROUP BY dfs.TABLESPACE_NAME) b
    on a.TABLESPACE_NAME = b.TABLESPACE_NAME;
TABLESPACE_NAME                  USED_PRC
------------------------------ ----------
SYSAUX                               3.15
UNDOTBS1                              .19
USERS                                2.85
SYSTEM                               2.22
RPT_DAT                             66.76
TWO_DAT                               .01

表空間使用情況對比

TABLESPACE_NAME   AUT             TOTAL_MB   FREE_MB_1      FREE_MB_2   USED_MB    USED_PRC(%)
------------------------------ ----------  ----------     ----------   --------    ----------
UNDOTBS1           YES            32767.98    4695.875      28002.98      61.25         .19
SYSAUX             YES            32767.98          69      31667.98    1031.44        3.15
USERS              YES            32767.98   3674.3125      28159.23     934.44        2.85
SYSTEM             YES            32767.98   1602.4375      30437.98     727.56        2.22
RPT_DAT            NO                30720    10210.25             0   20509.75       66.76
TWO_DAT            1:YES,1:NO        31744        2046         29696          2         .01
-- TWO_DAT:一個自增文件最大可使用30720MB,一個非自增文件最大可使用：1024MB

腳本如下：

執行輸出

-- 表空間使用越30%
[oracle@db~]# sh check_tablespace.sh 30
TableSpace used value!|SYSAUX:30.32%|RPT_DAT:71.62%|RPT_IND_DAT:56.7%|TWO_IND_DAT:55.32%|TWO_DAT:64.36%
[oracle@db~]# sh check_tablespace.sh 80
all tablespaces are ok!

從執行結果可以看到系統表空間：SYSAUX及業務表空間：RPT_DAT、TWO_DAT … …

文章下載：Oracle 表空間監控腳本.pdf