常用SQL-表空间使用情况查询

最新推荐文章于 2024-10-28 16:07:59 发布

唯一的小彬哥

最新推荐文章于 2024-10-28 16:07:59 发布

阅读量1.1k

点赞数 12

分类专栏： Oracle 常用SQL 文章标签： oracle 数据库运维 sql

本文链接：https://blog.csdn.net/xsdsj326/article/details/135848049

版权

Oracle 同时被 2 个专栏收录

5 篇文章 0 订阅

订阅专栏

常用SQL

2 篇文章 0 订阅

订阅专栏

本文介绍了如何通过查询Oracle数据库的DBA_FREE_SPACE和DBA_DATA_FILES视图来评估表空间使用情况，强调了MAXBYTES和BYTES的区别，以及在计算表空间使用率时考虑两者的重要性。

摘要由CSDN通过智能技术生成

在Oracl中查询数据库表空间空闲区可通过查询DBA_FREE_SPACE视图来获得数据。查询数据文件大小可以通过DBA_DATA_FILES来获取数据文件大小的情况。因此查询表空间使用情况都是通过以上两个表关联来获取数据。但是你是否有疑惑为什么从网络上找到的表空间查询语句很多时候表空间使用率都非常高？继续阅读为您解开疑惑。

首先我们查看一下DBA_DATA_FILES的查询结果。如下图所示：

可发现数据大小的字段一共有两个分别是BYTES和MAXBYTES。从上图可以得知当AUTOEXTENSIBLE为NO时MAXBYTES为0。而AUTOEXTENSIBLE为YES时则是有一个固定的值。那这两个值分别代表什么意思呢。我们可以通过新建数据文件，观察数据变动来分析其含义：

命令一：初始大小1M，自动扩展，最大值10MB

ALTER TABLESPACE WYDXBG_SMAMLL_SPACE ADD DATAFILE 'D:\APP\ORACLE\TABLESPACE\WYDXBG01.DBF' SIZE 1M AUTOEXTEND ON NEXT 1M MAXSIZE 10M;

执行后结果如下：

命令二：初始大小1M，无法自动扩展

ALTER TABLESPACE WYDXBG_SMAMLL_SPACE ADD DATAFILE 'D:\APP\ORACLE\TABLESPACE\WYDXBG03.DBF' SIZE 1M AUTOEXTEND OFF

执行后结果如下：

所以从上面可以得出结论BYTES是初始化的空间也是自动扩展后的空间，而MAXBYTES则是最大允许扩展的空间，且要是文件不可自动扩展该值会为0。而我们可发现其实网络上几乎大部分的表空间查询语句基本都是基于BYTES来计算的，所以如果表空间存在自动扩展的情况误差就会极大，而直接统计MAXBYTES则也可能存在错误，所以合计数据时我们需要同时考虑BYTES和MAXBYTES，应当取MAXBYTES而当该值为0时则取BYTES。

注意：BYTES是已分配的空间，它已经实际占用了磁盘空间。而MAXBYTES是数据库可扩展的最大空间，实际并未占用物理磁盘空间。所以有可能会在查询中出现最大可扩展空间非常大，但磁盘已经没有空间的情况。

SQL如下：

WITH DATA_FILE AS
 (SELECT T.TABLESPACE_NAME,
         SUM(T.BYTES) TOTAL_BYTE_ALLOCATED,
         SUM(DECODE(T.MAXBYTES, 0, T.BYTES, T.MAXBYTES)) TOTAL_BYTE_MAX,
         MAX(DECODE(T.AUTOEXTENSIBLE, 'NO', 0, 1)) AUTOEXTENSIBLE
    FROM DBA_DATA_FILES T
   GROUP BY TABLESPACE_NAME),
FREE_DATA AS
 (SELECT TABLESPACE_NAME, SUM(BYTES) FREE_BYTE
    FROM DBA_FREE_SPACE
   GROUP BY TABLESPACE_NAME),
QUERY_RESULT AS
 (SELECT /*+ NO_MERGE(T) NO_MERGE(T1) USE_HASH(T T1)*/
   T1.TABLESPACE_NAME,
   ROUND(TOTAL_BYTE_ALLOCATED / (1024 * 1024), 2) TOTAL_BYTE_ALLOCATED,
   ROUND(TOTAL_BYTE_MAX / (1024 * 1024), 2) MAXIMUM,
   ROUND(FREE_BYTE / (1024 * 1024), 2) ALLOCATE_REMAIND,
   ROUND((TOTAL_BYTE_ALLOCATED - FREE_BYTE) / (1024 * 1024), 2) ALLOCATE_USED,
   ROUND((TOTAL_BYTE_ALLOCATED - FREE_BYTE) / TOTAL_BYTE_ALLOCATED * 100, 2) ALLOCATE_USAGE_RATE,
   ROUND((TOTAL_BYTE_ALLOCATED - FREE_BYTE) / TOTAL_BYTE_MAX * 100, 2) TOTAL_USED_RATE
    FROM DATA_FILE T, FREE_DATA T1
   WHERE T.TABLESPACE_NAME = T1.TABLESPACE_NAME)
SELECT TABLESPACE_NAME "表空间",
       TOTAL_BYTE_ALLOCATED "已分配-总大小(MB)",
       ALLOCATE_USED "已分配-已使用(MB)",
       ALLOCATE_REMAIND "已分配-未使用(MB)",
       ALLOCATE_USAGE_RATE "已分配-使用百分比",
       MAXIMUM "可扩展-总大小(MB)",
       MAXIMUM - TOTAL_BYTE_ALLOCATED "可扩展空间",
       TOTAL_USED_RATE "可扩展-使用百分比"
  FROM QUERY_RESULT