表空间使用率查询（针对表空间数据文件自动扩展场景）

摘要：
适用于表空间监控需要扩容数据文件的场景。若表空间占用较小，考虑80%告警；若表空间占用较大，则可以考虑90%再进行告警，因为90%，可能还有大几百G的剩余可用空间。

select ts#,
       tablespace_name,
       sum(BYTES),
       sum(MAXBYTES),
       sum(free),
       round(sum(BYTES) / sum(MAXBYTES) * 100, 2) AS "使用率%"
  from (
        select ts.ts#,
                ts.NAME as tablespace_name,
                dfe.FILE#,
                dfe.NAME,
                round((ddf.BYTES / 1024 / 1024 / 1024), 2) as BYTES,
                round(ddf.MAXBYTES / 1024 / 1024 / 1024, 2) as MAXBYTES,
                decode(ddf.INCREMENT_BY, 1, 'YES', 'NO') INCREMENT_BY,
                dfe.CREATION_TIME,
                round(dfe.CREATE_BYTES / 1024 / 1024 / 1024, 2) as CREATE_BYTES,
                round((ddf.MAXBYTES - ddf.BYTES) / 1024 / 1024 / 1024, 2) as free
          from v$tablespace ts
          join v$datafile dfe
            on dfe.TS# = ts.TS#
          join dba_data_files ddf
            on ddf.FILE_ID = dfe.FILE#
        --where ts.ts# = 6
where ddf.autoextensible = 'YES'
        )
 group by ts#, tablespace_name
--having round(sum(BYTES) / sum(MAXBYTES) * 100, 2)>85   --超过80的
 order by round(sum(BYTES) / sum(MAXBYTES) * 100, 2) DEsc
-- order by tablespace_name
;
复制

Linux环境下，可结合shell脚本加钉钉、邮件等方式进行告警