表空间文件数告警处理

参考文献

1. 查询Oracle表空间使用情况

一、 告警原因

oracle内部用10位数据记录单个表空间下文件个数，因此当单个表空间下文件数超出2^10时，该表空间将无法继续添加数据文件，必须对表空间下数据进行迁移。表空间文件数告警阈值为800个。

二、 查询文件个数达到阈值的表空间

SET linesize 200

SELECT *
FROM   (SELECT tablespace_name,
               Count(*) AS file_cnt
        FROM   dba_data_files
        GROUP  BY tablespace_name
        ORDER  BY tablespace_name)
WHERE  file_cnt > 800;

或者

SET linesize 200

SELECT *
FROM   (SELECT ts#,
               tablespace_name,
               Count(*)           AS file_cnt,
               Max(creation_time) AS max_creation_time
        FROM   v$datafile_header
        GROUP  BY ts#,
                  tablespace_name) t
WHERE  file_cnt > 800;

二、 找出表空间下数据量最大的用户

SET lines 200

SELECT *
FROM   (SELECT tablespace_name,
               OWNER,
               Sum(bytes) / 1024 / 1024 AS mb
        FROM   dba_segments
        GROUP  BY tablespace_name,
                  OWNER
        ORDER  BY mb DESC)
WHERE  tablespace_name = '&tbsname';

或者

SET lines 200

SELECT *
FROM   (SELECT tablespace_name,
               OWNER,
               Sum(bytes) / 1024 / 1024 AS mb
        FROM   dba_extents
        GROUP  BY tablespace_name,
                  OWNER
        ORDER  BY mb DESC)
WHERE  tablespace_name = '&tbsname';

三、 组合查找

找出文件个数达到预警值的表空间，并且找出其中空间使用量前三的用户。

SET lines 200

SELECT *
FROM   (SELECT tablespace_name,
               OWNER,
               Sum(bytes) / 1024 / 1024 AS mb
        FROM   dba_extents
        GROUP  BY tablespace_name,
                  OWNER
        ORDER  BY mb DESC)
WHERE  tablespace_name IN (SELECT tablespace_name
                           FROM   (SELECT tablespace_name,
                                          Count(*) AS file_cnt
                                   FROM   v$datafile_header
                                   GROUP  BY tablespace_name) t
                           WHERE  file_cnt > 800)
       AND rownum < 4;