Oracle 表空间监控脚本编写

背景

  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

• 计算所有表空间的总大小：
  根据上面的查询结果得出的计算公式：自增汇总 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