【案例分析】 tablespace有足够空间，为什么无法使用？
[TOC]
一、问题描述
1. 错误信息
- alert 日志从1点开始，出现大量ORA-1688错误，表空间无法扩展错误。
2025-06-29T01:00:40.302871+08:00 ORA-1688: unable to extend table xxxxx by 8192 in tablespace SYSBS 2025-06-29T01:00:40.696315+08:00 ORA-1688: unable to extend table xxxxx by 8192 in tablespace SYSBS 2025-06-29T01:00:40.908780+08:00 ORA-1688: unable to extend table xxxxx by 8192 in tablespace SYSBS
2. 分析思路
- 只看错误，问题很简单，增加数据文件扩展表空间即可。
- 可是我们是配了报警的啊，怎么没有收到报警邮件呢？
- 查看表空间使用率：free空间还有将近1T的空间，那怎么会报表空间不足呢？
- 查看dba_hist_tbspc_space_usage 检查表空间历史使用情况，这里在1点的时候只 free了5.73G，和alert 日志报错时间也基本吻合。但是上面我们查询的free空间还有1T，这里只有5.73G相差很大。这个原因是dba_hist_tbspc_space_usage 不包含recyclebin占用的空间。
3. 紧急恢复
- 先增加数据文件扩展表空间，应用也确实恢复了，所以数据库层面确实空间不足，导致应用无法使用。
二、原因分析
环境模拟
1. 创建测试表空间
- 新建一个表空间 test ，目前使用率基本为0
SELECT D.TABLESPACE_NAME "Tbsname",
D.total_mb "Total_M",
D.total_mb - F.free_mb "Used_M",
round(F.free_mb) "Free_M",
TO_CHAR(ROUND((D.total_mb - F.free_mb) / D.total_mb * 100,2),'990.99') "used_%"
FROM (SELECT TABLESPACE_NAME,
ROUND(SUM(BYTES) / (1024 * 1024), 2) free_mb
FROM SYS.DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) F,
(SELECT DD.TABLESPACE_NAME,
ROUND(SUM(DD.BYTES) / (1024 * 1024), 2) total_mb
FROM SYS.DBA_DATA_FILES DD
GROUP BY DD.TABLESPACE_NAME) D
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)
and D.TABLESPACE_NAME='TEST'
ORDER BY 5 DESC;
Tbsname Total_M Used_M Free_M used_%
------------------------------ ---------- ---------- ---------- --------------
TEST 120 1 119 0.83
2. 创建测试表 testpst1
- 新建一个表，然后drop 进入recyclebin
SXC@ORA11G> create table testpst1 tablespace test as select * from dba_objects; -- dba_objects 差不多12M
Table created.
SXC@ORA11G> insert into testpst1 select * from testpst1;
87989 rows created.
SXC@ORA11G> insert into testpst1 select * from testpst1;
175978 rows created.
SXC@ORA11G> insert into testpst1 select * from testpst1;
351956 rows created.
SXC@ORA11G> commit;
Commit complete.
-- 查询表空间，使用了81M
Tbsname Total_M Used_M Free_M used_%
------------------------------ ---------- ---------- ---------- --------------
TEST 120 81 39 67.50
- drop 测试表，进入recyclebin
SYS@ORA11G> drop table sxc.testpst1;
Table dropped.
SXC@ORA11G> sho recyclebin;
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -------------------
TESTPST1 BIN$ONieRdcoBr3gYwEAWKzv+w==$0 TABLE 2025-07-01:15:12:05
-- drop 完之后，查询表空间使用率，可以看到已经free空间释放出来了
Tbsname Total_M Used_M Free_M used_%
------------------------------ ---------- ---------- ---------- --------------
TEST 120 1 119 0.83
3. 创建测试表 testpst2
-- 再次建表，并插入数据
SXC@ORA11G> create table testpst2 tablespace test as select * from dba_objects;
Table created.
SXC@ORA11G> insert into testpst2 select * from dba_objects;
87989 rows created.
SXC@ORA11G> insert into testpst2 select * from dba_objects;
87989 rows created.
-- 先抛出了insert 被 trigger 阻止的错误
SXC@ORA11G> insert into testpst2 select * from dba_objects;
insert into testpst2 select * from dba_objects
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 2
ORA-20001: Please not do DROP or TRUNCATE Table,You will be Caught!!!
ORA-06512: at line 4
-- 再次insert 就抛出了无法扩展表空间错误
SXC@ORA11G> insert into testpst2 select * from dba_objects;
insert into testpst2 select * from dba_objects
*
ERROR at line 1:
ORA-01653: unable to extend table SXC.TESTPST2 by 128 in tablespace TEST
SXC@ORA11G> insert into testpst2 select * from dba_objects;
insert into testpst2 select * from dba_objects
*
ERROR at line 1:
ORA-01653: unable to extend table SXC.TESTPST2 by 128 in tablespace TEST
-- 此时，查询表空间使用率，还有80M的free空间，但是就是无法insert数据，而且很明显inert语句所需要的空间大概12M左右，远小于80M的
Tbsname Total_M Used_M Free_M used_%
------------------------------ ---------- ---------- ---------- --------------
TEST 120 40 80 33.33
-- 同时查看alert 日志只抛出ORA-1653无法扩展空间的错误，没有trigger相关信息
Tue Jul 01 15:13:23 2025
ORA-1653: unable to extend table SXC.TESTPST2 by 128 in tablespace TEST
Tue Jul 01 15:13:23 2025
Archived Log entry 546 added for thread 1 sequence 512 ID 0x7f7bd3c4 dest 1:
Tue Jul 01 15:13:58 2025
ORA-1653: unable to extend table SXC.TESTPST2 by 128 in tablespace TEST
原因排查
1. 排查思路
- insert 语句为什么会触发trigger 呢？ trigger只是禁用DROP 和 TRUNCATE 操作
-- trigger 内容：
set long 2000000000
set linesize 500
set pagesize 0
select dbms_metadata.get_ddl('TRIGGER','TRIGGER_UNDROPTABLE','SYS') from dual;
CREATE OR REPLACE TRIGGER "SYS"."TRIGGER_UNDROPTABLE"
BEFORE DROP or TRUNCATE ON DATABASE
begin
if ora_login_user not in ('SYS','SYSTEM') THEN
if upper(dictionary_obj_type) ='TABLE' THEN
Raise_application_error (-20001,'Please not do DROP or TRUNCATE Table,You will be Caught!!!');
end if;
end if;
end;
ALTER TRIGGER "SYS"."TRIGGER_UNDROPTABLE" ENABLE
- 查看test表空间中的对象
SXC@ORA11G> col owner for a10
SXC@ORA11G> col segment_name for a50
SXC@ORA11G> select owner,segment_name,bytes/1024/1024 from dba_segments where tablespace_name='TEST' order by bytes desc;
OWNER SEGMENT_NAME BYTES/1024/1024
---------- -------------------------------------------------- ---------------
SXC BIN$ONieRdcoBr3gYwEAWKzv+w==$0 80 --> 回收站占用了80M空间
SXC TESTPST2 39
- 从理论知识来说，当表空间不够的时候，应该会清理回收站信息，已满足正常的空间需求。从这里看recyclebin对象并没有被自动清理出来释放空间。
- 手动清理一下回收站空间
-- 手动pruge 也触发了trigger ，和上面insert语句一样，所以怀疑是上面insert是有自动清理recyclbin 信息，只是被trigger阻止了，所以insert抛出了trigger错误
SXC@ORA11G> purge table "BIN$ONieRdcoBr3gYwEAWKzv+w==$0";
purge table "BIN$ONieRdcoBr3gYwEAWKzv+w==$0"
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 2
ORA-20001: Please not do DROP or TRUNCATE Table,You will be Caught!!!
ORA-06512: at line 4
- 禁用trigger ，验证
SXC@ORA11G> conn / as sysdba
Connected.
SYS@ORA11G> alter trigger sys.trigger_undroptable disable;
Trigger altered.
SYS@ORA11G> conn sxc/sxc
Connected.
SXC@ORA11G> insert into testpst2 select * from dba_objects; ---》 成功
87986 rows created.
SXC@ORA11G> commit;
Commit complete.
SXC@ORA11G> show recyclebin; --》 recyclebin中的对象也被自动清理了
SXC@ORA11G>
SYS@ORA11G> /
Tbsname Total_M Used_M Free_M used_%
------------------------------ ---------- ---------- ---------- --------------
TEST 120 51 69 42.50 --> 表空间使用率也上去了
综上：分析到这里，我们判断是因为trigger导致了recyclebin无法自动清理释放空间，才导致了dba_free_space显示有大量空间，却无法使用。
解决办法：禁用trigger 或者手动清理recyclebin。
另外：多次测试发现，disable trigger 之后，已经报错的session，insert 仍然报错，但是新的session insert操作恢复正常，并且当新的session insert 之后，会触发自动清理recyclebin，当recyclebin被新的session清理后，原本报错旧的session也可以正常insert的了。
2. 附：purge table 验证
SYS@ORA11G> drop table sxc.testpst2;
Table dropped.
SXC@ORA11G> show recyclebin;
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -------------------
TESTPST2 BIN$ONq46RPbCrbgYwEAWKye4Q==$0 TABLE 2025-07-01:16:18:05
-- 通过sql_trace追踪一下purge recyclebin 过程
SXC@ORA11G> select SPID from v$process where addr=(select PADDR from v$session where sid=(select distinct sid from v$mystat));
SPID
------------------------------------------------
2754
SXC@ORA11G> alter session set sql_trace = true;
Session altered.
SXC@ORA11G> purge table "BIN$ONq46RPbCrbgYwEAWKye4Q==$0";
Table purged.
-- 查看trace 文件
[oracle@ora11204 trace]$ tkprof ORA11G_ora_2754.trc ORA11G_ora_2754.txt
TKPROF: Release 11.2.0.4.0 - Development on Tue Jul 1 16:19:13 2025
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
-- 可以看到里面包含了drop table 操作，这也就是为什么会触发trigger的原因
[oracle@ora11204 trace]$ cat ORA11G_ora_2754.txt|grep BIN
purge table "BIN$ONq46RPbCrbgYwEAWKye4Q==$0"
1 1 1 TABLE ACCESS FULL RECYCLEBIN$ (cr=2 pr=0 pw=0 time=34 us cost=2 size=384 card=16)
0 0 0 TABLE ACCESS FULL RECYCLEBIN$ (cr=2 pr=0 pw=0 time=6 us cost=2 size=24 card=1)
1 1 1 TABLE ACCESS FULL RECYCLEBIN$ (cr=2 pr=0 pw=0 time=18 us cost=2 size=8 card=1)
drop table "SXC"."BIN$ONq46RPbCrbgYwEAWKye4Q==$0" purge
0 0 0 DELETE RECYCLEBIN$ (cr=2 pr=0 pw=0 time=83 us)
1 1 1 TABLE ACCESS FULL RECYCLEBIN$ (cr=2 pr=0 pw=0 time=11 us cost=2 size=51 card=3)
最后修改时间：2025-07-03 09:52:22
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
