被"NULL"骗了一次：Oracle表空间查询中一个被忽视的边界场景

原创

薛晓刚-

发布于 2026-08-27 09:45:39

写在前面：前几天朋友抛来一句"表空间快满了，加文件还是扩磁盘？"我心想这不简单，先看看使用率再说。顺手丢出一条用了 N 年的查询语句，结果屏幕上跳出来一行：USED_MB 空、FREE_MB 空、USED_PERCENT 空——一排醒目的 NULL。我心里咯噔一下：不会是数据文件坏了吧？

– 这是我工作多年第一次在生产库上看到这种现场。后来在 150 演示环境的 ai PDB 上完整复现了它，并写下了这篇博客。它暴露的不是 Oracle 的 Bug，而是我那条"用了多年"SQL脚本里，一个被所有正常场景掩盖住的陷阱。

一、事情的开头：一行用了 N 年的查询

我那段脚本是这么写的（删减版）：

SELECT 
    tablespace_name,
    file_name,
    bytes/1024/1024 as total_mb,
    (bytes - (select sum(bytes) 
               from dba_free_space f 
               where f.tablespace_name = d.tablespace_name 
               and f.file_id = d.file_id))/1024/1024 as used_mb,
    (select sum(bytes)/1024/1024 
     from dba_free_space f 
     where f.tablespace_name = d.tablespace_name 
     and f.file_id = d.file_id) as free_mb,
    round((1 - (select sum(bytes) from dba_free_space f 
                where f.tablespace_name = d.tablespace_name 
                and f.file_id = d.file_id) / bytes) * 100, 2) as used_percent
FROM dba_data_files d
ORDER BY used_percent DESC;

思路很简单：用 dba_data_files 拿到总大小，再 join dba_free_space 算出剩余、已用、使用率。在文件状态"正常"的环境下，它跑得行云流水——99% 的时候，used_percent 都是个扎眼的两位数小数。

直到那个下午，我看到了下面这种画面：

TABLESPACE_NAME	FILE_NAME	TOTAL_MB	USED_MB	FREE_MB	USED_PERCENT
USERS	/dataprod04/styy/ISC/users09.dbf	20480	(NULL)	(NULL)	(NULL)
USERS	/dataprod04/styy/ISC/users10.dbf	20480	20479.88	0.13	100
USERS	/dataprod04/styy/ISC/users11.dbf	20480	20367.19	112.81	99.45

users09.dbf 那个文件，20480MB 的"大块头"，居然整行派生出 NULL 字段。它的"邻居"们 99% 99.45% 报得欢快，唯独它，沉默。

二、第一反应：数据文件坏了吗？

看到 NULL，老 DBA 的第一直觉往往是"读不到了"。文件头是不是挂了？磁盘是不是掉链子？我马上查了 v$datafile_header：

FILE_NAME                                        STATUS  CHECKPOINT_CHANGE#  BYTES
/dataprod04/styy/ISC/users09.dbf                 ONLINE  13827315285        21474836480

STATUS=ONLINE，CHECKPOINT_CHANGE# 正常。文件没坏。磁盘没问题。Oracle 也没报错。

那为什么 dba_free_space 这条路走不通？

三、真相浮出水面：换一条 SQL 试试

dba_free_space 走不通，那就换条路。我用 LEFT JOIN 的写法再跑一次，关键差异是给子查询套了 NVL(..., 0)：

SELECT ddf.tablespace_name AS "表空间",
       ddf.file_name       AS "数据文件",
       ROUND(ddf.bytes / 1024 / 1024, 2) AS "文件大小(MB)",
       ROUND(NVL(ds.free_bytes, 0) / 1024 / 1024, 2) AS "空闲 (MB)",
       ROUND((ddf.bytes - NVL(ds.free_bytes, 0)) / 1024 / 1024, 2) AS "已用 (MB)",
       ROUND((ddf.bytes - NVL(ds.free_bytes, 0)) / ddf.bytes * 100, 2) AS "使用率(%)",
       ddf.autoextensible AS "自动扩展",
       ddf.maxbytes / 1024 / 1024 AS "最大(MB)"
  FROM dba_data_files ddf
  LEFT JOIN (SELECT file_id, SUM(bytes) AS free_bytes
               FROM dba_free_space
              GROUP BY file_id) ds
    ON ddf.file_id = ds.file_id
 ORDER BY ddf.tablespace_name, ddf.file_name;

结果让我"豁然开朗"——也对，应该豁然开朗：

表空间	数据文件	文件大小(MB)	空闲 (MB)	已用 (MB)	使用率(%)
USERS	/dataprod04/styy/ISC/users09.dbf	20480	0	20480	100

