oracle 表空间监控脚本编写 -m6米乐安卓版下载

背景

  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