空闲 0MB，已用 20480MB，使用率 100%。

文件没坏，是用得一干二净。

为什么两个 SQL 给出了完全不同的答案？因为前者把"没有记录"和"记录为 0"混为一谈了。

四、为什么是 NULL？——一个 SQL 陷阱

把原版 SQL 拆开看，本质上是这么个算式：

used_mb   = bytes - (subquery)
free_mb   = (subquery)
percent   = round((1 - (subquery) / bytes) * 100, 2)

subquery 来自 select sum(bytes) from dba_free_space f where ...。

而 dba_free_space 视图只列出 free extent。当一个数据文件里完全没有任何 free extent 时，文件头文件段尾没留一点缝隙，dba_free_space 中根本不会出现这条 file_id 的记录。子查询返回的不是 0，是 NULL（SQL 标量聚合在没有记录时返回 NULL，这是 ANSI SQL 标准行为）。

接下来——

bytes - NULL = NULL
NULL / bytes = NULL
1 - NULL = NULL
NULL * 100 = NULL
round(NULL, 2) = NULL

这就是NULL 的算术传染性。整条派生列，从空闲、已用到使用率，集体"沉默"。

新版 SQL 之所以正确，是因为我对 ds.free_bytes 加了 NVL(..., 0)。LEFT JOIN 时子查询的 SUM(bytes) 没匹配上也是 NULL，NVL 把它兜成 0，整条算式才回到正轨。

换句话说：**老脚本假设"dba_free_space 必有记录"，这在文件被塞满到没有 free extent 的极端场景下不成立。**99.99% 的时间里这个假设成立；剩下 0.01% 的现场，它会"骗"你一次。

五、让Agent复现：让 20 个 1MB extent 一个都不剩（以下都是重复性劳动，全部由AI完成）

口说无凭。我在让Agent在我的演示环境的 ai PDB 上完整复现了一次。思路是：建一个 SMALLFILE 表空间，EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M，20MB 恰好切成 20 个 1MB 的 extent。然后塞 20 张表（每张占 1 个 extent），让 dba_free_space 里找不到任何一个 free extent。

-- 1) 建表空间：20MB = 20 个 1MB 的 UNIFORM extent
CREATE SMALLFILE TABLESPACE test_full 
  DATAFILE '/opt/oracle/oradata/XXGCDB/AI/test_full01.dbf' SIZE 20M
  EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

-- 2) 建 20 张表，每张表一个 segment 占一个 1MB extent
BEGIN
  FOR i IN 1..20 LOOP
    EXECUTE IMMEDIATE 
      'CREATE TABLE ann_demo.t'||i||' (id NUMBER, pad VARCHAR2(4000)) TABLESPACE test_full';
  END LOOP;
END;
/

-- 3) 灌数据触发段创建，并把段扩展到塞满
BEGIN
  FOR i IN 1..20 LOOP
    EXECUTE IMMEDIATE 
      'INSERT INTO ann_demo.t'||i||' 
         SELECT ROWNUM, RPAD(''A'',3500,''B'') FROM dual CONNECT BY LEVEL<=300';
  END LOOP;
  COMMIT;
END;
/
-- 第 10 张表在分配第二个 extent 时报 ORA-01653，正常——表空间已经撑爆了

跑完后我先验证下"现场"——dba_free_space 中 file_id=34 这条记录，已经彻底消失了：

SELECT file_id, COUNT(*) extents, SUM(bytes)/1024/1024 free_mb
  FROM dba_free_space 
 WHERE tablespace_name = 'TEST_FULL' 
 GROUP BY file_id;

返回：未选定行。

而 dba_segments 里清楚地列着 10 张表（T1~T10），每张 1~2MB，总占用 19MB（剩 1MB 留给 segment header 级别的元数据）：

SEGMENT_NAME  SEGMENT_TYPE  MB    EXTENTS
T1            TABLE         2     2
T2            TABLE         2     2
...
T9            TABLE         2     2
T10           TABLE         1     1

截图 1：用原版 SQL（"用了多年"那个）查询

SELECT tablespace_name, file_name,
       bytes/1024/1024 as total_mb,
       (bytes - (select sum(bytes) from dba_free_space f 
                 where f.tablespace_name = d.tablespace_name 
                   and f.file_id = d.file_id))/1024/1024 as used_mb,
       (select sum(bytes)/1024/1024 from dba_free_space f 
        where f.tablespace_name = d.tablespace_name 
          and f.file_id = d.file_id) as free_mb,
       round((1 - (select sum(bytes) from dba_free_space f 
                   where f.tablespace_name = d.tablespace_name 
                     and f.file_id = d.file_id) / bytes) * 100, 2) as used_percent
  FROM dba_data_files d
 WHERE tablespace_name = 'TEST_FULL';

输出：

TABLESPACE_NAME	FILE_NAME	TOTAL_MB	USED_MB	FREE_MB	USED_PERCENT
TEST_FULL	/opt/oracle/oradata/XXGCDB/AI/test_full01.dbf	20	NULL	NULL	NULL

跟生产库上那台 users09.dbf 一模一样。20480 行的现场、20 行的现场，机制完全相同。

截图 2：用修复版（加 NVL）

SELECT tablespace_name, file_name,
       bytes/1024/1024 as total_mb,
       ROUND((bytes - NVL((select sum(bytes) from dba_free_space f 
              where f.tablespace_name = d.tablespace_name and f.file_id = d.file_id),0))/1024/1024, 2) as used_mb,
       NVL((select sum(bytes)/1024/1024 from dba_free_space f 
        where f.tablespace_name = d.tablespace_name and f.file_id = d.file_id), 0) as free_mb,
       ROUND((1 - NVL((select sum(bytes) from dba_free_space f 
                where f.tablespace_name = d.tablespace_name and f.file_id = d.file_id),0) / bytes) * 100, 2) as used_percent
  FROM dba_data_files d
 WHERE tablespace_name = 'TEST_FULL';

输出：

TABLESPACE_NAME	FILE_NAME	TOTAL_MB	USED_MB	FREE_MB	USED_PERCENT
TEST_FULL	/opt/oracle/oradata/XXGCDB/AI/test_full01.dbf	20	20	0	100

真相大白：0 空闲、20 已用、100% 占用。文件没坏，就是被塞到了极致。

截图 3：v$datafile_header 验证文件完好

SELECT name, status, checkpoint_change#, bytes/1024/1024 mb
  FROM v$datafile_header 
 WHERE name LIKE '%test_full%';

NAME	STATUS	CHECKPOINT_CHANGE#	MB
/opt/oracle/oradata/XXGCDB/AI/test_full01.dbf	ONLINE	103180418	20

STATUS=ONLINE，checkpoint 正常——这跟生产库的 users09.dbf 检查结果完全一致。

六、有时候即使多年经验也有盲点

事后我回看那天的"误判"链条，其实错不在 NULL，错在我把 NULL 当成了"读不到"，而没把它当"算式没收敛"。复盘下来，三句话值得留下：

第一，NULL 在 Oracle 里要分两类：缺失（missing）和未知（unknown）。 dba_free_space 查不到 file_id，不是"我读不到这张视图"，而是"该文件压根没有 free extent——SUM 自然没东西可聚"。两种 NULL 在用户体验上一样，语义上完全不一样。脚本里加 NVL 不光是"兜底"，更是显式表态：“我默认 0 才是这个字段的合理语义”。

第二，“用了多年没问题"不等于"写得对”。 这段 SQL 在 99.99% 的现场都能跑出漂亮的小数。但 Oracle 一直没报错，并不代表它正确——它只是恰好满足"文件里至少有一个 free extent"这个隐含前提。用 LEFT JOIN ... GROUP BY 把 0 与 NULL 显式分开，是写生产脚本的基本功。子查询里裸用 select sum(...) 然后 bytes - subquery，是 SQL 反模式。

第三，遇事先别急着改配置。"加文件还是扩磁盘"这个问题，正确的前提是：你得先知道文件到底还有多少剩余。如果脚本一上来给你一排 NULL，绝大多数人会先怀疑数据库、怀疑磁盘、怀疑坏块——这正是我那天的真实心理活动。先把"NULL 还是 0"这个问题搞清楚，再去聊加文件/扩磁盘。

七、写在最后

工作多年，我以为自己对 dba_free_space 早就熟得不能再熟了。这次 NULL 现场让我意识到：很多"熟练"其实只是"没遇到反例"。当一个数据文件被用得一干二净，没有一个 free extent 时，dba_free_space 不会再为它"占一行"——这时所有基于它的派生指标都要重新审视。

把每一段"用了 N 年"的脚本都重新审视一次，把每个隐含假设都写进 NVL 或者 CASE WHEN ... IS NOT NULL 里——这是 DBA 工作里最朴素的修行。AUTOEXTEND 是兜底，NVL 也是兜底，但兜底不是"懒"，是"我已经想到那个 0.01% 的边界"。

下回再有人问"加文件还是扩磁盘"，我想我会先反问一句：你的脚本能正确显示 0 吗？

原创声明：本文系作者授权腾讯云开发者社区发表，未经许可，不得转载。

如有侵权，请联系 cloudcommunity@tencent.com 删除。

上海同盟